Google Sheets is a powerful tool that comes with a ton of built-in functions that can be used to do many things.
As you work with spreadsheet programs like Google Sheets, eventually you will encounter a formula parse error. These errors can be pretty annoying, but eventually, with enough practice, you can learn to understand what these errors mean, and how to fix them.
In this tutorial, I am going to cover the common types of formula parse errors in Google Sheets and show you how to fix them.
Table of Contents
What is a Formula Parse Error?
A formula parse error is an error that is returned when Google Sheets does not understand your formula. The formula isn’t working properly and so you are returned an error message.
There are many different things that can cause these errors.
Some common reasons include:
- A typo in the formula
- Missing arguments or too many arguments in a function
- Mathematically impossible calculations
- Cell reference issues
Understanding why your formula is returning an error is important. Below I will cover the different types of error messages, what they mean, and how to resolve them.
There Was a Problem Popup
One common type of error message that you may receive when entering a formula in Google Sheets is the “There was a problem” popup.
This popup will say something like this: “It looks like your formula has an error. If you don’t want to enter a formula, begin your text with an apostrophe (‘).”
This error usually occurs when there is a problem with the formula. Usually, it means there is a character in the formula that shouldn’t be.
If you get this error message, it is likely that you mistyped and have some kind of character in the formula that should be removed.
To fix this, double-check that your formula doesn’t have anything that shouldn’t be there:
Once you remove the unwanted character, the formula should work as normal.
#N/A Error
The #N/A error occurs when a value that the formula needs is not available. It most often occurs if you’re using a lookup function and it cannot find the value that you are looking for.
You can see in the example above, I have a lookup function searching in cells A1:B4.
As you may notice, the very last value in my lookup does not exist in that cell range, so that formula results in an #N/A error message.
The only way to fix this error message is to make sure that the value you are searching for is available wherever you are searching, so double-check and make sure your data ranges are correct. If the value really doesn’t exist, you can hide the error message with a function like IFERROR.
#VALUE! Error
The #VALUE! error will occur when the data type that a formula expects is wrong. For example, if you are trying to do math operations with a text value, this error will occur. Because Google Sheets is expecting number values.
You can see in the example above I have the wrong cell in my formula and it is set to add cells A1+B2 together. Cell A1 is a text value you though, so the #VALUE! error is returned.
This error message can also occur if you have a space in a cell.
If you get this error message, pay attention to the wording of the message that is returned.
In the above example, the error says “Function ADD parameter 1 expects number values“.
This is a great hint that the value that is the problem is the first parameter in my formula.
So to fix this, check and make sure every input into the formula is the correct kind of data, and make sure to read the error message for clues on what is causing the issue.
#DIV/0 Error
The #DIV/0 error message will return if you try to divide by 0 or divide by a blank cell. This is mathematically impossible, so the formula will always result in this formula parse error.
To fix this error, check the parameters used in your argument and make sure none of them is zero or blank.
If they are zero or blank, the only real solution that you will have is to either not use the formula or hide the error message with IFERROR.
#REF! Error
The #REF! error will return when you have a reference that is invalid.
A common issue that causes this error is that formula was set up on a row or column that has been deleted.
Another common reason this error may return is that you are trying to reference a cell that is outside of a data range.
In the example above, I have a lookup function searching columns A:B that is trying to return a value in column 3. However, A:B is only 2 columns, so my formula is trying to lookup out of bounds. This will cause a #REF! error.
Another reason you may get this error is that there could be a circular dependency detected in one of your formulas.
You can see in the example above, there is a circular dependency detected. This is because the formula for column K2 depends on the value column L2 and the formula for column L2 depends on the value in column K2. This will cause an error.
To fix a #REF! error, you need to diagnose what is causing your error.
Check your formulas and make sure the cell range is not out of bounds. If it is out of bounds, fix your formula so that it is only searching for in-bound cells.
Make sure that the cells that the formulas were set up on initially have not accidentally been deleted. If they have been deleted you will have to set up your formula again on a new cell.
If it is a circular dependency that is causing your error, make sure that you do not have formulas that depend on the value of each other.
#ERROR! Error
The #ERROR! formula parse error will occur in Google Sheets when the formula that you have entered is not being understood.
This could be caused by a few different things, from extra or missing parenthesis, extra or missing quotation marks, or other mistakes in your formula.
In the example above, there are extra quotation marks outside the formula and this is causing Google Sheets to not understand the formula which results in the #ERROR! message.
Whenever you get this error message, you need to check your formula and make sure it is set up properly.
#NAME? Error
The #NAME? the error will occur if there is a problem with the syntax of your formula.
It most often occurs if you have misspelled the name of one of the functions.
You can see in the example above the #NAME? error is returning a message stating “unknown function”.
You can also get this error if you are trying to reference a named range that doesn’t exist, or if you are trying to perform a function like CONCATENATE and you forget to put quotation marks around your text values
To fix this error message, check the message that is being returned. If the function is misspelled, make sure you are using the correct spelling.
If it is missing quotation marks around your text values, make sure to add those, and if you have misspelled a named range, you will need to correct that.
#NUM! Error
If you are using a function and you have numeric values that are invalid for the function you are using, this will often return a #NUM! error.
The #NUM! error simply means that the numbers you are using aren’t valid.
For example, here I have a SQRT (square root) function set up on the number -1. But this function only works if the parameter is positive or zero, so it results in an error message.
To fix this error, double-check the function you are using and make sure all the parameters used in your formula are acceptable values for the function.
You will find that one of the numbers might not meet the requirements for the function.
Closing Thoughts
Having errors in your spreadsheet can be incredibly frustrating, but if you take the time to learn what each error means, and how to diagnose your formulas for issues, you should be able to fix most errors that you encounter.
Every once in a while you may come across an error that you really can’t figure out. At that point, you may want to check out some of the various help forums for getting help in Google Sheets.
More Google Sheets Tutorials:
How to Use the ISNA Function
FILTER Has Mismatched Range Sizes