How To Fix Formula Parse Error Google Sheets

While using Google Sheets, the most common errors you can run into are formula parse errors. There are many different formula parse errors that denote what exactly is wrong in your formula.

They are commonly caused by typos or mistakes in your formula. Some formula parse errors can also occur when you reference an unreachable cell or if you try to calculate the wrong data types. In such cases, Google Sheets won’t be able to understand your formula and will return an error message instead.

How to Fix Formula Parse Error in Google Sheets?

There are many different formula parse errors in Google Sheets, and they appear depending on what is wrong with the formula in a question. So, you will have to understand what the individual formula parse error means to be able to fix it.

There Was a Problem Popup

This error occurs when there are additional unnecessary characters, such as  /, \, <, >, and ^ in the formula, especially at the end. Google Sheets will be able to understand that you’ve typed in a formula, but it can’t execute it due to the unwanted character.

#N/A Error

The #N/A Error occurs when a value you’re looking for is unavailable or when Google Sheets can’t provide the value due to an incomplete formula.

In most cases, this error appears when using a function to look for a particular value. For instance, you may use the Vlookup function to look for a particular value within a range. If that value is not present within that range, you will get the #N/A error.

You will also get this error when you do not have all the necessary arguments within a formula for Google Sheets to be able to execute it. For example, within a VLOOKUP function, if you do not specify which range to search a value for, Google Sheets will not be able to find that value for you. It will then return the #N/A error.

#Value! Error

The #Value! Error typically appears when the formula you’ve written gets the wrong data type for input than the one it needs.

For instance, the formula you’ve written may have tried adding the content in cells A2 and B1 together. However, if cell A2 contains numbers and Cell B1 contains text, Google Sheets won’t be able to add them together. In such cases, it will instead return the #VALUE! error.

You can also get this error when performing a function with different date formats. For example, if you use Google Sheets in the US country setting, 12/25/2022 (MM/DD/YYYY) will be stored as a numerical value in your Google Sheets.

For country settings other than US, 12/25/2022 will be stored as a text value. It is because the rest of the world follows the DD/MM/YYYY date format, and there doesn’t exist the 25th month. If you try to subtract a US date format with other date formats, Google Sheets will instead return the #VALUE! Error.

Sometimes, you may also get the #Value! Error when calculating a numeric value by a blank cell. A blank cell should be 0 in numeric value, but if you get this error, the blank cell is probably occupied by spaces, making it a text value.

#DIV/0 Error

The #DIV/0 error appears when dividing any numeric value by zero or a blank cell and vice versa.

As you can’t divide any number by 0, Google Sheets will return the #DIV/0 error. If you try to divide zero by any other number, you will also get this error.

#REF! Error

The #REF! error occurs when you refer to a cell that can’t be referred to. It mostly happens due to three reasons:

Reference Cell Got Deleted

When a formula refers to a cell that later got deleted, it will show the #REF! Error. For example, the A1 cell contains the formula =C4+B4. If the B4 cell gets deleted, the formula in the A1 cell won’t be able to refer to the B4 cell. So, it will show the #REF! error.

You have to delete the whole cell row or column for this error to occur. The contents of an individual cell getting deleted won’t trigger this error.

Out of Bounds

This particular error can also appear when a cell can’t be referred to due to formula arguments.

For example, if you have the =VLOOKUP(F2,A1:B3,5,false) formula in  cell A1, it will result in the #REF! error.

The error in this example occurs due to the following reasons:

• The A1:B3 range argument tells the formula to look for the F2 search key in the A1 to B3 cell.
• The 5 index argument in the formula says it wants the information present in the fifth column, which isn’t in the A1 to D8 range, as it is the E column.

Circular Dependency

If the formula in a cell refers to its own cell, it will also cause the #REF! Error. This is known as circular dependency. In such a case, the cell will keep trying to reference itself endlessly in the formula.

For example, if the A1 cell contains the =A1+A2 formula, it will refer to its own cell, causing circular dependency.

#ERROR! Error

The #ERROR! error appears when there is something wrong with your formula or when it is incomplete. It can also occur when there are some unwanted characters in the formula.

This error can also occur if you try to include specific symbols as numeric values. For example, if you try the =$100+$100 formula, Google Sheets will not treat the $ symbols as numeric values. It will thus return the #Error! error.

#NAME? Error

The #Name? error means that your formula is unknown or you are quoting an unknown range name. It usually occurs due to misspelled functions.

For example, if you input only the Vloookup (wrong spelling) function, you will get the #NAME? Error “unknown name range.” It is because Google sheets will not know it is a formula and will treat it as a range name instead.

A range name is the name of a particular cell range defined by the user. However, as there is no range name called Vloookup, you will get this error.

On the other hand, if you input the Vloookup(I18,A1:I18,2,false) formula, Google Sheets will know it is a formula due to the arguments following the function. However, it won’t know what function Vloookup (wrong spelling) is and will return the #NAME? Error “unknown function: Vloookup.”

If you forget to put quotation marks in the concatenate function, you will also get the same “unknown name range” error.

#NUM! Error

The #NUM! error occurs when the formula contains numbers that aren’t considered valid or can’t be displayed by Google Sheets.

It mainly occurs when using the Small or Large function. For instance, if you use the Small function to find the 6th smallest number in your data set of 5 numbers, you will get the #NUM! Error.

The other instance you can get this error is when you try to get the square root of negative numbers. While you can still do so in Google Sheets, you will have to change them to complex or imaginary numbers first.

Lastly, you can get this particular error when calculating numbers that are larger than 1.79769E+308 (309 characters long), which Google Sheets can’t display.