Google Sheets functions can help you do basic calculations, like SUM, AVERAGE, and COUNT. You can also use formulas to do non-math things, like translate text into other languages and retrieve data from other websites. The only catch is that you have to first correctly enter the formula that tells Google Sheets what to do, which, fair.
If there’s an error in your formula—maybe a typo or invalid cell reference—Google Sheets will alert you with a formula parse error message.
Here, I’ll cover the most common formula parse errors in Google Sheets and how to fix them.
Table of contents
What does formula parse error mean in Google Sheets?
A formula parse error means Google Sheets can’t interpret the instructions you’ve given it. It’s a blanket term used to describe a handful of syntax errors.
Take this formula, for example: =Sample!A3
.
This formula tells Google Sheets you want it to retrieve the value of cell A3 in a worksheet titled “Sample.” But if you accidentally enter, say, =SampleA3
(note the missing exclamation mark), Google Sheets will return an error message.
When Google Sheets returns an error message, it tries to be helpful by telling you the category your error falls into—#REF!, #VALUE, #NAME?, that sort of thing. To get slightly more details about what’s wrong, hover over the cell containing the problematic formula. This way, you can narrow your troubleshooting efforts instead of playing a guessing game. (The only exception to this is the dreaded #ERROR! message.)
How to fix a formula parse error in Google Sheets
Here are the most common syntax errors and how to fix them. Need to fix a specific type of error? Feel free to jump ahead. Or keep scrolling to learn all the ways you could possibly mess up a Google Sheets formula.
#ERROR
#ERROR! is the most frustrating message to receive because it tells you next to nothing about what the error might be. When you hover over #ERROR!, the description simply tells you “Formula parse error.” Not helpful.
In this case, you’ll have to manually review every element of your formula to identify the issue. (The irony of me giving you an equally unhelpful solution to this error message is not lost on me.)
Note: You may need to apply more than one troubleshooting tip to fix your particular error.
#N/A error
The #N/A error message means the value you want Google Sheets to find is missing or not available. This happens most often when you’re using a lookup function, like VLOOKUP or INDEX/MATCH.
For example, in the INDEX/MATCH formula below, I asked Google Sheets to reference the value in cell C17, but there was nothing there. So it returned an #N/A error message.
Fix: Update your formula so that it references values that exist.
#REF error
The #REF! error message occurs when a formula references a cell that no longer exists. Here’s how to troubleshoot the most common sources of #REF! errors.
The reference cell has been deleted
Let’s say your formula contains explicit cell references (where each cell referenced is separated by a comma), but one or more of the cells referenced has been deleted.
There are two ways to fix this.
Fix 1: If reference cells were accidentally deleted, immediately undo the action.
Fix 2: Update the formula so that it references a range of cells—for example, B1:D1—instead of using explicit cell references. For some reason, Google Sheets is still able to process a formula with cell ranges—even if one or more reference cells are missing.
A formula uses relative references
Before we troubleshoot this error, let’s talk about relative references.
A relative reference means that the data used (or referenced) is relative to the location of the cell where the formula was inputted.
In the example below, when I copy the formula =SUM(B2:D2)
from cell F2 and paste it in cell F3, Google Sheets assumes it should add the sum of all cells ranging from columns B to D within row 3 (or the same row that the formula has been pasted into). Whenever a formula containing a relative cell reference is copied and pasted somewhere else in a worksheet, that reference will automatically change.
If a formula with relative references has been copied to another area of the worksheet, or another worksheet altogether, but the reference is impossible, Google Sheets will return a #REF! error.
For example, if the formula =SUM(F2:F7)
is copied to cell H5, Google Sheets assumes you want to add the values of the six cells immediately above cell H5. In this example, that’s impossible since there are only three reference cells available.
Fix: Update your formula to include absolute references. This way, if the formula is copied to another cell within the same worksheet, the formula still maintains the original cell references. To make the cell reference absolute, include a dollar sign ($
) immediately before each column letter and row number that you want to stay the exact same, directly in the formula.
For example, to make =SUM(F2:F7)
absolute, enter =SUM($F$2:$F$7)
.
#VALUE error
The #VALUE error message means your formula contains the wrong type of value.
For example, if you ask Google Sheets to multiply two cells, but one of the cells contains text instead of a number, it’ll return a #VALUE error message.
Fix: Update any referenced cells so that they contain the expected value.
#NAME error
The #NAME? error message can mean a few things. Here’s how to troubleshoot each one.
The function name or a named range is misspelled
Let’s say you accidentally entered =SUMM(A1:A3)
instead of =SUM(A1:A3)
to add the values of cells A1 to A3. The misspelled function name will force Google Sheets to return a #NAME? error.
Fix: Correct the named range or function name.
The best way to avoid typos when entering a named range or function name is to use Google Sheets’ formula and named function suggestions. As you start typing the named range or function name, Google Sheets will automatically suggest a list of named ranges or function names containing the same letters.