Summary

The Excel ERROR.TYPE function returns a number that corresponds to a specific error value. You can use ERROR.TYPE to test for specific kinds of errors. If no error exists, ERROR.TYPE returns #N/A. See below for a key to the error codes returned by ERROR.TYPE. 

Purpose 

Test for a specific error value

Return value 

An error number or #N/A if no error.

Syntax

=ERROR.TYPE(error_val)
  • error_val - The error for which to get an error code.

How to use 

The Excel ERROR.TYPE function returns a number that corresponds to a specific error value. You can use ERROR.TYPE to test for specific kinds of errors. If no error exists, ERROR.TYPE returns #N/A. See the table below for a key to the error codes returned by ERROR.TYPE. 

The ERROR.TYPE function takes just one argument, error_val, which is expected to be an Excel error like #VALUE!, #DIV/0!, #NAME!, etc. When error_val is an error, ERROR.TYPE returns a numeric code. If error_val is not an error, ERROR.TYPE returns an error itself: the #N/A error. In most cases, error_val will be supplied as a reference to a cell that may contain an error value.

Examples

If cell A1 contains displays the #DIV/0 error, then ERROR.TYPE will return 2:

=ERROR.TYPE(A1) // returns 2

If cell A1 displays the #N/A error, ERROR.TYPE returns 7

=ERROR.TYPE(A1) // returns 7

If cell A1 displays no error, ERROR.TYPE returns #N/A

=ERROR.TYPE(A1) // returns #N/A

One way to use ERROR.TYPE is to test for specific errors and display a custom message when certain error conditions exist. For example, to test for a #DIV/0! error in cell A1 and display a custom message when present, you can use a formula like this:

=IF(ISERROR(A1),IF(ERROR.TYPE(A1)=2,"Missing value",A1),"")

This formula returns an empty string ("") when no error is present, and the message "Missing value" when A1 contains #DIV/0!. Other errors are displayed normally.

Errors and codes

Error Code
#NULL! 1
#DIV/0! 2
#VALUE! 3
#REF! 4
#NAME? 5
#NUM! 6
#N/A 7
#GETTING_DATA 8
#SPILL! 9
#BLOCKED! 11
#CALC! 14

Other error functions

Excel provides a number of error-related functions, each with a different behavior:

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.