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.
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.
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:
- value = original formula
- 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")
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.
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.
You can also encounter this error if you divide by a blank 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.
In this example, we add two numbers from cells A1 & B1 and return their sum in cell C1.
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.
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.
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.
#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.
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.
It can also occur if you try and calculate a large value, exceeding the limit.
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.