Excel ERROR.TYPE Function: Codes and Examples

Part of the free Module 5: Excel Formulas and Functions · Function 27 of 105 · Full Excel course

The Excel ERROR.TYPE function returns a number that identifies which error a cell or formula contains: 1 for #NULL!, 2 for #DIV/0!, 3 for #VALUE!, 4 for #REF!, 5 for #NAME?, 6 for #NUM!, 7 for #N/A and so on. ERROR.TYPE in Excel lets you handle different errors differently, instead of treating them all the same with IFERROR.

Table of Contents

ERROR.TYPE syntax

=ERROR.TYPE(error_val)

Arguments

Argument	Required	Meaning
error_val	Required	The error value to identify: a reference to a cell showing an error, or a formula that produces one.

Return codes

Error	ERROR.TYPE returns
#NULL!	1
#DIV/0!	2
#VALUE!	3
#REF!	4
#NAME?	5
#NUM!	6
#N/A	7
#GETTING_DATA	8
#SPILL! (Excel 365)	9
#CONNECT!	10
#BLOCKED!	11
#UNKNOWN!	12
#FIELD!	13
#CALC!	14
Anything that is not an error	#N/A

Step-by-step example

Cell A1 contains =10/0, which displays #DIV/0!.

=ERROR.TYPE(A1)

Excel evaluates A1 and finds the #DIV/0! error.
It looks up that error in its internal list.
It returns 2. If A1 held a normal number, the formula would return #N/A, because there is no error to classify.

Practical use cases

1. Custom message per error type

=IF(ISERROR(B2), CHOOSE(ERROR.TYPE(B2), "Range error", "Divide by zero", "Wrong data type", "Bad reference", "Unknown name", "Number problem", "Not found"), B2)

CHOOSE maps codes 1 to 7 to plain-language explanations.

2. Treat only #DIV/0! as zero

=IF(IFERROR(ERROR.TYPE(C2/D2), 0)=2, 0, C2/D2)

Other errors remain visible so they get fixed.

3. Count each error type in a range

=SUMPRODUCT(--(IFERROR(ERROR.TYPE(A2:A500), 0)=7))

Counts the #N/A cells; change 7 to another code for a different error.

4. Audit report column

=IFERROR(ERROR.TYPE(E2), "")

Fill down beside a calculation column to see at a glance which rows fail and why.

Common mistakes and errors

#N/A from ERROR.TYPE itself – the tested cell is not an error. Wrap in IFERROR(ERROR.TYPE(x), 0) or test with ISERROR first.
Confusing 7 and 8 – #N/A is 7; 8 is #GETTING_DATA, seen while external data loads. Older references sometimes swap them.
Using ERROR.TYPE where IFNA or IFERROR suffices – if you only need to trap one error, IFNA is simpler; ERROR.TYPE is for branching on several.
Testing text that looks like an error – “#N/A” typed as text is not an error and returns #N/A from ERROR.TYPE.
Codes above 8 in older Excel – #SPILL! and later errors exist only in Excel 365; older versions never return 9 to 14.

Tips and best practices

Wrap in IFERROR(ERROR.TYPE(x), 0) so non-error cells return 0 and the result can be compared or counted safely.
Map codes to messages with CHOOSE for user-facing sheets, and keep the raw code in a hidden audit column.
Count each error type across a sheet to prioritise clean-up: many 7s mean missing lookup keys, many 2s mean division guards are needed.
Use IFNA for lookups and ERROR.TYPE only when branching on several error kinds is genuinely needed.
Remember the numbering: #N/A is 7, not 8; 8 is #GETTING_DATA.

Related functions

IFERROR – replaces any error with one value.
IFNA – replaces only #N/A.
ISERROR, ISERR and ISNA – TRUE/FALSE error tests.
CHOOSE – turns the code into a message.
Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.

Frequently asked questions

What number does ERROR.TYPE return for #N/A?

7. The full list is #NULL! 1, #DIV/0! 2, #VALUE! 3, #REF! 4, #NAME? 5, #NUM! 6, #N/A 7 and #GETTING_DATA 8, with 9 to 14 reserved for newer Excel 365 errors.