Troubleshoot Excel Formulas with ERROR.TYPE Function

Did you know that you can use the Excel ERROR.TYPE function to identify specific types of errors on a worksheet? And after the error type is identified, you can use that information to provide help with error troubleshooting.

There’s a short video below, that shows an example, and there’s a list of the Excel error values that this function can identify.

Excel ERROR.TYPE Function

It’s easy to use the ERROR.TYPE function in Excel. Just give it a cell reference, and it will check that cell for an error value.

For example, in the screen shot below, the following formula is in cell C3. It checks for an error in cell B3:

=ERROR.TYPE(B3)

Note: This example is from the sample file that you can download from my Contextures site, on the Excel Formula Error Values page.

Video: Excel ERROR.TYPE Function Examples

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

First, the simple formula (shown above),
Next, a fancier way to help with error problems.

There’s more information on the second example below the video.

Two Data Entry Cells

In the second example, there are two data entry cells on an Excel worksheet.

If you enter a number in cell B3 and cell D3, the formula in cell D3 calculates a percentage.

Wrong Data – Error Result

However, things can go wrong if you do either of these things:

put text in either of those cells, OR
type a zero in cell C3

In those cases, the result cell will show an error value, and cell D4 shows a short troubleshooting message.

Message for Error Values

For example, in the screen shot below,

I typed a zero in C3, which created a #DIV/0! error.
The message in cell D4 says, “Total can’t be zero”

For people who aren’t familiar with Excel error values, the short message could help them fix the problem quickly.

Messages – Error Code Lookup Table

The messages in cell D4 come from a 3-column lookup table that I set up.

First column has the error type codes that ERROR.TYPE returns.
Second column has the error values
Third column has a short message to explain each error type

Formula to Show Message

In cell D4, I 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 Formula Works

Here’s how the formula works:

1) First, the ISERROR function checks cell D3 for an error.
2) Next, if cell D3 contains an error, the Excel LOOKUP Function:

finds the error code in the first column
returns the message text from the third column

3) Finally, if there’s no error in cell D3, the IF function returns an empty string

See Formula in Action

The animated screen shot below shows how the formula works.

If I type text, or a zero, in cell C3, a short message appears in cell D4.

Get the Sample File

To download the ERROR.TYPE sample file, go to the Excel Formula Error Values page on my Contextures site.

You’ll also find more information on Excel error values on that page.

For example, did you know that Microsoft calls them hash errors?

_________________________