How to Use the TYPE and ERROR.TYPE Functions in Microsoft Excel to Handle Formula Errors

Building automated financial models, inventory calculators, and data transformation pipelines in Microsoft Excel often encounters unexpected formula errors. While standard error-trapping functions like IFERROR or ISERROR provide broad safeguards, they treat all errors identically, masking the difference between a simple missing lookup key (#N/A), a critical mathematical division by zero (#DIV/0!), and an invalid reference corruption (#REF!).

Microsoft Excel includes two specialized diagnostic functions—TYPE and ERROR.TYPE. Together, these functions identify data types and isolate the exact numerical error code generated by faulty calculations, enabling spreadsheet engineers to build intelligent, context-aware error handling formulas that respond differently depending on the specific error condition.

Understanding the TYPE Function

The TYPE function returns a standardized integer code indicating the data type of any cell reference or expression result:

=TYPE(value)

• 1: Number (including standard integers, decimals, dates, and times).
• 2: Text (string values).
• 4: Logical value (TRUE or FALSE).
• 16: Error value (any Excel error such as #VALUE!, #REF!, etc.).
• 64: Array (dynamic array spill ranges).
• 128: Compound data types (Rich Data types like Stocks or Geography).

You can use TYPE inside logical expressions to validate input columns before executing arithmetic:

=IF(TYPE(A2) = 1, A2 * 1.2, "Invalid Number Entry")

Understanding the ERROR.TYPE Function

When an expression evaluates to an error, the ERROR.TYPE function inspects the specific failure mode and returns an integer code corresponding to the exact error classification:

=ERROR.TYPE(error_val)

Standard Excel Error Code Mapping

Returned Integer Code	Excel Error Type	Common Underlying Cause
1	#NULL!	Invalid intersection of two ranges that do not intersect.
2	#DIV/0!	Formula attempts to divide a numeric value by zero or an empty cell.
3	#VALUE!	Wrong argument type (e.g., text passed into mathematical function).
4	#REF!	Referenced cells, columns, or rows were deleted or overwritten.
5	#NAME?	Misspelled formula name or unrecognized text string.
6	#NUM!	Invalid numerical argument (e.g., square root of a negative number).
7	#N/A	Value not available (common in failed XLOOKUP or VLOOKUP queries).
8	#GETTING_DATA	Asynchronous query actively fetching remote web or database feeds.
9	#SPILL!	Dynamic array blocked by existing data in target spill range.
10	#CALC!	Calculation engine failure or unhandled empty array.

Building Granular Error-Handling Formulas

By nesting ERROR.TYPE within a SWITCH or CHOOSE function, you can provide tailored guidance to end users depending on the nature of the error:

=IF(ISERROR(A2), 
    SWITCH(ERROR.TYPE(A2),
      2, "Calculation Warning: Division by zero",
      4, "System Alert: Referenced cells were deleted (#REF!)",
      7, "Lookup Notice: Record not found in database",
      "General Formula Error"
    ), 
    A2
)

This granular approach ensures data entry teams immediately understand whether a problem requires adding a new customer record to a database table or repairing a broken worksheet column reference.