Last Updated on September 19, 2023 by Jake Sheridan
Formula Parse Errors in Google Sheets
Formulas are a fundamental part of spreadsheets that help make your life easier.
Apart from those times when your formula doesn’t work so you drop to your knees and scream into the air just like the movies.:
“Why am I getting this error in Google Sheets, why do you forsake me spreadsheet gods!?“
Okay, maybe it isn’t that dramatic, but spreadsheet errors can be annoying and slow down your work.
Just like any other tool, it’s common to run into errors from time to time. Especially if a formula is not set up correctly.
Luckily, most of these errors can be fixed with a basic understanding of what caused them.
In this guide, we’ll look into what a formula parse error is and look into what the possible causes are behind each error.
What Is a Formula Parse Error?
A formula parse error occurs when Google Sheets doesn’t understand the formula you’ve entered due to incorrect syntax, missing arguments, or unsupported functionalities.
You’ll know when it happens because you will see this error pop up:
In other words, it’s a way for Google Sheets to say, “I don’t understand this command.“
For example, you may have mistyped the name of the formula or have only provided one argument when there are two required arguments.
In other cases, errors in Google Sheets imply that the formula cannot be executed for other reasons.
For example, you may encounter an error if you divide a number by 0 or when your output is extremely large.
While these errors are not strictly related to how the user writes the formula, they are still commonly encountered errors in Google Sheets that you may want to resolve.
9 Common Google Sheets Errors (+ How To Fix Them)
If reading through a guide full of spreadsheet error troubleshooting is your idea of hell, here’s the TLDR version.
Need more info? Just click on the error to jump to that section in the guide:
|How to Fix
|Formula parse error message popup
|Arises from syntax mistakes in a formula.
Ensure correct syntax, match functions & parentheses, and include all required arguments.
|Occurs when a formula tries to divide by zero.
Ensure the denominator isn’t zero or use an IF function to handle zero denominators.
|Triggered when a formula returns an invalid numeric output or exceeds Google Sheets’ numeric limits.
Monitor formula inputs and ensure they’re within valid ranges.
|Means a formula can’t find a specified value, often in lookup functions.
Ensure the lookup value exists or use the IFERROR function for custom error messages.
|Indicates that Google Sheets doesn’t recognize a name, function, or formula.
Check spelling/syntax or ensure named ranges are defined.
|Known as an “invalid reference error”, it happens when a formula references a cell or range that’s missing.
Update the formula to reference valid cells or ranges.
|Signifies a data type mismatch in a formula.
Ensure all data types in the formula are compatible and functions receive the correct data types.
|A general error that doesn’t fit other categories, its causes can vary.
Consult Google Sheets documentation or rewrite the formula.
|An uncommon error in Google Sheets but common in Excel, it arises from missing operators or characters in formulas.
In Excel, add the missing elements; in Google Sheets, it typically shows as a #ERROR! error.
Read on to find out more detail about Google Sheets errors (and how to fix them):
1. Formula parse error message popup
The formula parse error message differs from other errors we’ll talk about since its the only one that shows up as a popup message in Google Sheets.
This error is typically triggered after typing a formula with syntax errors.
A formula parse error message popup looks like this:
For example, if we accidentally type an additional closing parenthesis to a function like in the formula “=SUM(A2:A10))”.
How to Fix: Formula parse error message popup
To fix this formula, double-check the formula for correct syntax, ensure all functions and parentheses match, and verify that all required arguments are included.
2. #DIV/0! Error
Users will encounter a #DIV/0! Error if their formula performs a division by zero.
In mathematics, dividing by zero is considered an impossibility, and this same logic is applied in Google Sheets.
A #DIV/0! Error looks like this:
For example, the simple formula =2/0 will result in a #DIV/0! Error.
The error may also be the result of a particular cell or formula being 0 or blank.
In the formula seen above, we encounter a #DIV/0! error because the result of COUNT(A1:A6) is equal to zero and it happens that this function is being used as a divisor in a division operation.
How to Fix: #DIV/0! Error
To fix the #DIV/0! error, you may need to revise the formula to make sure that the denominator is never zero.
However, since there are cases where this cannot be avoided, you can use an IF function to check if the denominator is zero.
For example, the formula =IF(A3,A2/A3,0) allows us to divide cell A2 by cell A3 only if the value in cell A3 is not 0.
If A3 is equal to 0, the IF function will automatically return the third argument, which we’ve set to 0.
3. #NUM! Error
You may encounter the #NUM! error if your formula returns an invalid numeric output or has arguments that are invalid for the formula.
A #NUM! Error looks like this:
For example, if you evaluate the formula =SQRT(-1), it will return a #NUM! Error.
This is because Google Sheets requires the argument of SQRT to be either positive or zero.
The #NUM! error may also occur if the numeric value output is greater than the upper limit of what Google Sheets can display.
In the example above, we tried evaluating the formula =10000^10^10.
Google Sheets returns a #NUM! error since the output of the formula is too large for the program to handle.
Similarly, you may find a #NUM! error if your formula outputs a number that is too small.
How to Fix: #NUM! Error
To fix the #NUM! Error, you’ll need to monitor the input of your formulas.
For instance, trying to find the square root of a negative number will trigger this error.
You’ll need to trace back what values are causing your formulas to return the #NUM! Error.
4. #N/A Error
If you encounter a #N/A error, this typically means that a particular formula can’t find what it’s been asked to look for.
This output often occurs when using lookup functions like VLOOKUP.
A #N/A Error looks like this:
For example, if we try to look up an entry with an ID of 6 in the table seen above, the VLOOKUP will return a #N/A error since it can’t find the value 6 in the lookup column.
How to Fix: #N/A Error
How do we avoid this error? First, ensure that the value you are searching for exists in the data range.
Alternatively, you can use the IFERROR function to handle this error.
With the IFERROR function, you can return a custom string such as “The value cannot be found” or “Invalid lookup value”.
5. #NAME? Error
When working in Google Sheets, users might occasionally come across an error message that reads “#NAME?”.
The #NAME? error is essentially Google Sheets’ way of informing you that it doesn’t recognize a particular name.
This might be a reference to a function, formula, or a specific named range that you have input into the sheet.
A #NAME? Error looks like this:
The most common cause is mistyping a function name.
Another frequent scenario is referencing a name that hasn’t been defined yet.
For instance, if you attempt to call upon a named range that hasn’t been set up in your spreadsheet, Google Sheets won’t recognize it and will display the #NAME? error.
In the example above, our formula returns a #NAME? error because we’ve misspelled the VLOOKUP function.
How to Fix: #NAME? Error
Resolving this error is typically straightforward.
Start by double-checking the spelling and syntax of all function names you’ve used.
If you’re trying to use a named range, go to the named ranges section of Google Sheets and ensure that the name you’re referencing has been defined.
6. #REF! Error
The #REF! error is often termed an “invalid reference error”.
When you see this error, it signifies that a formula in your spreadsheet is attempting to reference a particular cell or range of cells that, for one reason or another, it can’t locate.
A #REF! Error looks like this:
This situation commonly arises when rows or columns that were originally referenced in your formulas are deleted.
For instance, if you had a formula in cell A5 that summed up values from A1 to A4, and then you deleted row 3, the formula in A5 would return a #REF! error.
This is because the reference to cell A3 has become invalid post-deletion.
How to Fix: #REF! Error
Addressing the #REF! error requires a bit of detective work and subsequent adjustments.
Begin by identifying the problematic formula that’s returning the error.
Once located, inspect the formula for any references that may seem out of place or invalid.
If you’ve recently deleted rows or columns, it’s highly likely that these are the culprits.
To rectify the error, update the formula to reference only the existing cells. It’s always a good practice to double-check your formulas after making significant structural changes to a spreadsheet, like deleting rows or columns.
7. #VALUE! Error
The #VALUE! error indicates a data type mismatch in your formula.
This error typically arises when you mix incompatible data types, such as trying to subtract text from a number or add a word and number together.
A #VALUE! Error looks like this:
How to Fix: #VALUE! Error
To address the #VALUE! error, identify the problematic formula.
Ensure all data types within the formula are compatible.
For instance, make sure you’re not mixing text with numbers in calculations.
Furthermore, always ensure that functions you use are being fed the correct data types.
Some functions might require text inputs, while others require numbers.
8. #ERROR! Error
The #ERROR! error signifies a general issue that doesn’t match other specific error categories.
Its causes can be varied, from using intersecting ranges improperly in a formula to other less-defined issues.
A #ERROR! Error looks like this:
In the example above, we provided two arguments but failed to indicate the function to use.
If we select the cell with the error, Google Sheets indicates that this is a type of formula parse error.
How to Fix: #ERROR! Error
Since its reasons can be diverse, consult the Google Sheets documentation or try to rewrite the formula again.
Adjustments to the formula based on the identified issue will resolve the error.
9. #NULL! Error
The #NULL! error is an uncommon error type that is supported by Google Sheets.
While not typically found in Google Sheets, it is common when using Microsoft Excel.
In Excel, the #NULL! Issue occurs when the user forgets to place an operation between two values.
A #NULL! Error looks like this:
This formula may also occur when the user provides arguments to a formula without adding a comma between values.
The user may also encounter this error when they forget to add a colon between the starting and ending cell of a range.
How to Fix: #NULL! Error
In Excel, the best way to fix this error is to identify the missing operator or character and revise the formula.
In Google Sheets, this type of user error will output #ERROR! error instead.
While formula parse errors in Google Sheets can be confusing at first , understanding the root causes and how to fix them can help.
Remember to always double-check your formulas and consider the types and references of data you are working with.
What’s Next?Explore some of the other useful resources on Sheets for Marketers:
- Google Sheets Templates – A collection of over 200 Google Sheets templates for SEO, marketing, PPC and more.
- Google Sheets Tutorials – A growing collection of spreadsheet tutorials for doing (almost) everything in Google Sheets.
- Data Studio Templates – A collection of over 50 Google Data Studio templates for SEO reporting.