Once the Sheets show a parse error, it is simply telling you to correct your formula, arguments, data types, or parameters. A formula parse error is not a single error, there are many other errors under its hood like #N/A error, #error, etc. The parse error does not directly show in a wrong formula, it shows an error (like #error) but when you click on the error, then, in the side dropdown, it says formula parse error as shown in the image below where #Error! Is occurring in cell D11 but when clicked, it shows a formula parse error. Google Sheets does not have a compiler (usually, linked to a parse error in the computing world). When a formula is entered in a Google Sheet, Sheets breaks down the syntax of the formula to analyze, categorize, and understand the syntax by using the parsing function. The parsing process consists of text dissection and the text is converted into tokens. The Sheets parser function will then build a structure based on these tokens and other data received. If Sheets fail to perform the above on any of the formulae, then it will return a parse error. In simple words, parsing is dividing a large structure into smaller logical units for easier data storage and manipulation. Then Sheets re-compiles these as per the instructions and if any of these fail, that may lead to the Formula parse error.
Common Reasons for a Parse Error on a Google Sheet
The following are common reasons for which you may encounter a parse error:
Types of Formula Parse Errors on a Google Sheet
The following are the most common types of parse errors on Google Sheets.
Fixes for a Parse Error on a Google Sheet
As we have covered the basics of the parse error, let us focus on troubleshooting it. But keep in mind that there is not a single size that fits all the scenarios and the troubleshooting process differs on a case-to-case basis. Let us discuss each error type with examples.
1. #N/A Error
This error is derived from the phrase “Not Available”. It mainly occurs in Lookup, HLookup, ImportXML, or similar functions which find a particular value in a given range. If that value is not available in the given range, then that would result in a #N/A error on a Google Sheet. Let us clarify the concept by the example. You can use a similar approach to clear #N/A errors on your Google sheet.
2. #Div/0 Error
Divided by zero is donated as #Div/O in Google Sheets. If any step in your formula divides a value by zero or an empty cell, then that would result in a #Dive/0 error. Let’s clear it by the following example:
3. #Value Error
You may face a #value error on a Google sheet if the data type of at least a cell does not match what is required for the calculations to happen on a particular formula. In other words, a Google Sheet might show a #value error if you try to calculate a single data type (like a number) from two different input data types (like a number and a text string). Let us clear it by an example.
4. #Name Error
A Google Sheet might show the #name error if a function name is misspelled, quotation marks are not present in the formula syntax (if required), or a cell/range name is not correct. We have a very detailed article on our website about #name errors, do not forget to check it. Let us take another example to clarify the idea about the #name error due to values.
5. #Num Error
You may encounter the #num error on a Google sheet if the result of a calculation is larger than the maximum display capacity of Google Sheets i.e., 1.79769e+308. For example, if we multiply fifty-five billion by fourteen billion in a Google Sheet cell, then that will cause a #num error as Google Sheets cannot display such a large number. Another reason for this error is that the input type of a number does not meet the required type of the number type. Let us discuss it through an example:
6. “#Error” Error
If a Google Sheet cannot understand a particular formula but cannot specify the reason for the error (like other errors where we get a hint like in #num error we know that the problem is with numbers), then that could result in a “#error” error. As the reason for the error is not specified, it is a more general error in nature or we can say that if a Google Sheet cannot link an error to any other error types of the parse error, then it will show a “#error” error. It could be a result of missing characters like commas, apostrophes, values, and parameters. Let us understand it with the following example: As this error is general, here are some steps that you may take to clear this type of parse error:
7. #Ref Error
This error might occur on a Google Sheet if the cell references used in the formula are not valid or missing. This error can mainly occur due to the followings:
Deleted Cell ReferencesCircular DependencyCell Reference Out of the Data Range
#Ref Error Due to Deleted Cell References
If a formula is referring to a cell range but that cell range is deleted, then it will cause a #ref error in the formula cell. Let us discuss an example in this regard:
#Ref Error Due to Circular Dependency
If a formula cell is referring to itself as an input range, then that will cause a #ref error due to circular dependency. Let us clarify the concept by the following example:
#Ref Error Due to Cell Reference Out of the Data Range
If you are using a function (like VLOOKUP) to search/extract an entry in a selected cell range but the cell reference given is outside the selected range, thus a #REF! error due to cell reference being out of the data range. Let’s discuss it through an example:
8. There Was a Problem Pop Up
This is probably the most recurring type of parse error. When this error occurs, you cannot do anything on the sheet till you fix or skip the formula. This error mostly occurs if a character is missing from the formula’s syntax or an extra character is present in the formula syntax. You can understand it by the following example:
9. #Null Error
This error mainly occurs in Excel and if you copy the data from an Excel sheet to a Google sheet, then that may show a #null error. If an Excel sheet is uploaded to Google Sheets, then that data may show the #error” error, not the #null error. Then either you clear the #null error in Excel or clear the “#error” error on Google Sheets (discussed earlier).
Functions to Deal Errors on a Large Google Sheet
As the above examples were simple to make the idea clear but on a large sheet, it becomes troublesome to find and troubleshoot errors. We are listing down some Google Sheet functions that make this process easier.
ISNA Function
You can use this function to check the selected cell range for a N/A error. It uses the following syntax:
ISERR Function
If you are interested in all others errors in a range except the #N/A error, then this function will list down all such errors. Following is the syntax of this function:
The ERROR TYPE Function
This Google Sheets function lists down every error on a sheet in numbers. It takes the following syntax: The errors detected and the corresponding numbers are as followings:
If Error Function
If a parse error cannot be rectified due to circumstances, then you may hide it by using the IFERROR function, if no other calculations are getting disturbed. Please use it as a last resort because it can cause unintended issues in the future. You can refer to the #Div/0 Error section to understand the process.
Best Practices to Avoid a Parse Error
It is always better to avoid an error than to waste countless hours troubleshooting it. Here are some of the best practices that you can use to avoid parse errors. Hopefully, we have succeeded in clearing the parse errors on your Google sheet. If you have any queries or suggestions, you are more than welcome in the comments section.
How to Fix #Name Error in Excel (with Examples)DNS Record Types Explained (with Examples)How to add error bars in Google Sheets?How to Remove Duplicates from Google Sheets
