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:
What is a Formula Parse Error in Google Sheets?
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:
- Missing input values
- Input is out of bounds; ex. negative number input to a square root formula
- Input of data type has mismatch with the one required by the formula
- Invalid reference
- Misspelt function name
- Invalid numerical values
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.
Fix a #N/A Formula Parse Error
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.
#N/A Due to Missing Value in a Selected Range
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:
1. Click the cell with the error
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:
- Match a set of input values (for row and column labels) to cells within the specified ranges (of row and column labels, respectively)
- Convert them to a cell reference
- Output the value stored in the cell reference
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.
2. Identify the input causing the issue
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.
3. Fix the value or range causing the error
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:
#N/A Due to Missing Input Values
For example, we tried adding an IF formula but we get the following error:
How can we fix this error? Check the steps below:
1. Click the cell with the error
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.
2. Identify the missing input
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.
3. Add the missing input
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.
Fix a #DIV/0! Formula Parse Error
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:
1. Click the cell with the error
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.
2. Identify the input for the denominator
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.
3. Adjust the denominator
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!
Fix a #VALUE! Formula Parse Error
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:
1. Click the cell with the error
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.
2. Identify the invalid input
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.
3. Replace the invalid input with a valid one
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.
Fix a #REF! Formula Parse Error
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:
1. Click the cell with the error
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.
2. Identify the missing input
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.
3. Modify the input or the formula
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!
Fix a #NAME? Formula Parse 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.
#NAME? Error Due to Misspelled Function
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.
1. Click the cell with the error
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.
2. Identify misspelled function
From the same cell, the message is that “hours” is an unknown function. This is actually a misspelling of the HOUR function.
3. Fix the spelling of the 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.
#NAME? Error Due to Misspelled Range
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:
1. Click the cell with the error
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.
2. Click the wrong named range in the formula bar
You can click the wrong named range in the formula bar, and a list of named ranges that may match it will appear.
3. Select the correct named range in the drop-down list
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.
Fix a #NUM! Formula Parse Error
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:
1. Click the cell with the error
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.
2. Identify the input that is out of bounds
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.
3. Fix the out-of-bounds input
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.
Fix an #ERROR! Formula Parse Error
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:
1. Click the cell with the error
Clicking the cell will let you see the formula itself in the formula bar.
2. Identify portion or portions of the formula not following proper syntax
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.
3. Modify the formula to follow Google Sheets syntax rules
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.
Recommended articles
More resources
