• Home

  • Productivity

  • App tips

App tips

5 min read

What does formula parse error mean in Google Sheets? (And how to fix it)

By Jessica Lau · March 21, 2024
A hero image for Google Sheets app tips with the Google Sheets logo on a green background

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? 

  • How to fix formula parse errors in Google Sheets 

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

Example of a formula parse error in Google Sheets.

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

  • #N/A

  • #REF

  • #VALUE

  • #NAME

  • #NUM


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

Example of an n/a error in Google Sheets.

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. 

Demo of a ref error in Google Sheets caused after the deleting the column containing the formula's reference cell.

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. 

Demo of a formula containing relative cell references being copied and pasted into neighboring cells in Google Sheets.

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. 

Demo of a ref error in Google Sheets caused because the copied formula uses relative references.

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

Demo of a formula containing absolute references copied and pasted to another cell in Google Sheets.

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

Example of a value error in Google Sheets.

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. 

Demo of entering the first few letters of the sum function in Google Sheets and selecting the suggested function name from the drodpown that appears.

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. 

Example of a num error in Google Sheets.

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

Collect new Typeform responses as rows on Google Sheets
  • Typeform logo
  • Google Sheets logo
Typeform + Google Sheets

Add Google Sheets rows for new Google Forms responses

Add Google Sheets rows for new Google Forms responses
  • Google Forms logo
  • Google Sheets logo
Google Forms + Google Sheets

Add new Facebook Lead Ads leads to rows on Google Sheets

Add new Facebook Lead Ads leads to rows on Google Sheets
  • Facebook Lead Ads logo
  • Google Sheets logo
Facebook Lead Ads + 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: 

  • How to fix common errors in Excel

  • Google Sheets vs. Excel: Which is right for you?

  • How to create and use pivot tables in Google Sheets

Get productivity tips delivered straight to your inbox

We’ll email you 1-3 times per week—and never share your information.


Related articles

Improve your productivity automatically. Use Zapier to get your apps working together.

Sign up
A Zap with the trigger 'When I get a new lead from Facebook,' and the action 'Notify my team in Slack'