How to fix a formula reading error in Google Sheets?

There’s nothing worse than working on a formula in your spreadsheet and receiving an error instead of a result. Here we’ll take a look at the formula parsing errors you may encounter in Google Sheets and how to fix them.

Some errors you see in Google Sheets give you details. For example, the #N/A error tells you that your search value cannot be found. On the other hand, the error labeled #ERROR! in Google Sheets is a headache because it gives you no clue what is wrong.

Let’s take a look at the different formula parsing errors you can encounter in Google Sheets and how to fix them.

3 Ways to Clean Up Your Google Sheets Data

Error: #DIV/0!

This is one of the easiest errors to recognize and fix in Google Sheets. If you see #DIV/0!, it means you are trying to divide by zero or an empty cell.

Here you can see that we are dividing the value of cell F2 by that of cell G2, but the value of G2 is $0.00.

DIV error in Google Sheets

When you see this error, hover your cursor over the cell that contains it. You should see something like “Parameter 2 of DIVIDE function cannot be zero” or similar.

Correction: Check the parameter concerned by the error and correct the null value or the empty cell.

Error: #ERROR!

When you see this error and hover your cursor over the cell, you’ll see the dreaded “Formula Parse Error” line with no further details. This error basically means that something is wrong with your formula.

ERROR message in Google Sheets

The problem could be just as much a missing operator as an extra parenthesis or a bad symbol.

You can see below that we received the #ERROR message! for our formula. Looking closer, you’ll see that we’re missing the operator to join the range of cells we want to sum. There is a space instead of the colon.

ERROR for missing operator

In the following example, we add values ‚Äč‚Äčinto our formula. However, you may see a dollar sign in front of the first value, which is not correct. By default, Google Sheets uses the dollar sign to indicate absolute values.

ERROR using a dollar sign

Fix: The best thing to do when you receive this error is to step through the formula one argument at a time to find the error.

Error: #N/A

The #N/A error occurs when you search for a value that does not exist in your cell range. You may be using the VLOOKUP, HLOOKUP, or MATCH function.

Here we use the VLOOKUP function to find the value of cell B15 (Monday) in the range of cells A1 to F13. Since Monday does not exist in the specified range, we get the error instead of the result.

NA error in Google Sheets

This error is somewhat explained when you hover your cursor over the cell. You can see here that it specifically says “Did not find value ‘Monday’ in VLOOKUP rating”.

NA error message

Fix: Check the lookup value you are looking for within the specified range. Sometimes it’s just a simple typing error in the value or a wrong range of cells in the formula.

Error: #NAME?

If a function name is misspelled, if you’re using one that’s not supported by Sheets, if you have a typo in a defined name, or even if you don’t use quotes, the #NAME error? is displayed.

In this first example, we simply misspelled the AVERAGE function.

#NAME error for misspelled function

And in this example, we made a typo and entered CLOOKUP instead of VLOOKUP.

Name error for misspelled function

Fix: The most common reason for this error is a spelling or typing error, so check function names in your formula carefully.

Error: #NUM!

The #NUM! appears when you have an invalid numeric value or a value greater than the scope of Google Sheets.

For example, here we have a formula that returned more than Sheets can display. You can see this when you hover your cursor over the error.

NUM error in Google Sheets

Fix: Make sure the calculation you want to perform is valid and that Sheets can support the result.

Error: #REF!

This error appears when you delete a cell referenced in the formula or if you try to get a result that does not exist. Let’s take examples.

Here we are simply subtracting the value of cell G2 from that of cell F2. All is well when both cells contain values. But then we delete column G and we see the #REF error appear! because the reference is now missing.

REF error for a missing reference

In this example, we use VLOOKUP to return a value in the 7th column, but this column is out of range.

REF error for a reference out of range

For each error example, hover your cursor over it for help. You can see that the first error tells us that the reference is missing, and the second tells us that the function is evaluating an out-of-bounds range.

Correction: Replace the deleted cell, column or row or simply correct the references in the formula. For a lookup function, make sure the arguments you use are valid.

Error: #VALUE!

The last error we will look at is #VALUE! and is often displayed when a cell you are referring to is not of the correct type.

In this example, you can see that we are subtracting the value of cell F2 from that of cell F1. But the value of cell F1 is a text and not a number.

Value error in Google Sheets

You can hover your cursor over this error to get more details and see that we must have entered the wrong cell reference in our formula.

Fix: Make sure you are using the correct data types for your formula. You can select a cell and use the Other Formats drop-down list on the toolbar to check the data type.

Tools to avoid formula parsing errors in Google Sheets

Google Sheets has a few features to help you work with your formulas, mainly when using functions.

Formula suggestions

When you start your formula with the equals sign and the function name in a cell, you get suggestions from Sheets. You can select a suggestion if it fits your purpose and just add the arguments. If you don’t see any suggestions, enable them by going to Tools > Autocomplete > Enable Formula Suggestions.

Formula Suggestions in Google Sheets

Help with formulas

As you type your formula, you can also select the blue question mark icon that appears on the left. You can then consult the drop-down menu to find out the arguments expected by the function as well as examples.

Help with formulas in Google Sheets

Use the IFERROR function

Another way to stop seeing errors is to use the IFERROR function. This handy tool can provide you with a different result from one of the above error messages or completely hide the error. For more details, see our tutorial on the IFERROR function in Google Sheets.

Mistakes are infuriating, especially when you’re not sure what’s wrong or how to fix it. We hope you find this list of Google Sheets formula parsing errors, explanations, and solutions helpful.

Leave a Comment