How To Fix Formula Parse Errors In Google Sheets

How To Fix Formula Parse Errors In Google Sheets
Reading Time: 8 minutes

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.

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.

Different types of formula parse errors in Google Sheets

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.

Fix formula parse error in Google Sheets - #N/A error

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.

#DIV/0! error in Google Sheets

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.

Using the IFERROR function to check for blank cell and zeroes

#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.

#REF! error in Google Sheets

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.

Restoring to previous version to undo changes

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.

#VALLUE! error in Google Sheets

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.

#VALUE! error in Google Sheets due to an extra space

You can use the ISTEXT function to check if a cell contains a text.

Using 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.

Formula parse error due to mispelled 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.

Fixing the #NAME! error by correcting the function name

A #NAME? Error due to a mistyped range is shown below.

Name error due to a missing colon

In this case, including a colon (:) in the range fixes the issue.

#NAME? error due to unquoted text

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. 

Fixed #NAME? error

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.

#NUM! error in Google Sheets

Trying to calculate the square root of a negative number likewise returns a #NUM! error.

#NUM! error because mathematical impossibility

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.

Fixing the #NUM! error by using the ABS function

#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.

#ERROR! error due to a missing comma

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.

#ERROR! error in Google Sheets

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/

2 responses to “How To Fix Formula Parse Errors In Google Sheets”

Leave a Reply