How to Use the TYPE and ERROR.TYPE Functions in Microsoft Excel to Audit Formula Values

Auditing complex financial models, data pipelines, and multi-sheet workbooks in Microsoft Excel often demands automated checks to ensure cells contain the expected data formats. When external data imports introduce mismatched text strings, unexpected arrays, or formula errors, diagnosing the root cause manually can be challenging. Excel provides two specialised information functions—TYPE and ERROR.TYPE—designed specifically to inspect and categorize cell contents programmatically.

The TYPE function evaluates a cell or formula expression and returns an integer code corresponding to its underlying data type (such as a number, text string, boolean logical, error value, or array). Meanwhile, the ERROR.TYPE function inspects formula calculation faults and returns an exact numeric code distinguishing between common errors like #DIV/0!, #N/A, #VALUE!, and #REF!. In this tutorial, you will learn the syntax, return codes, practical use cases, and conditional formula integration for both functions.

Understanding the TYPE Function Syntax and Return Codes

The TYPE function accepts a single argument representing any value, cell reference, or evaluated formula:

=TYPE(value)

Excel categorises the result into one of five standard numeric identifiers:

Return Code	Underlying Data Type	Example Input Value
1	Number (including dates and currencies)	42, 12.50, 2026-10-14
2	Text string	"Inventory", "Total: "
4	Logical value	TRUE, FALSE
16	Error value	#N/A, #VALUE!, #DIV/0!
64	Array	{1,2,3}, FILTER(A2:A10, B2:B10>0)

Note: The TYPE function cannot determine whether a cell contains a formula; it only evaluates the final resulting value produced by that formula.

Practical Examples of the TYPE Function

The TYPE function is particularly useful when building data-validation routines and conditional formula branches:

1. Validating Numeric Input Before Calculation

If you need to ensure cell A2 contains an authentic numeric value before applying a multiplication factor:

=IF(TYPE(A2)=1, A2*1.2, "Invalid Input: Expected Number")

If cell A2 contains a number or date, the formula executes the calculation. If text was entered accidentally, it outputs a descriptive warning message.

2. Flagging Inadvertent Text Formatted Numbers

When data is exported from external accounting software, numbers are often stored as text strings (which display like numbers but break mathematical sums). You can audit column A by applying this formula in column B:

=IF(TYPE(A2)=2, "Stored as Text", "Valid Format")

Understanding the ERROR.TYPE Function Syntax and Codes

While functions like ISERROR or IFERROR catch errors generally, they do not explain which specific error occurred. The ERROR.TYPE function inspects an error value and maps it to a specific integer:

=ERROR.TYPE(error_val)

Excel returns the following numeric codes corresponding to standard calculation errors:

Return Code	Error Description	Common Root Cause
1	#NULL!	Incorrect range intersection operator (missing space or comma)
2	#DIV/0!	Division by zero or an empty divisor cell
3	#VALUE!	Mathematical operations attempted on incompatible text strings
4	#REF!	Invalid cell reference caused by deleted rows or columns
5	#NAME?	Misspelled function name or unrecognised named range
6	#NUM!	Invalid numerical argument in a calculation (e.g., negative square root)
7	#N/A	Lookup value not found in VLOOKUP, XLOOKUP, or MATCH
8	#GETTING_DATA	Temporarily displayed while external or asynchronous data loads

Important: If the referenced cell does not contain an error, ERROR.TYPE returns #N/A. To avoid unhandled formula errors when wrapping ERROR.TYPE, combine it with ISERROR.

Practical Application: Custom Error Diagnostics with SWITCH

By pairing ERROR.TYPE with the modern SWITCH function, you can replace cryptic error codes with actionable troubleshooting messages for end-users:

=IF(ISERROR(A2), SWITCH(ERROR.TYPE(A2),
    2, "Notice: Divisor is zero",
    4, "Alert: Referenced cell was deleted",
    7, "Warning: Record not found in database",
    "General calculation issue"
), "Calculation Normal")

In this example, if cell A2 encounters a division error (#DIV/0!), Excel outputs “Notice: Divisor is zero”. If a lookup fails to locate the record (#N/A), it outputs “Warning: Record not found in database”. If the calculation completed successfully without an error, it returns “Calculation Normal”.

Best Practices When Auditing Formula Integrity

• Use TYPE for Pre-Flight Validation: Check input parameters with TYPE before executing heavy iterative formulas or complex Power Query scripts.
• Combine ERROR.TYPE with Logging Tables: Maintain an audit summary sheet that counts error frequencies (for example, counting how many instances of code 7 occurred across sales lookup columns).
• Distinguish Missing Data from Broken Formulas: Differentiate between harmless lookup misses (#N/A) and critical structural corruption (#REF!) to prioritize spreadsheet maintenance.