Home > Formulas > VLOOKUP > Errors

Excel Formula Error Values

What the Excel formula errors mean, and how to fix them. Make your own hash errors, to use with formulas. Create short troubleshooting messages with ERROR.TYPE function

Clear messages help troubleshoot formula errors

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

Troubleshoot with ERROR.TYPE Function

The Excel ERROR.TYPE function can identify specific types of errors, and you can use that information to help with troubleshooting worksheet formulas.

In the video below, I show examples of using the ERROR.TYPE function for formula troubleshooting

ERROR.TYPE Syntax

The ERROR.TYPE function identifies an error type by number, or returns #N/A if no error is found.

ERROR.TYPE function syntax

The ERROR.TYPE function has the following syntax:

  • ERROR.TYPE(error_val)

There is one required argument:

  • error_val -- the error that you want to identify

ERROR.TYPE Codes

The ERROR.TYPE function returns a code number, if an error is found, using one of the following codes:

  • 1…..#NULL!
  • 2…..#DIV/0!
  • 3…..#VALUE!
  • 4…..#REF!
  • 5…..#NAME?
  • 6…..#NUM!
  • 7…..#N/A

If no error is fount, the formula returns #N/A

Example 1 - ERROR.TYPE

With the ERROR.TYPE function you can check a cell, to identify which error it contains.

In the screen shot below, I've entered the following formula in cell C3, to check for an error in cell B3: =ERROR.TYPE(B3)

ERROR.TYPE function checks cell B3

In this example, cell B3 contains a #VALUE! error, so the error type is 3.

ERROR.TYPE function code numbers

Example 2 - Troubleshooting Help

By combining the ERROR.TYPE function with other functions, you can help people troubleshoot error results in a cell.

In the example shown below, numbers should be entered in cells B3 and C3.

  • B3: enter the student score (number)
  • C3: enter the total score available (number)
  • D3: formula to calculate score percent: =IF(C3="","",B3/C3)

IF formula to show score percentage

Formula Result - Percent or Error

The result in cell D3 should be a number, but could return an error in some cases:

  • If text is entered in either cell, the result in D3 is a #VALUE! error.
  • If a zero is entered in cell C3, the result is a #DIV/0! error.

In cell D4, another formula shows a troubleshooting message, when needed.

Clear messages help troubleshoot formula errors

ERROR.TYPE Troubleshooting

In cell D4, I've entered the following formula, which uses ERROR.TYPE to get an error code number, if cell D3 contains an error:

  • =IF(ISERROR(D3), LOOKUP(ERROR.TYPE(D3), $B$9:$B$15, $D$9:$D$15),””)

How the Formula Works

Here's how the formula works:

  • ISERROR checks for an error
  • ERROR.TYPE function returns a number for the error.
  • LOOKUP function returns the applicable troubleshooting message from a table of error type codes

Here is the error code lookup table, with code numbers, error values, and messages.

error code lookup table for ERROR.TYPE troubleshooting help

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 Sample Files

Errors: Download the zipped Excel Formula Errors file. The zipped file is in xlsx format, and does not contain any macros.

ERROR.TYPE: To follow along with the ERROR.TYPE video, you can download the ERROR.TYPE function 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

 

 

Last updated: February 21, 2024 1:54 PM