Contextures

Excel Formula Error Values

What 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 #N/A error

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.

Excel #NUM! 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.

Excel #VALUE! error

#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.

Excel #N/A error

#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.

Excel #DIV/0! error

#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.

Excel #REF! error

#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.

Excel #NAME? error value

#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.

Excel #NULL! error

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.

IF function with custom error value

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!"

customexcelerrors02

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.

fast excel

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:

  1. online videos, with downloadable Excel workbooks
  2. 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.

fast excel

Related Links

Audit Excel Formulas

SUM Function

VLOOKUP Function

INDEX function and MATCH Function

Count Functions

INDIRECT Function

MATCH Video

VLOOKUP Video

Hide VLOOKUP Errors

 

Get weekly Excel tips from Debra

 

 

30 Excel Functions in 30 Days

 

excel tools

 

fast excel

 

 

 

pivot power premium

Last updated: February 14, 2019 11:13 AM