ERROR.TYPE Function

Returns a number that identifies which error a value is.

Category

Information

Available in

All versions, Microsoft 365

Returns

A number for the error type, or #N/A

Want to practice ERROR.TYPE in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

ERROR.TYPE turns an error into a code: 1 #NULL!, 2 #DIV/0!, 3 #VALUE!, 4 #REF!, 5 #NAME?, 6 #NUM!, 7 #N/A — and in Microsoft 365, 8 #GETTING_DATA, 9 #SPILL!, 13 #FIELD! and 14 #CALC!.

If the value isn't an error at all, ERROR.TYPE itself returns #N/A. Use it to show a friendly explanation for each kind of error.

Syntax

=ERROR.TYPE(error_val) Explain

Arguments

ArgumentRequired?What it does
error_valYesThe value, cell or formula to identify.

Examples

Example 1 — Identify each error

Column A holds formulas that went wrong — and one that didn't.

B2fx=ERROR.TYPE(A2)
AB
1ResultCode
2#DIV/0!2
3#VALUE!3
4#NUM!6
5#N/A7
6125#N/A
=ERROR.TYPE(A2) Explain

Example 2 — Friendly error messages

Explain what went wrong in plain words. Check ISERROR first, because ERROR.TYPE returns #N/A for normal values.

B2fx=IF(ISERROR(A2),SWITCH(ERROR.TYPE(A2),2,"Divide by zero",3,"Wrong kind of data",7,"Not found","Other error"),"OK")
AB
1ResultMessage
2#DIV/0!Divide by zero
3#N/ANot found
4125OK
=IF(ISERROR(A2),SWITCH(ERROR.TYPE(A2),2,"Divide by zero",3,"Wrong kind of data",7,"Not found","Other error"),"OK") Explain

Common mistakes

  • #N/A when there's no error. That's how ERROR.TYPE says “not an error”. Wrap it in ISERROR or IFERROR.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.