|
Home > Formulas > VLOOKUP > Errors Excel Formula Error ValuesWhat the Excel formula errors mean, and how to fix them. Make your own hash errors, to use with formulas.
|
Introduction
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.
Excel Error Values
Here is a list of the built-in Excel formula error values. Microsoft calls them hash errors, because of the character at the beginning.
- #NUM!
- #VALUE!
- #N/A
- #DIV/0!
- #REF!
- #NAME?
- #NULL!
There is a brief note on each hash error below, and a link to the related Microsoft help page,
#NUM! Error Value
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.
#VALUE! Error Value
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.
#N/A Error Value
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.
#DIV/0! Error Value
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.
#REF! Error Value
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.
#NAME? Error Value
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.
#NULL! Error Value
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.
Create Custom Hash Errors
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.
IF Function
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.
Use a Custom Number Format
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)
=B8>D2
If that condition is met, the cell shows a custom number format:
"#OVER!"
Download the Sample File
Download the zipped Excel Formula Errors file. The zipped file is in xlsx format, and does not contain any macros.
Excel Error Resources
These resources can help you troubleshoot Excel errors, and help prevent errors from appearing in your workbook.
FastExcel
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.
Error Checking System
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:
- online videos, with downloadable Excel workbooks
- online and downloadable videos, with downloadable Excel workbooks
RefTreeAnalyser
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.
Related Links
Last updated: March 9, 2023 6:55 PM