Formula Parse Error in Google Sheets

What Is Formula Parse Error in Google Sheets?

A formula parse error in Google Sheets occurs when the sheets cannot carry out the instructions in your formula. It happens when Google Sheets is unable to process a formula due to missing data, incorrect data types, or an incorrectly written formula. The errors may vary, ranging from simple typos to mathematical issues such as dividing by zero.

Let us look at an example of a formula parse error, as shown below. Here, we get a divide-by-zero error. To correct this, check the reason your denominator is zero and if it can be changed. You can also use ISERROR to handle the error.

Common Formula Parse Errors

There are a variety of ways in which formula parse errors can happen. These include typos, missing commas, and even mathematical impossibilities. It is essential to understand the reason behind these errors and try to fix them. Many times, Google Sheets tell you the type of error like #REF!, #VALUE, etc., and you can hover over the cell containing the formula, thereby understanding what the error is and learning how to fix the formula parse errors in Google Sheets.

Some of the common types of formula parse errors include:

1. Error: #ERROR!
2. Error: #N/A
3. Error: #NAME?
4. Error: #NUM!
5. Error: #REF!
6. Error: #VALUE!

Error: #ERROR

This formula parse error message is unique to Google Sheets. The following  Review the formula for syntax errors or incorrect usage. Sometimes, this error can be more specific if you hover over the error message for additional details.

Here, we do not include an ampersand between the text and formula, and hence, it gives an error.

Another reason for getting it is incorrectly not closing the brackets for a formula.

How to fix: To fix this error, check if your formula contains the correct number of brackets. Also, ensure that you don’t miss the & symbol for concatenating.

Use the $ symbol for currency by formatting it and not just typing it.

Error: #N/A

This error indicates that a value is not available. These types of errors often occur in lookup functions when a match is not found. As seen below, the formula does not contain any errors, but the value is not found.

How to fix: Check if the lookup value exists and is correct. To make it more user-friendly, print a message which explains why we got it.

If you get this error, you can handle it with the IFERROR Google Sheets formula to provide a more user-friendly message.

=IFERROR(VLOOKUP(“Banana”, A5:B7 , 2, FALSE), “Not found”)

Error: #NAME?

The #NAME? formula parse error means your formula’s syntax is incorrect. It also signifies that Google Sheets doesn’t recognize the reference or function name, which may be due to a spelling mistake.

Here, we have misspelled the average function. Another reason for the #NAME? Error is when you reference a named range that does not exist.

=ISNUMBER(num)

Here, you get a #NAME? error if the named range num does not exist

If you miss, quotation marks in your formula, you get this error.

How to fix: Check whether your function names are correct.  Check for all defined named ranges and their spellings.

• Also, make sure to check out the colons in your references; otherwise, it will lead to this error.
• For instance =SUM(A1A5) should read =SUM(A1:A5)

Please check if you have defined all named ranges before using them in your formulas and that they all have the correct spellings.

Error: #NUM!

The #NUM! formula parse error occurs when numeric values aren’t valid in your formula. It occurs when you use non-numeric data where numeric values are expected.

For instance, when we try to find the square root of -2, we get this error.

How to fix: Ensure you have the correct numeric arguments in your formula as required. The error message gives you an idea of where you went wrong.

Error: #REF!

The #REF! error indicates that a formula refers to an invalid cell. It can happen when the data in a cell or range is deleted, and you refer to it. Let us look at an example of how this happens. Here, we used the VLOOKUP function but, by mistake, gave a wrong range, which led to a circular dependency.

Thus, the #REF! formula parse error occurs when you have an invalid reference.

You also get the #REF! error if you use lookup and try to return a value outside of the ranges you’ve specified. For example, in a table with two columns, we look up the third column.

For the same example above, we refer to column 3, which does not exist in the table.

How to fix: The error message will give you an idea of the kind of error you are dealing with. Look for the #REF! error in your formula and replace it with the correct reference to a cell or range.

For circular dependencies, you should find the reference causing the problem and change it accordingly.

Error: #VALUE!

We get this error when a formula has a wrong type of argument. For example, if we give a number as an argument, where a formula should receive a text, we may get this error. Below, the value in cell B21 should have been a number.

Another cause of #VALUE! errors are mixing up of date formats.

How to fix: Verify whether all arguments in your formula are of the correct data type.

How to Fix Formual Parse Errors in Google Sheets?

Formula parse errors in Google Sheets usually occur due to an error in the structure or syntax of the formula.

Using Formula Suggestions & Help

Enable formula corrections: Google Sheets has a very helpful feature for those who use formulas frequently. You can activate the AutoComplete feature. It enables formula suggestions and corrections. Enabling these features makes Google Sheets automatically autocomplete some formulas, make any corrections, or give suggestions.

To activate, click Tools, select Autocomplete, and enable them.

Function List: To avoid errors with functions, type = and begin typing the formula name. A dropdown list will appear showing those functions which match your input.

Using IFERROR Function

IFERROR is a useful function that is used to handle errors gracefully. Here, you can specify a message or fallback value should your formula encounter an error. It is beneficial to manage errors like #DIV/0!, #VALUE!, etc. which may disturb your spreadsheet’s functionality. Its syntax is =IFERROR(value, [value_if_error]).

For example, to avoid a divide by zero error, you can write the following formula shown in the example.

Using IFERROR & Lookup Functions

In case of missing values in VLOOKUP, instead of showing the #N/A error, the formula will output a custom message that we give, like “Search term not found.” Usually, when a search term does not yield a result in VLOOKUP, it results in an #N/A! error. To handle this output, which does not explain much to the user, we use the IFERROR function in conjunction with the VLOOKUP function to give a helpful output to the end user.

Here, we are searching for ‘6’ in the first column of the range A1:B5. Since it does not exist, without the use of the IFERROR function, you will get formula parse errors in Google Sheets.

The IFERROR function enables the user to understand the issue with a user-friendly message.

Important Things to Note

1. Beware while using the IFERROR function as overusing IFERROR might conceal the actual issues that need addressing. This can be used only to handle known errors but underlying problems should be check for and fixed.
2. Before you encounter any error, use data validation to prevent them from occurring in the first place.
3. In case you are not able to fix formula parse errors in Google sheets, use the Google Docs Editors Help Community to post queries and find solutions to specific formula issues.
4. To learn more about an error in detail, clicking on it or the formula bar will often provide useful suggestions to correct the formula.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the Formula Parse Error in Google Sheets examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Formula Parse Error in Google Sheets. We learn Formula Parse Error in Google Sheets, its examples, working template. You can learn more from the following articles.

Slicers In Google Sheets

Timeline In Google Sheets

DELTA Function In Google Sheets