In this article we will show how to fix a formula parse error in Google Sheets for all common error types. Simply follow the steps below:
A formula parse error occurs when Google Sheets could not process the formula you entered in a cell.
There are several reasons why Google Sheets could not process the formula. It could be one of the following:
Or it’s something that Google Sheets could not even identify. Most of the time, however, Google Sheets attempts to identify the reason for the formula parse error.
This is what we will learn more about in the article below. We will interpret each error and then learn how to fix those errors. We will use an example formula for each of these errors.
There are several ways an #N/A error occurs. One is when the value you are looking for is missing from the selected range. Another is when a function has missing input values. We will learn how to address both of these reasons in this section.
Formulas for finding values and entries in a spreadsheet are commonly used in Google Sheets. However, they can give errors when the values you are looking for are not present in a selected range.
For example, we have the following INDEX and MATCH functions in a formula:
We want to check why we are getting the #N/A error and fix it. Here are the steps:
When you hover your cursor over the cell containing the error, a message will appear where Google Sheets gives more details of the source of error.
The INDEX MATCH formula works as follows:
For our example, we used INDEX MATCH to find the value given a set of certain input values. However, the formula gave an error saying the value ‘45004’ was not found.
We now need to identify the input that is causing the issue. Specifically, we are looking for the input that has no match in the range.
The error message contains the value ‘45004’. Google Sheets stores dates as integers; in this case, March 19 corresponds to the value ‘45004’. Therefore, the input date (March 19) is the value causing the issue. The input range for the dates range from March 20 to March 26 but the input date is March 19, which is clearly out of bounds.
We can fix the error by changing the input. In our example, we change March 19 to any date from March 20 to March 26:
Afterwards you can press Enter, and the formula will start working:
For example, we tried adding an IF formula but we get the following error:
How can we fix this error? Check the steps below:
If you hover the cursor over the cell, you will also get a message where Google Sheets gives more details on the source of the error.
For our example, Google Sheets identifies that the function has the wrong number of arguments.
The “wrong number of arguments” error means that the function either has missing input or has too many inputs. Let us check the formula:
=if(B1>5)
The IF function has two required arguments: (1) Condition and (2) Value if true, and an optional value if false argument.
The formula giving the #N/A error has only one argument which is the condition. Therefore we need to add the value if true so the formula works. For this example, we will add “Passed” as the output if the condition is fulfilled.
To add the value if true argument, we will write the formula as
=if(B1>5,"Passed",”Failed”)
And then press Enter.
The formula now works.
A #DIV/0 error is a common “mathematical” error in Google Sheets. It appears when the formula you set attempts to divide by zero:
To fix this error, follow the steps below:
If you hover the cursor over the cell, you will also get a message where Google Sheets gives more details of the source of error. For our example, Google Sheets says that “function DIVIDE parameter 2 cannot be zero”. This means that the formula is attempting to divide a number by zero, which is not possible.
You should recall the original purpose of your formula that has a #DIV/0 error.
In this example, the formula is used to get the total percentage scores students got for all the quizzes. You want to divide the total scores (stored in Column E, starting at E3) by the total maximum score (stored in E2). The total maximum score is the same for all the entries in the sheet. .
If you check E2, it is actually empty. Google Sheets interprets this as having a value of 0.
Since the cell supposedly containing the denominator is blank, we can simply input a value here.
Since Row 2 contains the total number of points for the quizzes, we can get their sum through the SUM function containing the range B2:D2:
Press enter afterwards. The error disappears. You can further modify the sheet to your liking!
The #VALUE! error emerges when the data type input for formulas is incorrect. Although Google Sheets has significantly improved its ability to handle various data types, errors may still occasionally occur.
The solution can be straightforward. Here are the steps:
When you hover over the cell, Google Sheets provides an error message with more details.
In our example, it indicates that a particular input is a string when the formula needs numeric input. As our formula divides two numbers, both inputs must be numerical.
We then need to check the cell references in the formula to see which of the two inputs do not contain a number.
For our example, it’s the value of the numerator that is a character instead of a number.
To replace the invalid input, we need to recall the purpose of the formula: it is used to calculate the percentage of the total score. Therefore, the numerator should be the sum of the scores. We replace the stray character with the formula
=SUM(B5:D5)
The formula now works:
If the cell you need to edit is part of a column with similar formulas, you can just drag down a cell so the formula is transferred to the specific cell with the stray character.
This parse error appears when, in the course of editing the sheet, the original cells were deleted, breaking some references to existing formulas. They commonly occur when you delete entire rows and/or columns.
You can repair the formulas with such errors. Check the steps below:
Usually, you can get more details about the source of error if you hover your cursor over the cell containing the error. In this case, however, it simply says that the reference does not exist. Nonetheless, you can still get enough information by checking the formula bar after clicking the cell.
You can deduce from the formula that it is designed to divide a quantity with another one. Unlike other errors, the error code itself appears on the location of the missing reference within the formula:
What we have is clearly a formula for dividing the sum of scores over the total possible score. The missing value is the total possible score which is in the denominator.
We now need to set up the denominator in the sheet. Since the value itself is missing, we need to set a new cell to contain the denominator and edit the formula so that it points to the new cell.
For the value itself, we get the sum of Row 2 values with the range of B2:D2 and insert it in E2:
We then edit the formula to point to E2, but with E2 being an absolute reference with dollar signs to make it fixed:
We do the same thing to other cells containing the error. We now fixed the #REF! error!
There are two main ways this type of parse error occurs in Google Sheets. It’s either a misspelled function or a misspelled named range.
This error appears when Google Sheets could not identify the function or functions you use in your formula. This is often caused by misspelled function names.
Clicking the cell with the error will allow you to see which function or functions are misspelled.
For this example, we want to extract the hours, minutes, and seconds from the timestamps in Column A. We got the column for minutes and seconds working well but the column for hours is not.
From the same cell, the message is that “hours” is an unknown function. This is actually a misspelling of the HOUR function.
We then edit the original formula from using HOURS, which does not exist, to the HOUR function, which is an existing function in Google Sheets.
After editing the formula, press Enter. The formula now works as intended.
You can get the same error when you misspelled a reference you added to a formula. This is possible especially when you declare named ranges in your sheet:
The steps to fix this type of error are as follows:
Clicking the cell with the error will allow you to see which named range or ranges are misspelled.
For this example, it’s the sole named range input that is misspelled.
You can click the wrong named range in the formula bar, and a list of named ranges that may match it will appear.
From the drop-down list, the named range total appears as the closest match to the misspelled named range. We click it.
Then press Enter. The error code disappears and the sheet works as intended.
Sometimes, a #NUM! Error appears for cases where the input number is out of bounds for a specific function.
A common example is having a negative number as an input to a SQRT function. Another one involves using either the SMALL or LARGE functions, specifying an nth rank that is out of bounds of the given range.
For our example, we have a formula for identifying the 7th largest number in the selected range that has ten (10) numbers, but it is giving the #NUM! error.
While the #NUM! error is less common than other errors, you can still easily fix it using the steps below:
When you hover your cursor over the cell with the error, Google Sheets will give you more details regarding the error. In our example, the LARGE parameter 2 value is out of range.
In identifying the input that is out of bounds, you need to consider not just what Google Sheets says is the immediate source of the error but also what the formula originally intends to do.
In our example, we also examine the range, as our initial intent is for the formula to determine the 7th largest number within it. The range is:
A1:A6
This means it only includes six (6) numbers. Therefore, Google Sheets will inevitably struggle to identify the 7th largest number from a set of only six.
Now that we identified the input causing the problems, we can now fix the error.
We change the range from A1:A6 to A1:A10 which is what we originally intended to do:
And then press Enter. The error disappears.
A formula sometimes refuses to work but Google Sheets could not identify exactly why. For this, Google Sheets gives the #ERROR! error code as an output.
Below are some steps you can take to fix #ERROR! Code in your formula:
Clicking the cell will let you see the formula itself in the formula bar.
One reason Google Sheets gives the #ERROR! code is when the syntax is improperly followed. For our example, we want to combine certain strings and numbers to a single cell.
We note that this formula did not either use the ampersand (&) symbol or did not use the CONCATENATE function. Additionally, it did not properly include the reference to the cell containing a numerical value.
As we want to combine the text and numbers in a single string, we set it up properly by enclosing the words in double quotes and by adding the ampersand symbol after each word. The formula becomes
="The"&"Greatest"&"Spreadsheet"&D1
Press Enter and the formula now works as intended.
We hope this article has helped you and given you a better understanding on how to fix formula parse errors in Google Sheets. You might also like our articles on how to select a data range in Google Sheets and how to use the IF function in Google Sheets.
To optimize your workflow, we recommend checking out our free address book template for Google Sheets.
