All versions
Information
The Excel ERROR.TYPE function returns a number identifying which error a cell holds — 1 for #NULL!, 2 #DIV/0!, 3 #VALUE!, 4 #REF!, 5 #NAME?, 6 #NUM!, 7 #N/A — so you can give specific, helpful error messages.
Quick answer: which error is it?
=ERROR.TYPE(A2) // 2 = #DIV/0!, 7 = #N/A, etc.
Syntax
=ERROR.TYPE(error_val)
| Argument | Description | |
|---|---|---|
error_val | Required | An error value or a cell containing one. |
How to use it
Pair it with CHOOSE or IFS to turn cryptic errors into plain-English guidance:
=IF(ISERROR(A2), CHOOSE(ERROR.TYPE(A2),"#NULL!","Divide by zero","Wrong type","Bad reference","Unknown name","Bad number","Not found"), A2) // friendly error text
If the cell has no error, ERROR.TYPE itself returns #N/A — so always guard it with ISERROR.
Try it: interactive demo
Live demo
Pick an input and watch the formula and result update.
Result:
Practice workbook
Download the free ERROR.TYPE practice workbook
Every example on this page, ready to open in Excel — plus practice challenges with answers on a separate tab. No sign-up required.
Frequently asked questions
What are the error codes?
1 #NULL!, 2 #DIV/0!, 3 #VALUE!, 4 #REF!, 5 #NAME?, 6 #NUM!, 7 #N/A, 8 #SPILL! (365), 14 #FIELD!/others.
What if the cell has no error?
ERROR.TYPE returns #N/A itself. Always wrap it: =IF(ISERROR(A2), ERROR.TYPE(A2), "OK").
Why use it over IFERROR?
IFERROR gives one catch-all message; ERROR.TYPE lets you tailor the message to the specific error.
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class