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.
The formula is missing quotation marks
Certain formulas require you to wrap values or text strings in quotation marks. For example, if you want to use a formula to join two text strings, like "Bur" and "rito," together, the formula would be =CONCATENATE("Bur","rito"). But if you accidentally forget to include quotation marks around any of your text strings—for example =CONCATENATE("Bur",rito)—Google Sheets will return a #NAME? error.
Fix: Add quotation marks around the appropriate values or text strings.
#NUM error
The #NUM error occurs when a formula contains an invalid number. There are a handful of scenarios where this could happen. For example, your formula tells Google Sheets to find the square root of a negative number (the number needs to be positive or zero). Or your formula asks Google Sheets to output too large a number. Google Sheets can technically do these calculations, but it can't display the answers properly.
Fix: It depends. Your best bet is to hover over the error message for a description of what's wrong, and then adjust accordingly.
Automate Google Sheets
If the errors we've gone through tell us anything, it's that manual data entry is ripe for human error. With Zapier, you can connect Google Sheets with your go-to apps. This way, you can automate your most time-consuming spreadsheet-related tasks. For example, you can automatically add new lead data and form submissions to an existing spreadsheet. Learn more about how to automate Google Sheets, or get started with one of these workflow templates.
Collect new Typeform responses as rows on Google Sheets
Add Google Sheets rows for new Google Forms responses
Add new Facebook Lead Ads leads to rows on Google Sheets
To get started with a Zap template—what we call our pre-made workflows—just click on the button. It only takes a few minutes to set up. You can read more about setting up Zaps here.
Related reading:
