Every Google Sheet user on whatever level of experience be it, beginner, intermediate or expert has come across multiple types of parse errors when working on some data in Google sheets.
There are many types of parse errors that can occur if the slightest mistake is made in the function or formula syntax or data type.
These errors tend to become disturbing and frustrating if you don’t know how to fix them when they come up.
Don’t worry anymore, by the end of this tutorial you will fully understand what parse errors are, what each error message means, the cause of these errors and how to fix them.
What is a Formula Parse Error in Google Sheets?
Before we analyze the types of errors, we want to know what formula parse errors are.
A Formula Parse Error is simply an error returned by Google Sheets because it doesn’t understand the formula or function used. A formula parse error is displayed when the following occurs.
- There is a typographical error in the formula used.
- Too many or too few arguments are inputted in the function used.
- Performing impossible mathematical operations.
- Wrong cell references in the worksheet.
The above are the most common reasons why formula parse errors are generated on the worksheet.
Next up, we are going to discuss the different types of formula parse errors, their causes and how to fix them.
Different Types of Formula Parse Errors in Google Sheets.
There are multiple formula parse errors in Google Sheets but in this tutorial, we are going to analyze the frequent ones that are most likely to occur whenever you’re operating in Google Sheets.
#N/A Error.
This is the first parse error and the most common error displayed in Google Sheets. The abbreviation means “Not Available”.
It appears when the values the formula is looking for are missing. It also occurs when lookup functions such as LOOKUP, VLOOKUP, HLOOKUP and IMPORTXML functions.
In the sample worksheet below, we have a list of items and we are searching for an item in the range of items using the VLOOKUP function.
However, a #N/A error is generated because Item 5 is not included in the selected cell range.
This means that the VLOOKUP function couldn’t find Item 5 in the cell range and we need to fix what we’re looking for in the worksheet.
The error message states that it couldn’t find the value ‘Item 5’ in the VLOOKUP function.
How To Fix the #N/A Error Message.
Ensure that whatever you’re searching for is actually in the cell range.
Note that this error does not only occur with lookup functions, but the chances of coming across this error are higher when using these functions.
Alternatively, there is a function called ISNA() which helps to validate data to prevent errors anywhere on the worksheet.
#DIV/0 Error.
This error occurs when you are trying to divide a number by zero or an empty cell. It is mathematically impossible to divide any actual number by zero.
So, any value divided by zero on the worksheet would generate this error.
For example, in the worksheet below, we have some numbers in columns A and B and we want to perform some mathematical operations on them.
The results are displayed in column C. In cells A4 and B4, we have numbers five and zero respectively. In cell C4, is a #DIV/0 error.
The error message states that the function DIVIDE parameter 2 cannot be zero. This gives us an idea that it should be positive numbers.
How To Fix the #DIV/0 Error Message.
Locate the cause of the error and replace the zero in cell B4 with a valid number or use the IFERROR function.
This function helps to replace the #DIV/0 text with whatever text you want the cell to display. The function is placed directly in front of the original function. The IFERROR function takes the syntax below.
=IFERROR(value, [value_if_error])
Now, in our sample worksheet, we want to add the IFERROR function into the original formula. So we want the IFERROR function to display a Wrong Division! text so we type in the formula as,
=IFERROR(“Wrong Division!”, (A4/B4))
Click Enter and the #DIV/0 error message is replaced with the Wrong Division! text. This makes the error more appealing and less annoying.
#VALUE! Google Sheets Error.
This error message appears when the data type the formula expects is wrong. In other words, coercing two values of different data types expecting to produce a result for a single data type.
In the sample worksheet below, in column B, we have numbers 8 and 20 in cells B2 and B3 respectively, and we want to add them up. So in the result cell B5, the input formula says =B1+B2.
A #VALUE! error message is generated because cell B1 is the header row containing text while cell B2, contains a number.
This means that a text and a number cannot be added together since they are of different data types.
The error message clearly states that ” Function ADD parameter 1 expects number values but GRADES is a text and cannot be coerced to a number.
Note that if special characters or blank cells are parameters of a formula, the formula would generate a #VALUE! error message.
How To Fix the #VALUE! Error Message.
Double-check the formula’s parameters and ensure the cell references are correct.
If you still have difficulty locating the cause of the error, the error message gives a hint on the cause of the error, read the text and go through the worksheet.
In the example, we’ve discovered where the error is located which is cell B1. So we change the cell reference to B3, that is =B2+B3. It gives a correct answer of 28.
#NAME? Error.
This is another common error message in Google Sheets. This occurs when there is a misspelling in the function name, a reference to an unavailable cell or an absence of quotation marks in the formula syntax.
If you add quotation marks in a formula syntax, the formula evaluates it as a named range. For example, the CONCATENATE function is used in the worksheet.
We have two texts that we want to join together to form a single string but we forgot to add the quotation marks to the texts.
So the CONCAT function considers the text as only one text which generates a #NAME? error message.
The error message could state that there’s an Unknown range name or unknown function depending on the mistake made.
How to Fix the #NAME? Error Message.
Due to misspellings and mistakes in the formula’s syntax, one must check if the spellings and quotations are replaced accurately in the function name and its parameters.
Also, ensure the text values are enclosed in quotations at the front and the end of the text value.
If the error is caused by cell ranges, make sure the cell references are separated by a colon (:).
#NUM! Error.
This error is generated when a formula has invalid numbers such as negative numbers or calculations involving large numbers that are too large.
In the worksheet below, in cell A2, there’s a #NUM! error because we are trying to find the square root of a negative number using the SQRT function.
An error message is displayed stating that “the function SQRT parameter 1 value is negative. It should be positive or zero”
Another instance is multiplying thirteen billion by fifty-five billion which are very large numbers. The standard limit in Google Sheets is 1.79769e+308. The result is more than the limit so it returns a #NUM! error.
How To Fix the #NUM! Error Message.
As noted earlier, the error is caused when there is an issue with the numerical values.
If you don’t have an idea of the cause of the error, click on the error message and it gives a hint on the cause of the error. In this case, the error message says “.
This implies that the negative number is the cause of the error. It should be changed to a positive number or zero.
To fix this, examine your calculation and ensure it doesn’t derive from a negative number. To see the result, you can highlight the syntax in the function (fx) tab.
The result pops up on the top left of the function tab. In a case where there is a large number in the cell, the result in the pop up is #NUM!.
#ERROR! Error Message
This error occurs when Google sheets do not understand what your formula is trying to say but cannot specify where the cause of the error is.
It is a result of missing characters like apostrophes, commas, parameters and values. In the sample worksheet below, a #ERROR! the message is displayed in cell B2, this is a result of forgetting to input a comma in the formula.
It could also be a result of missing opening and closing brackets. Also if there’s a dollar sign used to refer to price in the data, Google sheet might mistake it for an absolute cell reference which would not make sense in the formula.
Other times, if you input a text with an equal to symbol (=), Google sheets would think that you want to use a function, but since the text isn’t an actual function, you could generate an #ERROR! message.
How to Fix the #ERROR! Error Message.
Fixing this error is sometimes considered difficult especially if it appears numerous times on the worksheet or appears in a tricky formula.
This is because it does give any hint at where you can find the error or the cause of the error. But no need to worry, there are some ways to correct and fix this error.
- Double-check if the opening and closing parentheses match accordingly to the amount required.
- Double-check if the special characters such as the commas, colons and apostrophes are placed.
- If your data contains percentage or dollar signs, do not include them in the formula. Be sure to only input them as normal numbers. If the need arises, format them as percentages or currencies later on.
#REF! Error.
This error transpires when the cell reference used in the formula is invalid. There are also multiple causes of the #REF! error.
Omitted and Deleted Cell References.
If the cells used in a formula are missing or deleted, the result cell displayed a reference error. In the worksheet below, we have numerical values 15 and 20 in cells B2 and B3 respectively.
We used the SUM function to add the two cells in cell B4. Assuming we delete cell B2, a reference error occurs.
Circular Dependency Error.
In a case where there are two sets of data with different functions, if there is a single error in one formula, the second dataset also receives an error.
This happens because these datasets are dependent on each other for results. In other words, if a cell is referring to itself in a formula and is acting as the input and output cell, it would result in an endless circle and crashing.
Therefore, the formula generates a #REF! error due to the circular dependency. The error cell states that ” Circular dependency detected. To resolve with iterative calculation, see File > Settings”.
Cell Reference Out of the Data Range.
For instance, you are using the VLOOKUP function to search and extract an item in the selected cell range but giving reference to a cell outside the selected range, a #REF! error occurs.
In the sample worksheet below, the VLOOKUP function is searching for Apples in the ninth and tenth column of the dataset even though the formula only included column G.
This would result in a #REF! error because you want to return a value outside the specific range in the formula used.
The error message states the “VLOOKUP evaluates to an out of bounds range” which is invalid.
How To Fix the #REF! Error Message.
We already wrote a detailed article on how to fix this issue. Click here to check this article.
Formula Parse Error Message Pop-Up.
This error message occurs rarely. If the message is displayed, it won’t let you input any other formula until you fix the error message.
It mostly occurs when there’s an extra character or a missing character in the formula’s syntax before pressing the enter key.
The popup always states that “There was a problem. It looks like your formula is missing one or more open parentheses. If you don’t want to enter a formula, begin your text with an apostrophe (‘).”
In the example below, we’ve unconsciously entered an apostrophe at the end of the formula before pressing the enter key. This misstep would result in the formula parse error popping up.
How To Resolve The Formula Parse Error Message Popup.
Check your formula thoroughly before pressing the enter key. Double-check for extra characters, missing characters and cell references. This is the easiest method of fixing this error message.
#NULL! Error.
This error is prominent in the Microsoft Excel spreadsheet app. Users who also use this app have come across this error message multiple times.
Note that this error occurs only in Microsoft Excel but not in Google Sheets therefore there is no way of resolving such an error in Google Sheets.
Functions To Resolve Formula Parse Error Messages in Google Sheets.
Some users might not like the idea of double-checking and proofreading their formulas multiple times because it might seem confusing to the user.
Google Sheets has provided an alternative way of checking your worksheet for errors by using functions. Here, we are going to discuss how these special functions work.
- The ISNA function. This function checks the selected cell reference for a Not Available error otherwise denoted as #N/A error. This function takes the syntax below.
=ISNA(value)
- The ISERR function. This function checks for every other error in the worksheet except the #N/A error. The function takes the syntax below.
=ISERR(value)
- The ERROR TYPE function. This function is specifically unique because every error detected is returned to a numerical value. The function takes the syntax =ERROR.TYPE(value). The detected errors and the numbers generated are stated below.
1 means #NULL!
2 means #DIV/0!
3 means #VALUE!
4 means #REF!
5 means #NAME?
6 means #NUM!
7 means #N/A!
8 for other random errors that may occur in the worksheet.
These functions are quite efficient and easy to understand and operate. One may even call them shortcuts to finding and correcting formula parse errors anywhere on the worksheet.
Frequently Asked Questions about Formula Parse Error in Google Sheets.
In this section, we are going to answer the frequently asked questions on some error messages in Google Sheets.
How Do I Remove Formula Parse Errors in Google Sheets?
As stated accurately above, there are different formula parse errors in Google sheets and we have highlighted how to fix each one.
Identify the error message in your worksheet and follow the detailed tutorial on how to locate the cause and fix it.
How Do I Stop Formula Parse Error in Google Sheets?
As a Google Sheets user, there’s no way you can completely remove and stop formula parse errors from the worksheet but they can be prevented.
So to prevent them, ensure the syntax, parameters and references in your formula are accurate.
How Do I Get Rid Of #DIV/0 Error in Google Sheets.
The primary cause of the error is when a numerical value is divided by zero. To get rid of this error, ensure that the denominator in the formula is not zero but a positive number.
If the value isn’t directly divided by zero but by subtraction of some digits in the cell references, check them out and increase either value to ensure the resulting number is greater than zero.
How Do I Fix The Error Number in Google Sheets?
The error number otherwise denoted as #NUM!, transpires when the result produces a negative value. Locate the cause of the error and remove any negative values in the formula and replace them with positive values or zero.
Final Thoughts
From the numerous errors encountered in Google Sheets, we have covered the top eight most frequently experienced errors in Google Sheets.
Be sure to always check your formulas thoroughly to prevent these errors from happening. Do you know any formula parse error that wasn’t discussed in this tutorial?
Please let us know in the comment section and we would find a way to fix your error messages.
Now you know How to Fix Formula Parse Error in Google Sheets. I hope you found this tutorial informative and beneficial. Thanks for reading.
