Formulas in Google Sheets were meant to make things easier for us.
However, every Formula user, regardless of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will).
These errors can often be quite frustrating, especially if you don’t understand what they mean.
In this article, we will talk about each common formula parse error in Google Sheets, what they mean, and how to trace the cause and correct the problem.
This Article Covers:
What is a Formula Parse Error?
A formula parse error occurs when Google Sheets is unable to understand your formula. There may be a number of reasons for this, for instance:
- There might be a typo in the formula.
- There might be more or fewer parameters than the number expected for a specific function.
- One or more parameters entered might be of a different type from what is expected.
- Cell references in your formula might be out of bounds.
- You might be trying to do a calculation that is mathematically impossible.
There may be a number of other reasons, and all these reasons make it difficult for Google Sheets to fulfill what is requested by your formula. As such, it returns an error message.
We are going to take a look at some common error messages and explore what they mean in the following section.
Finding the cause of the problem is of utmost importance, in order to solve it. Thankfully, Google Sheets tries to help you out by suggesting what might be wrong with the formula, so you can break it down and detect the root of the problem.
Different Types of Formula Parse Errors in Google Sheets
Let us first take a look at what kinds of different error messages you are likely to see:
#N/A Error
This error usually occurs when a particular value that the formula needs is not present. The ‘N/A’ in the error message simply means ‘not available’. In other words, the value is not available for the formula to work on.
This problem is often encountered when you use lookup formulas like VLOOKUP in Google Sheets.
For example, if you’re looking for a value and that value does not exist in the given range, you will most likely see a #N/A error.
In the image below, the VLOOKUP function needs to find the value ‘Tom’ from the range A:B. But since the value ‘Tom’ does not exist in the range, the function simply returns a #N/A function to indicate that the value ‘has not been found’.
How to Fix the #N/A Error
The #N/A error does not always signify a problem with your formula itself, but a certain circumstance (the absence of a value) that results in an error.
To rectify this error, you can combine the formula with an IF statement that displays a custom message if the #N/A error is encountered. A very useful function to handle these kinds of issues is the IFERROR function.
For example, we rectified our problem below by using an IFERROR statement to display a “Search Value not found” message instead of an #N/A error:
#DIV/0 Error
This error occurs when a number in the formula is being divided by zero. This makes no sense mathematically, so the function returns a #DIV/0 error. For example, if you are trying to divide a value by a function or operation that results in a 0 value, you will get a #DIV/0 error.
In the image below, we are trying to divide the number 7 by the difference between values in A1 and A2, which results in 0. Since it is not possible to divide 7 by 0, the formula returns a #DIV/0 error.
This error also happens when you are trying to apply the AVERAGE function on a range of blank cells. This happens as the AVERAGE function needs to divide the SUM of the values with the number of the values, and if the range is blank, it’s equivalent to dividing by 0.
How to Fix the #DIV/0 Error
To resolve this error, you can try to first find out why your denominator is evaluating to a ‘0’ value. Select parts of the denominator in the formula bar to see what each part evaluates.
You can see the result of the calculation is a little popup right on top of the formula bar. Once you find the cause, you can make changes to your formula accordingly.
If that does not work, you can check if any part of the formula’s denominator links to a blank cell or range. If so, then you can either fill required values into the cell or select the required range for the formula.
To avoid problems like this altogether, you can again use the IFERROR statement to display a particular message whenever an error is encountered:
#REF! Google Sheets Error
This error occurs when you have an invalid reference in your formula. There may be different types of invalid references:
Missing Reference:
This kind of #REF! error occurs when you might be referencing a cell in your formula that is missing. In the image below, we have two values (45 and 50) in cells A1 and A2. We used the formula: =A1+A2 to find the sum of these two numbers in cell A3.
Till now it is fine. But what happens if we delete row 2? Then the value in cell A2 goes missing. This causes the formula in cell A3 to return a #REF! Error.
Circular Dependency:
This kind of #REF! error occurs when your formula might be referencing itself. In the image below, we used the formula =SUM(A1:A3) in cell A3. This means the formula, in addition to referring to values in A1 and A2, also refers to itself.
So the function will keep going round in circles trying to be both an input and an output cell, which would result in an infinite loop and a system crash. So to avoid this, the formula detects the circular reference and returns a #REF! error.
Lookup that is Out of Bounds:
This kind of #REF! error occurs when using VLOOKUP, and trying to reference a cell that is outside the bounds of the VLOOKUP range parameter.
In the image below, the VLOOKUP formula is trying to look for the search parameter in the third column of the source table, even though the range parameter specified only consists of 2 columns.
Since you’re trying to return a value outside the range specified by the formula, it returns a #REF! error.
How to Fix the REF Error
Read the error message to find out what kind of #REF! error your formula has. If there are missing references, you can see the exact reference that is erroneous in the formula bar. The reference causing the problem will be replaced with an #REF!.
Once you’ve identified which cell is missing, you can replace the #REF! with the correct cell reference.
If there’s a circular dependency issue, then you need to identify the range of cells in the formula and change it to make sure it does not include the current cell too.
Finally, if there’s an out of bounds error, then either make sure the search term exists in the search table or make required changes to the search term itself. You can also avoid this type of error from happening by using the IFERROR function.
#VALUE! Google Sheets Error
This error occurs when one or more parameters in your formula are of a different type than what is expected. So if a function only accepts numbers as a parameter, but you have a text value in the cell being referenced, then you will end up with a #VALUE! error.
In the image below, a backslash character (\) got added to the end of the number in cell A2 by mistake. So when we use the formula =A1+A2 in cell A3, we are trying to add a number to a text value, which is invalid. Therefore, the formula returns a #VALUE! Error.
Similarly, in the next image shown below, the cell A2 has a space in it, which makes it get treated as text. If the cell had been blank, then the ‘+’ operator would have assumed the value as a ‘0’.
Again, since we are trying to add a numeric value to a text value (‘ ‘), the formula returns a #VALUE! error.
How to Fix the #VALUE! Error
Once again, the error message should give you a clue about the source of the error.
To resolve the #VALUE! error, you can try one of the following:
- Look for errant spaces either in your formula or in the cell range specified in the formula. You can easily tell if a cell is blank or contains a space character by clicking on the cell observing how it looks in the formula bar. When you click on the formula bar, you should see a flashing cursor. If there is a space between the flashing cursor and the character next to it, that means there exists a space character there.
- Check to see if any cell in the range contains a text value, even though it looks like a number. You can tell if it is a text value by clicking on the cell and observing how it looks in the formula bar. If you see an apostrophe preceding the value, it means the value is a text value.
- You might get a #VALUE! error even if there are spaces inside a date value. So, check these values and correct them if you see any spaces.
#NAME? Error
This error commonly occurs when there is a problem with the syntax of your formula. It could be due to a spelling mistake, an incorrect named range, or the presence or absence of quotation marks.
For example, if you forget to add quotation marks around a string value, then the formula considers it as a named range, and if a named range by that name doesn’t exist then it returns a #NAME? Error.
In the image below, we did not put quotation marks around the first parameter of the CONCAT function, causing it to consider the first parameter “Peter” as a named range. Since it cannot find a named range by that name in the sheet, it returns a #NAME? error.
Related: Google Sheets CONCATENATE
Similarly, in the next image below, we misspelled the function name AVERAGE, causing the formula to return a #NAME? error.
Again, in the next image below, as you can see, we missed adding a comma in between the cell references A1 and A2. This is a syntax error, due to which the AVERAGE function returns a #NAME? error.
How to Fix the #NAME? Error
Since this kind of error usually occurs due to a syntax issue, the best approach is to first check if there are any spelling mistakes in the function name or named range names.
You can also check to see if all string values are enclosed in quotations and that there are both opening and closing quotations for every string value.
If using cell reference ranges, check to see if you have them separated by a colon symbol. (‘:’).
#NUM! Error
This error occurs when your formula has invalid numeric values. For example, if you try to find the square root of a negative number or if a calculation results in a very large number that is outside the scope of Google Sheets.
In the image below, we are passing a negative value to the SQRT function, which is not acceptable, so the function returns a #NUM! error.
Similarly, in the next image shown below, we are trying to find the result of 150 raised to the power of 200, which is a very large number (more than 1.79769e+308, which is the limit). The formula thus returns a #NUM! error.
How to Fix the #NUM Error
This error invariably means there’s an error with one or more of your numeric arguments.
Usually, the error message gives a good clue about the issue causing the error. For example, if the error message says “Function SQRT parameter 1 value is negative.
It should be positive or zero.” it’s quite clear that your first parameter in the SQRT function evaluates to a negative number.
So to resolve it, you can re-evaluate your calculation to make sure it doesn’t result in a negative number. To see what the result of an operation is, you can select the operation by highlighting it in the formula bar. You should be able to see the result of the calculation is a little popup right on top of the formula bar.
Keep in mind though, that if an operation evaluates to a very large number, you would not be able to see its result in the popup. This, in itself, can give you a sign of the source of the #NUM! error.
#ERROR! Message
This type of error is unique to Google Sheets. It usually occurs when Google Sheets cannot make sense of your formula, so it cannot tell you exactly what is wrong with it. There may be a number of reasons you see this error.
It may be because you forgot to add an important operator between cell references, values, or parameters. For example, forgetting to add a comma between cell references of non-contiguous cells in your sheet.
It could also be because your number of opening brackets doesn’t match the number of closing brackets. In the image below, we used a $symbol to refer to a price amount, but Google sheets mistake it for an absolute reference and the formula, then, does not make sense.
Sometimes this error also occurs if you don’t want to enter a formula, but want to start your text with an equal to sign (‘=’). However, as soon as Google Sheets sees the equal to sign, it assumes that you are trying to use a formula. But the text following the equal to sign does not make sense, so you get a #ERROR message.
How to Fix the #Error
Resolving this error can sometimes be tough, especially if you have a particularly complex formula. This is because the error message does not give any clues about the error. However, there are a few steps that you can take to see if it helps resolve the problem:
- Check if the number of opening and closing brackets match and that they do not cause any logical errors.
- Check if there are colons and commas separating contiguous and non-contiguous cell/range references.
- If using currency or percentage symbols, make sure you remove them before applying them to the formula. If you need to use them, you can enter them as plain numbers and later format the result with an added percentage or currency symbol.
Formula Parse Error Message Popup
You see this error message in the form of a popup that does not let you enter your formula until you resolve the error. One comes across this error quite rarely.
It usually occurs when there’s some fundamental problem with your formula. In most cases, you will see it if you accidentally entered a character or symbol in your formula before pressing the return key.
In the image below, we accidentally entered a backslash character at the end of the formula before pressing the return key. This type of mistake is quite common since the backslash key is right above the return key on the keyboard.
A mistake like this will most likely result in a formula parse error message popup.
How to Fix the Formula Parse Error Message Popup
It’s best to avoid this error from happening in the first place. Always double-check your formula before hitting the return key.
Make sure there are no surplus characters hidden in your formula. Also, make sure there are no missing characters or cell references.
The #NULL Error in Google Sheets
There’s no fix for this error. But only because it doesn’t exist in Google Sheets. It’s only an Excel error.
Functions to Help Fix Formula Parse Errors
There are a few functions that you can use to help identify formula issues and resolve them. Let’s take a look at them and what they do.
=ISNA(value)
Checks whether the cell has an #N/A error
=ISERR(value)
Checks for every other type of error (not including #N/A)
=ERROR.TYPE(value)
Shows the type of error by returning a number value:
- 1 for #NULL!
- 2 for #DIV/0!
- 3 for #VALUE!
- 4 for #REF!
- 5 for #NAME?
- 6 for #NUM!
- 7 for #N/A
- 8 for all other errors
Google Sheets Formula Pass Error Extra Tips
Sharing with Other Countries
Some international sheets may use semicolons ; instead of commas , so try changing those out if you’ve copy-pasted a formula.
Highlighting
If you double-click the cell with the formula, sheets will usually underline the misfunctioning part of the formula in red. That makes it easier to pick apart your formula.
Formula Parse Error in Google Sheets FAQ
What Does Formula Parse Error Mean?
A formula parse error essentially means there is an error in the way you’ve entered a formula into Google Sheets.
How Do I Remove Formula Parse Error?
Troubleshooting will remove a formula parse error. There are several different types of errors that can occur. Click the cell to find the type you need to fix then follow our guide to fix that particular error.
How Do I Stop Formula Parse Error in Google Sheets?
The most common reason for a formula parse error is a mistyped formula. So, to minimize them make sure you’ve got the syntax and the figures right.
How Do I Get Rid of DIV 0 in Google Sheets?
The #DIV/0 error occurs when the denominator results in a zero. You can’t divide by zero, so check your formula to find where the cause for this error could be popping up.
How Do I Fix the Error Number in Google Sheets?
The #NUM error occurs when the result unexpectedly returns a negative value in a formula that only works with zero or positive numbers. You need to find the part of your equation that is causing the negative value result.
Fixing Your Formula Parse Error Google Sheets Issues
In this tutorial we discussed formula parse errors in Google Sheets in detail, explaining what they are, why you see them, and how to resolve them. We hope our guidelines help you come to terms with parse errors in your Google Sheets. If you’re still having trouble fixing a formula parse error, feel free to ask about it in the comments below.
Related:
