What the Excel formula errors mean, and how to fix them. Make your own hash errors, to use with formulas.
When there are problems with an Excel formula, Excel shows its built-in error values, like #N/A, or #REF! See an example of what causes each type of error value, and where you can get help on fixing those problems.
Here is a list of the built-in Excel formula error values. Microsoft calls them hash errors, because of the character at the beginning.
There is a brief note on each hash error below, and a link to the related Microsoft help page,
Meaning: Numeric values aren't valid, or an iteration function can't find a result, or the formula result is a number that's too large or small to be shown.
Troubleshooting Info: #NUM! Help
Example: The DATEDIF formula in cell D4 returns the #NUM! error because the end date is earlier than the start date. Correct one of the dates, to fix the error.
Meaning: Something is wrong with the formula or the cells that it references. Microsoft admits that this error value is vague, and it can be difficult to find the cause.
Troubleshooting Info: #VALUE! Help
Example: The simple formula in D4 returns the #VALUE! error, because there is a space character in C4. Sometimes people do that to "clear" a cell. To fix the error, select cell C4 and press the Delete key.
Meaning: The formula can't find the thing it was asked to look for, usually with VLOOKUP, MATCH, etc.
Troubleshooting Info: #N/A Help
Example: The formula in C4 returns the #N/A error because the Item in B4 was not found in the lookup range. To fix the error, type an "s" at the end of "Pant", in cell B4.
Meaning: The divisor is a zero, and Excel can't divide by zero.
Troubleshooting Info: #DIV/0! Help
Example: The formula in D4 returns the #DIV/0! error because there is nothing entered in C4. A blank cell is equal to zero, and Excel cannot divide by zero. To fix the error, type a number in cell C4.
Meaning: The formula refers to a cell that isn't valid. Perhaps a column or row is deleted, or a VLOOKUP range has 3 columns, and you ask for a result from column 4.
Troubleshooting Info: #REF! Help
Example: The formula in C4 returns the #REF! error because the lookup range has only 2 columns and the VLOOKUP formula refers to column 3. To fix the error, change the column number to 2.
Meaning: Usually caused by a type in a function name or a defined name. Or, you put text into a formula, but didn't enclose it with double quote marks
Troubleshooting Info: #NAME? Help
Example: The formula in C4 returns the #NAME? error because the defined name "TaxRate" is spelled incorrectly. To fix the error, correct the spelling in the formula.
Meaning: The formula refers to a range that doesn't exist. Often caused by the intersection operator (the space character)
Troubleshooting Info: #NULL! Help
Example: The formula in D4 returns the #NULL! error because there is a space character (intersection operator) between the cell references, instead of a comma, and those 2 cells do not intersect. To fix the error, type a comma after the B4 reference.
Instead of using Excel's built-in hash errors, you can create your own hash errors. Thanks to UniMord for this suggestion.
Warning: Use this technique with discretion, because your co-workers might not like it! Also, be sure to add notes to the workbook, or cell comments, explaining what your custom hash errors mean.
The examples below show how to create custom hash errors with the IF function, and with a custom number format, and you can download the sample file, to see both examples.
In the screen shot below, the budget limit is 6000. In the total cell (B8), there is an IF function that checks the sum. If the amount is over the budget limit, a custom hash error appears – #OVER!
=IF(SUM(B2:B7)>D2, "#OVER!", SUM(B2:B7))
Because the error looks like the built-in hash errors from Excel, people might pay attention to it.
You can also show a custom hash error with conditional formatting. In this example, there is a conditional formatting rule on the Total cell (B8), to check if the total is greater than the budget limit (D2)
If that condition is met, the cell shows a custom number format:
Download the zipped Excel Formula Errors file. The zipped file is in xlsx format, and does not contain any macros.
These resources can help you troubleshoot Excel errors, and help prevent errors from appearing in your workbook.
Charles Williams is an Excel calculation expert, and his FastExcel add-in can help you find calculation bottlenecks in your workbook, and understand and debug complex formulas more easily.
Learn about its troubleshooting tools and features, to see how it can help you.
Don't waste time looking for errors after your Excel file is completed. Check for errors from the beginning.
In his online course, Ken Puls shows how to build an error checking system in your workbooks. See how to create an error checking section on each worksheet, and a global check point for the entire workbook. The system even checks the pivot tables, to see if they've been updated -- I've been hit by that problem!
This small investment could save you hours of headaches! There are two versions of the course:
Use the Excel add-in, RefTreeAnalyser, to audit your formulas. There is a free trial version available.
This add-in helps with easy auditing of formula dependents and precedents, finding circular references, checking for formula inconsistancies, and many other auditing tasks.
Last updated: February 14, 2019 11:13 AM