Whether you’re just a beginner in Google or a seasoned veteran, you must have come across some kind of errors. If you haven’t, it means you haven’t used it enough; you inevitably will at some point in time.
Mastering Google Sheets is as much about knowing and understanding the errors as it is about knowing the formulas and functions.
So, here’s an article to help you fix formula parse errors in Google Sheets when you encounter them.
- Formula parse errors
- #N/A error
- #DIV/0! error
- #REF! error
- #VALUE! error
- #NAME? error
- #NUM! error
- #ERROR! error
What is a formula parse error?
A formula parse essentially means that the program cannot understand your input. This could be because of one of two reasons: incorrect syntax or incomplete syntax. For example, you may accidentally typed in “+” instead of “=”, mispelled the function name, forgot to enclose text values with quotation marks, or left out a parenthesis.
In some instances, the formula may return an error even if there’s nothing wrong with it syntactically, such as when you multiply two large numbers.
The error messages start with a hashtag (#) and end usually with an exclamation mark (!), some of them with a question mark (?) and others have no special characters.
In order to solve the error, we need to understand what the error is first and foremost; secondly, we need to know what’s causing the error.
So, let us learn about some of the common errors encountered in Google Sheets, and how we can fix them. Check this page for the type of errors in Google Sheets
Formula parse errors in Google Sheets–and how to fix formula parse errors in Google Sheets
#N/A error in Google Sheets
What is the #N/A error?
This error generally appears when the formula can’t find exactly what it’s looking for. This basically means that the value that is being looked for is not available (thus name N/A).
This is usually encountered when working with lookup formulas such as the VLOOKUP and the HLOOKUP functions.
In the example below, we use the VLOOKUP formula to look for the first name of employees by giving their employee ID. The search value (ie, Employee Id) 1003 returns an #N/A error because there is no employee with that particular ID and so there is no corresponding value to look up for the answer.
Any formula that includes the #N/A cell in its range will return an #N/A error. It is therefore crucial to fix the error so it doesn’t mess up other calculations.
How to fix the #N/A error
There is no automatic fix. What we can do instead, to avoid this error, is to look for the best possible match by using fuzzy parameters in the LOOKUP formulas. A better approach, however, is simply to ensure that the data or the input parameter is valid.
#DIV/0! error in Google Sheets
What is the #DIV/0! error?
This is one of the most common errors and it happens mostly because the second value is either a 0 or an empty cell. It also happens when you divide by the MULTIPLY formula one of whose values is a zero, an empty cell, or an error cell.
In the example shown below, when the Amount as well as the Quantity is 0 or when the quantity value is missing, ie, blank, we get an #N/A error.
The average formula can also return an #N/A error. This happens when an empty range is selected as the value.
How to fix the #DIV/0! error
Here, too, we can minimise the likelihood of getting this error by validating the data and making sure that zero or empty cells are not dividing another value.
One way you can verify this is by using the IFERROR function. The IFERROR returns a response the user likes if the formula has an error.
You can get the same answer in the rate by using the IFERROR function while returning a custom result if the formula has an error. In the example shown below, the formula returns “Value 2 is either 0 or blank” when there’s a #DIV/0 error.
#REF! error in Google Sheets
What is the #REF! error?
This error occurs when the formula references a cell that no longer exists. This is usually caused by deleting the cells that the formula is referring to, or when you copied a formula with a relative range and the new range is outside the bound of the range of values.
This error also crops up when you reference the formula cell in the formula values, that is, there is a circular dependency.
How to fix the #REF! error
When you encounter this error, you should see to it that the data in your spreadsheet have not been accidentally deleted. If you have, you can undo it by pressing Ctrl+Z. If the changes have been made in the previous sessions, you can undo the changes by going back to the previous version.
From the worksheet, go to File→Version history→See Version history. Select the version you’d like to go back to and click Restore this version.
If the #REF! error is caused by missing reference because of copying a formula with a relative range, you can click on the formula and replace the part of the formula with #REF! with the appropriate value.
#REF! error due to circular dependency can be avoided by making sure that the formula cell is not selected or entered as a value.
#VALUE! error in Google Sheets
What is the #VALUE! error?
The #VALUE! error appears when a value is not the expected type. This can occur when a function is expecting a number but receives a text value, or when dates are evaluated as text which is what happens when different date formats are evaluated.
How to fix the #VALUE! error
While Google Sheets does a good job of coercing texts into numbers and ignoring the cells with texts, it’s not perfect. In the event that it fails to read the formula and gives you the error, you can hover your cursor over the formula cell and it will give you a brief explanation of what is wrong with the formula.
You can use the ISTEXT function to check if a cell contains a text.
Fixing a #VALUE! error is usually just a matter of entering the right kind of value.
#NAME? error
What is the #NAME? error?
The #NAME? error occurs when Google Sheets doesn’t recognize something. Often, the #NAME? occurs when a function name is misspelled. But it can also occur for a number of other reasons such as entering an incorrect range, misspelling a named range, or entering a text value without enclosing it in double quotes.
Below is an example of a #NAME? error due to mispelling of a function.
.
How to fix #NAME? error in Google Sheets
A #NAME? error is an indication that there is a syntactical issue. Recheck the function name and whether you’ve used the correct name.
A #NAME? Error due to a mistyped range is shown below.
In this case, including a colon (:) in the range fixes the issue.
In the image above, we got a #NAME? error because Google Sheets tries to interpret the text value as a function name, or a named range. Since banana is neither, we need to tell it so by enclosing it in double quotes. This will give us the answer.
So, always remember to enclose text values in quotation marks to avoid getting the #NAME? error.
#NUM! error in Google Sheets
What is the #NUM! error?
The #NUM! error occurs when a calculation cannot be performed either because it’s is mathematically impossible due to logical inconsistencies or because the calculated value of outside the limit that Google Sheets accepts.
For example, trying to find 8^600 returns a #NUM! error. If you get an error because of this reason, perhaps the best solution would be to choose a friendly number–a number that’s not just a number but functionally relevant.
Trying to calculate the square root of a negative number likewise returns a #NUM! error.
If you need to get the square root of a negative number, you could make the formula treat the number as a positive number by wrapping the ABS function within the SQRT function. This will give the square root of the number but for the corresponding positive value.
#ERROR! error in Google Sheets
This error happens when Google Sheets knows something is amiss with the formula but can’t quite tell what, or when it misreads your intention, for example thinking a dollar symbol in the formula as an absolute reference.
In the example shown above, forgetting a comma in the range gives the #ERROR! error. If you enter the range manually, ensure that you do not miss the commas.
In this case, Google Sheets thinks the dollar symbols refer to an absolute reference as the dollar symbol in Google Sheets is used to indicate an absolute reference.
A good practice is to enter the values (currencies, percentages, etc.) in plain numbers and format the numbers from the formatting menu so that Google Sheets does not mistake the numbers for something else.
Conclusion
The types of errors considered here are not all there are but they are some of the most common ones in Google Sheets. Each of these will crop up at one point of time or another if we use Google Sheets long enough. So hopefully this tutorial will help you mend the issues when you do encounter them.
See also
There are plenty more articles on Google Sheets and various things you can do in and with it, such as importing data to Google Sheets from sources such as Spotify, Yahoo Finance, Binance, and so on using. Visit our blog and discover what knowledge awaits you.
Here are some related articles you may find useful:
https://blog.tryamigo.com/regexextract-function-in-google-sheets-for-2022/
https://blog.tryamigo.com/how-to-eliminate-duplicates-in-google-sheets/
https://blog.tryamigo.com/how-to-create-progress-bars-in-google-sheets/