Use the ERROR.TYPE to Identify Excel Errors

by | Aug 29, 2019 | Excel Formulas

excel error

The ERROR.TYPE function is an Information formula that returns an index number corresponding to a specific error value in Microsoft Excel. If there are no errors in the target cell(s), the function returns another error value, #N/A. This function can be especially useful in troubleshooting and error handling in your Excel models, as it can provide a quick overview of the existing issues. In this guide, we’re going to show you how to use the ERROR.TYPE function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

ERROR.TYPE Function Syntax

ERROR.TYPE(error_val)

Arguments

error_val The error value you want to test. A common scenario is using a formula or a reference to a cell containing a formula that you want to test.

Examples for Identifying Excel Errors

Use the this function to get a number value that indicates a specific error value. The return values and types are given in the table below:

1 - #NULL!
2 - #DIV/0!
3 - #VALUE!
4 - #REF!
5 - #NAME?
6 - #NUM!
7 - #N/A
#N/A - Anything else

Please see Common Formula Errors in Excel for more information about some common errors in Excel and how you can handle them.

To use this formula, all you need to to is to set a formula containing cell reference to the error_val argument to test its result. The result will give you the index number for the error type in the target cell.

=ERROR.TYPE(B5)

Excel ERROR.TYPE Function

Download Workbook

Tips

  • You can combine the ERROR.TYPE function with IF or IFNA functions for even better error handling in your Excel models.
  • Other error related functions of Excel include:
    • ISERROR function tests the error values and returns a Boolean (TRUE / FALSE) value based on the outcome
    • ISERR function tests error values and returns a Boolean (TRUE / FALSE) value based on the outcome
  • Please see Reducing Formula Errors in Excel to learn more about error-handling methods and workarounds.

Issues

  • If the error_val argument doesn't contain any errors, the function returns the #N/A error value.