Check that there are no missing operators, extra parentheses, or incorrect symbols typed into your function.
Also check for missing or incompatible data being used as input in your formula.
There’s nothing worse than working on a formula in your spreadsheet only toreceive an errorinstead of a result.
What Is a Formula Parse Error?
There are other potential causes for errors too, but those are the most common.
Some errors you see in Google Sheets give you details about what went wrong.
For instance, the #N/A error lets you know that your lookup value can’t be found.
On the other hand, the error labeled as #ERROR!
in Google Sheets is a head-scratcher because it doesn’t give you a clue what’s wrong.
Error: #DIV/0!
This is one of the easier errors to recognize and correct in Google Sheets.
When you see this error, hover your cursor over the cell containing it.
You should see something like “Function DIVIDE parameter 2 cannot be zero” or similar.
Fix: Check the parameter instructed by the error and correct the zero value or blank cell.
Error: #ERROR!
This error basically means that something is wrong with your formula.
The issue can be anything from a missing operator to an extra parenthesis to the wrong symbol.
you could see below we received the #ERROR!
There is a space instead of a colon.
In this next example, we are adding values in our formula.
However, you might see a dollar sign in front of the first value which is a no-no.
By default, Google Sheets uses dollar signs to denote absolute values.
You might beusing the VLOOKUP, HLOOKUP, or MATCH function.
Monday does not exist in the specified range, so we get the error instead of the result.
This error does help a bit when you hover your cursor over the cell.
Fix: Check the lookup value you are searching for in the specified range.
Sometimes it’s a simple typo in the value or the wrong cell range in the formula.
Error: #NAME?
And in this example, we mistyped and entered CLOOKUP instead of VLOOKUP.
Error: #NUM!
For instance, here we have a formula where the result is greater than what Sheets can display.
And you might see this when you hover your cursor over the error.
Fix: ensure the calculation you want to perform is valid and that Sheets can support the result.
Error: #REF!
Let’s look at examples.
Everything is dandy when both cells contain values.
But then we delete column G and see the #REF!
error because the reference is now missing.
For each instance of the error, hover your cursor over it for assistance.
Fix: Replace the deleted cell, column, or row or simply correct the references in the formula.
For a lookup function, ensure that the arguments you’re using are valid.
Error: #VALUE!
This final error we’ll look at is #VALUE!
and often displays when a cell you’re referencing is the wrong data throw in.
In this example, you might see we are subtracting the value in F2 from that in F1.
But the value in cell F1 is text, not a number.
Fix: ensure that you are using the correct data types for your formula.
you might then review the drop-down box for the arguments the function expects along with examples.
Use the IFERROR Function
One other way to stop seeing errors is by using the IFERROR function.
For complete details, check out our tutorial forIFERROR in Google Sheets.
Hopefully this list of formula parse errors, explanations, and fixes for Google Sheets helps you.
