How to Identify Errors with ERROR.TYPE in Excel

ERROR.TYPE turns each Excel error into a number, so you can explain exactly what went wrong in plain English.

You will learn to

• Read the ERROR.TYPE code of an error
• Handle cells that aren’t errors at all
• Translate the code into a plain-English explanation

How it works

ERROR.TYPE(error_val) returns a number identifying an error: 1 #NULL!, 2 #DIV/0!, 3 #VALUE!, 4 #REF!, 5 #NAME?, 6 #NUM! and 7 #N/A. If the value is not an error, ERROR.TYPE itself returns #N/A — so wrap it in IFNA to label healthy cells.

Because the codes run 1 to 7, CHOOSE turns them into explanations in one step: =CHOOSE(ERROR.TYPE(B4),"Null intersection","Divided by zero","Wrong type of value","Broken reference","Unknown name","Invalid number","Value not found"). That beats a vague "Error" message in any report.

=IFNA(ERROR.TYPE(B3),"No error")