Formula Errors in Excel
MS Excel or Microsoft Excel is currently the most powerful spreadsheet software that enables us to record vast amounts of data in cells formed by rows and columns. Once the data is recorded, we can apply various formulas or functions to get the desired results. But sometimes, when dealing even with the most normal-looking formula, we come across some error codes instead of the desired values or results within the Excel cells. This clearly states that something is wrong. These unexpected results or codes are defined as Excel Formula Errors.
Although formula errors are frustrating, they also help us understand what we have done wrong while applying the corresponding formula. Also, catching and fixing formula errors is easier with little knowledge and experience. To find all errors in an Excel worksheet, we need to launch Go To Special dialogue box by using the keyboard shortcut Ctrl + G and then clicking on the Special button. We need to select the Errors checkbox under Formulas in the dialogue box and click the OK button. This will show all errors at once.
Note: Excel has many distinct functions to help trap and handle different error types. The two common functions are IFERROR and ISERROR, which can catch specific types of errors in Excel. However, they don’t help in tracing logical Excel errors.
How many types of common formula errors are there in Excel?
While working with Excel formulas, we usually face various errors. Formula errors are also part of the most common errors when working in Excel. Formula errors block the execution of functions and make our formulas, dashboards, or reports completely useless. Each type of formula error occurs for a specific reason. We should know why these errors are happening so that we can easily catch them and fix them to get proper results.
The following are the most common formula errors found in Excel:
- #NULL! Error
- #DIV/0! Error
- #VALUE! Error
- #REF! Error
- #NAME? Error
- #NUM! Error
- #N/A Error
- #### Error
- #SPILL! Error
- #CALC! Error
Let us discuss each formula error in detail:
#NULL! Error
Although rare, the #NULL! error occurs because of the wrong value supplied in place of parameters. Generally, it is the result of a typo mistake or the use of space character in the place of the comma (,) or colon (:) between the Excel cell references.
For example, the following image displays the #NULL! error in cell D2. If we check the formula in cell D2, the values in A2, B2, and C2 are calculated using the SUM formula. But, we missed the colon and placed the space character between cell references. The #NULL! error occurs here because the space character is the ‘range intersect’ operator. The error indicates that the specified ranges (A2 and C2) do not intersect. That means an incorrect range operator is used within the SUM function.
How to fix #NULL! Error type in Excel?
To fix #NULL! error, we must use the correct argument separators. In our example, we can remove the NULL! error by replacing the space character with a colon just after the first argument (A2). It is better to avoid using the wrong syntax of the applied formula to eliminate the chances of #NULL! error in Excel.
Despite the above image, we can also add all three arguments using the comma (,), as shown below:
To use proper argument separators or operators, we have to choose the Excel reference operators according to the rules given below:
- Range Operator (colon): used when specifying references to a range of multiple cells.
- Union Operator (comma): used when joining/combining two different references into a single reference.
- Intersection Operator (space): used to return a reference to the intersection of two ranges.
#DIV/0! Error
As the name suggests, the #DIV/0! error usually occurs when the applied formula has values that are being divided by zero or by a cell reference with a value equivalent to zero. Furthermore, this error may also occur when the data has not been given or is not yet complete. This means that the formula is trying to divide the value by an empty cell. In Excel, the empty cells are evaluated as zero.
In other words, the #DIV/0! error occurs when we delete some existing value on which the division formula is dependent within the Excel sheet. In particular, this error is the result of the wrong calculation method. In the same way, if we try to divide any value by zer0 (0) by hand or calculator, it will not work.
For example, the following image displays the #DIV/0! error in cell D3. If we check the formula in cell D3, the value in cell B3 is divided by the value in cell C3, where C3 is an empty cell or a blank cell. Similarly, cell D6 gives #DIV/0! error where both the numerator and the denominator are empty (cell B6 is divided by cell C6).
Although the #DIV/0! error occurs when we divide by zero, it may also come in other applied formulas where we have referenced cells with the #DIV/0! error. For example, when we try to add the value in cell B3 with cell D3 (the cell with error), the SUM formula will also give the #DIV/0! error:
How to fix #DIV/0! Error type in Excel?
To fix #DIV/0! error, we must locate the formula and supply any value which is not equal to zero or empty. In our example, if we provide value in cell C3, the error is fixed, as shown below:
Fixing the #DIV/0! error is pretty easy when we have missed entering the value. However, when we want to use the empty cell as the denominator to enter value later, but don’t want to show the #DIV/0! error, we can display a custom message using the logical function IF:
In the above image, when cell C6 (the denominator in this example) is zero, the defined string (custom message) is displayed. If not, the division of B6/C6 is displayed.
#VALUE! Error
The #VALUE! error usually occurs when the formula does not receive the correct values (or arguments) as per its syntax. In particular, this error is the effect of supplied wrong data types or non-numerical values when the function needs them, respectively. For example, when the cells used within the formula are blank, text values are supplied in functions expecting numeric values, or dates are evaluated as texts, etc.
In the following image, the #VALUE! error occurs in cell D3. If we check the formula in cell D3, the value in cell B3 is added to the value in cell C3, where both the cells (B3 and C3) have text values. Since we need numeric values, to sum up, Excel gives #VALUE! error because of unsupported/ wrong values or arguments used.
Likewise, if we use the MONTH function to get a month value from any text string instead of date, the function gives the #VALUE! error.
How to fix #VALUE! Error in Excel?
To fix #VALUE! error, we must locate the formula with problematic value and supply the right value type. In our example, if we provide numeric values in cells B3 and C3, the error is fixed, as shown below:
Although it is easy to fix #VALUE! error, sometimes it can be difficult to see the problem. For example, the month formula gives the #VALUE! error in the following image even where we have supplied the date:
In the above image, the given date is not the correct format supported in Excel. Thus, it is evaluated as text. Therefore, to avoid the #VALUE! error, we must double-check the formula and the supplied arguments. The supplied arguments must be in supported format with no unsupported special characters.
#REF! Error
The #REF! error is one of the common excel formula errors we encounter while working with data in spreadsheets. This error usually comes when the defined reference becomes invalid or unidentified. Typically, it happens when we delete any cell, row, column, or sheet that has been used as a reference in Excel formula within another cell. Also, the #REF! error is displayed when we copy-paste formulas with relative references to the entire new location where defined references become invalid.
For example, the #REF! error occurs in cell D3. The formula used in cell D3 is copied from cell B8, where the defined range B2:B7 is relative. The corresponding range becomes invalid at the new location (cell D3), and the formula gives #REF! error.
Similarly, if we add the value in cells A2 and B2 to cell C2 and suddenly delete the entire column B, the defined reference becomes invalid and thus the #REF! error occurs.
How to fix #REF! Error in Excel?
When we get the #REF! error after pasting the formulas over a set of cells, we need to locate the cells and double-check their references. Later, we must edit the formula and replace the #REF! with a valid reference.
It is better to prevent #REF! error from occurring due to invalid reference instead of fixing it later. When we need to copy the formulas and use the same in other locations within the sheet, we can use the absolute reference (locked reference) to copy-paste the formula easily without any error.
Fixing the #REF! error can be sometimes easy, depending on what we have deleted before getting the #REF! error. If we have deleted a row or column and see the #REF! error, we can undo the last action and then copy-paste the formula result as values only.
However, if we delete the sheet and see this error, the defined cell references will be gone forever as sheets cannot be restored. In such cases, we have to work again.
#NAME? Error
The #NAME! error usually occurs when the formula does not recognize something important. Despite its name, the #NAME! error does not come because of the wrong person’s name. Instead, this error appears when Excel does not understand the name of the formula we apply in a cell. In most cases, the formula is misspelled. Also, the #NAME! error may come due to a wrong named range or an incorrect cell reference within the formula.
For example, the following image displays the #NAME! error in cell C2. If we check the formula in cell C2, we can see that the function we have supplied to add values does not match the standards of the function defined in Excel. This means we have misspelled the formula and used ‘SUMM’ instead of ‘SUM’, as shown below:
How to fix #NAME? Error in Excel?
It is easy to fix the #NAME! error in Excel. We must locate the error cells and double-check the spelling or syntax of functions applied in corresponding cells. We can select the specific cell and look for the formula bar to ensure whether we have entered everything correctly. If not, we can change the formula spelling or correct the syntax from the formula bar.
Since ‘SUMM’ is incorrect, we replace it with ‘SUM’ and hence no #NAME! error exists on the sheet.
It is better to avoid manually entering the formulas and using Excel’s autofill option. Whenever we start entering characters after the equal sign in an Excel cell, Excel lists relevant formulas. We can scroll down to the desired function name from the list and press the Tab key on the keyboard to use that function. After pressing the Tab key, the function is inserted, and its syntax is also displayed. This way, we can eliminate the chances of wrong inputs for functions and avoid #NAME! error.
#NUM! Error
The #NUM! error usually occurs when an Excel formula consists of numeric values that aren’t valid. The numeric value is either too small or too large in most cases. Furthermore, the error may also come when using the numeric values that do not meet the criteria of the formula or whenever calculation could not be performed.
For example, the following image displays the #NUM! error in cell B2. If we check the formula in cell B2, we can see that the function SQRT is used to calculate the square root of the negative number, which is not mathematically possible.
Additionally, suppose we need to use the currency or other number format with formula results in Excel. But, instead of using only the value (e.g., 1000), we use the values with the currency sign as in currency format (e.g., $1000) within the formula. In this case, the formula will return #NUM! error in a cell where it is used because the calculation isn’t possible with the currency sign within the formula.
How to fix #NUM! Error in Excel?
It is easy to fix the #NUM! error in Excel. To make a calculation possible, we must locate the error cells and adjust the given inputs as required by the formula. In our example, if we remove the negative sign, the calculation will be completed, and the error is gone.
If we need the negative value, then we can use the negative sign before the function, as shown below:
Also, we must ensure that we haven’t used any formatted currency, dates, or special symbols, within the formula, which is different than the arguments required by the corresponding formula. If used, we need to remove such formatting or characters from the applied formula and keep only the numeric values. It is recommended to adjust the formatting after the calculation has been completed.
#N/A Error
The #N/A! error usually occurs when an Excel formula consists of values that aren’t available. The error means “NOT AVAILABLE”, which states something is missing or misspelled. The cause of this error may be accidentally deleted numbers of rows that have been used within the formula. Moreover, this error may occur due to extra space characters, misspelled values, or incomplete tables in lookup functions.
The #N/A is mostly faced by users while working with the classic lookup functions, such as LOOKUP, VLOOKUP, HLOOKUP, etc. It occurs when a specified cell or referenced cell is not located by a formula in a VLOOKUP.
For example, the following image displays the #N/A! error in cell E2. If we check the formula in cell E2, we can see that the VLOOKUP function is trying to look for ‘Eggs’, which isn’t present in the lookup table.
How to fix #N/A Error in Excel?
To fix the #N/A error, we must double-check all our formulas and closely look at the referenced cells. We must ensure that no sheets, rows, or cells have been deleted or incorrectly referenced within the formula. When we have multiple formulas linked together in an Excel sheet, we must ensure that every formula has proper value as per syntax.
In our example, if we change the value in cell E2 from ‘Eggs’ to ‘Coffee’, the VLOOKUP will function as usual.
#### Error
In most cases, the #### error usually occurs because of the formatting issue. When we enter formulas with long format results in Excel cells, and the width is not wide enough, Excel gives #### error.
In the following image, cell A2 displays #### error because the cell’s width is not wide enough.
Sometimes, we think that the #### error is caused due to insufficient width; but this is not always the case. The error can also be caused when we try to display negative times, dates, etc., because these values cannot be negative.
How to fix #### Error in Excel?
Although it seems difficult, it is easy to fix the #### error. We only need to make the column width wider.
#SPILL! Error
The #SPILL! error occurs when the applied formula tries to return multiple results but cannot record them to the respective grid. In other words, the formula outputs a spill range that executes through a cell that already has data. This means the spill range for a spilled array formula isn’t blank.
For example, the following image displays the #SPILL! error in cell D5. The UNIQUE function tries to extract the unique names from the selected range (B5:B10) into a spill range starting from cell D5. Since cell D8 already contains the value ‘x’, the execution is blocked, and the #SPILL! error is returned.
How to fix #SPILL! Error in Excel?
To fix #SPILL! error, we must locate all the obstructing cells that block the formula from execution. Next, we need to go through each obstructing cell and clear the cell’s value. Once the obstruction value is removed, the array formula will spill normally. In our example, if we remove ‘x’ from cell D8, the formula spills as intended.
#CALC! Error
The #CALC! error appears when the applied Excel formula faces a calculation error with an array. Technically, Excel’s calculation engine encounters a scenario that is not supported.
For example, the FILTER function filters the source data in a range B5:D11. But, the formula is looking for the data in the category ‘x’ that isn’t present within the sheet. Thus, the #CALC! error appears in cell F5:
How to fix #CALC! Error in Excel?
To fix the #CALC! error, we must ensure that the formula and supplied values exist within the specified range. In our example, if we modify the formula and apply the filter on group ‘A’ instead of ‘x’, the formula works as usual.
Important Points to Remember
- We can use IFERROR to encounter any formula error.
- Two errors, SPILL and CALC errors, are related to “Dynamic Arrays” and are found only in Excel Office 365.
