How to Fix “Formula Parse Error” in Google Sheets

How to Fix Formula Parse Error in Google Sheets

A formula parse error occurs when Google Sheets cannot interpret your formula. Hence, it flashes an error message. There are different types of formula parse errors. In this guide, you will learn about each formula parse error, why they happen and how to fix formula parse errors in Google Sheets.

#ERROR! Error

This error is specifically for Google Sheets – you would not see this error in Microsoft Excel. It occurs because Google Sheets cannot interpret the formula. Usually, this happens when you have an extra or missing operator in the formula like parentheses, colon, or semicolon.

#Error Example
#Error! when you miss any operator in the formula.

As you can see, forgetting to include the colon sign while defining a range inside the formula returned a #ERROR! Error.

How to fix the #ERROR! error?

Use the same number of brackets in the opening and closing. Ensure that you are following the Google Sheets formula syntax. Check if you’re missing out on any operators in your formula.

#N/A Error

N/A stands for “not available”. The #N/A error message means a value is unavailable for the formula to work upon. Simply put, the formula cannot find what it is looking for. This error is often encountered while using a lookup function like VLOOKUP.

#NA Error Example
#NA Error while using the VLOOKUP function.

As you can see, the VLOOKUP returned a #N/A error in the example. This is because the word “Poland” was not available in the selected range.

To fix the #NA error:

We can add the missing value in the range to rectify this error. Or, we can use the IFERROR function, which will display a custom message if we encounter the error. The syntax of the IFERROR function is:

=IFERROR(value, [value_if_error])

Input Arguments:

  1. value = original formula
  2. value_if_error = value to display if there is an error

In our case, we can replace the existing formula with this:

=IFERROR(VLOOKUP(D2,A2:B7,2,0),"Not Available")
#NA Error Solution
Remove the #NA Error with the help of the IFERROR Function.

There was a problem Popup Error

The popup problem occurs when there is something wrong with the formula. Usually, it means that there is an unwanted character in the formula.

Popup Error Example
Unwanted Character resulting in Popup Error.
Popup Error Message
“There was a problem” Popup Error message.

To fix the Popup error:

Double-check the formula before pressing the Enter key. Make sure the formula is free of unwanted characters or unwanted referenced cells. Once you fix the typing error, the formula should work fine.

#DIV/0! error

DIV stands for “division.” It occurs if you try to divide by 0.

Div Error Example
#DIV/0! error when dividing the number by 0.

You can also encounter this error if you divide by a blank cell.

Div Error Blank Cell Example
#DIV/0! error when dividing the number by an empty cell.

To fix the #DIV/0! error:

Make sure that the denominator is not a ‘0’.

#REF! Error

You will encounter this error called the Reference Error when your formula has an invalid reference. Usually, this pops up when you intentionally or accidentally delete rows, columns, or sheets referenced in other cells.

Ref Error Example
Adding two numbers and returning their sum.

In this example, we add two numbers from cells A1 & B1 and return their sum in cell C1.

Ref Error Example Error
#REF Error example.

If we delete column A, the reference will disrupt the formula and result in a #REF! Error.

You can also encounter this error when the formula refers to itself. It is known as Circular Dependency.

Circular Dependency
Circular Dependency Error example.

As you can see, the formula uses the range from A1 to D1. But, D1 is the formula’s cell.

How to fix #REF! error:

For deleted references, replace the #REF! inside the formula with the correct reference. For circular dependencies, ensure that the formula cell is not within the referenced range.

#VALUE! Error

#VALUE! Error occurs when the formula expects a particular data type but receives different input.

Value Error Example
#VALUE! Error example.

For instance, if you multiply a number with an alphabet, you will encounter this error.

Fix #VALUE! error:

As the error message suggests, review the formula itself. Ensure every input into the formula is of the correct data type. In my case, I will change the value “Ten” to 10.

Value Error Solution
To fix the #Value! Error, make the data types parallel.

#NAME? Error

#NAME? error occurs when there is a problem in the formula’s syntax. Usually, it happens when you misspell the name of the functions.

It also occurs when you use an incorrectly named range or misspell the named range. For example, I have two named ranges, “Fruit” and “Colour,” at cells A2 and B2, respectively. If I misspell any of the two ranges, #NAME? Error will pop up.

Name Error Named Range Example
#NAME? Error when you use an incorrectly named range.

How to fix #NAME? error:

To fix this error, ensure that the syntax used in the formulas or ranges is correctly spelled. You can also check whether the string values used are within the quotation or not.

#NUM! Error

NUM stands for “numerical.” It occurs when the numeric value entered is not valid. The square root of a negative number is invalid, hence the error.

Num Error SQRT Example
Square Root of a Negative Number resulting in #NUM! Error.

It can also occur if you try and calculate a large value, exceeding the limit.

Num Error Large Number Example
The number exceeds the Google Sheets limit, hence #NUM! Error.

Fix #NUM! error:

Double-check all the parameters used in your formula and ensure they are acceptable for the function. In my case, I need to calculate the square root of a positive number.

Phoebe is an HR assistant in her day job, but is also a web blogger that loves writing guides and sharing her experiences. When she is not out with her friends, she enjoys traveling or binge-watching Netflix. She graduated with a BA in Communication and Media Studies from the University of Utah, and is a chief editor at TechObservatory.

Leave a Comment