The ERROR.TYPE function tells you what kind of error is in a cell. It returns a number for each error type. This helps you identify specific problems in your formulas.
What ERROR.TYPE Does
- Checks if a cell contains an error
- Returns a number (1-8) for each error type
- Returns #N/A if there is no error
Syntax
=ERROR.TYPE(error_val)
error_val is the cell you want to check (like A1).
Error Codes
| Error | Code | Description |
|---|---|---|
| #NULL! | 1 | Wrong intersection of ranges |
| #DIV/0! | 2 | Divide by zero |
| #VALUE! | 3 | Wrong data type |
| #REF! | 4 | Wrong cell reference |
| #NAME? | 5 | Wrong function name |
| #NUM! | 6 | Wrong number |
| #N/A | 7 | Value not available |
| #GETTING_DATA | 8 | External data still loading |
Basic Examples
Example 1: Simple Error Check
If cell A1 shows #DIV/0!:
=ERROR.TYPE(A1)
Result: 2
Example 2: Check #N/A Error
If cell A2 shows #N/A:
=ERROR.TYPE(A2)
Result: 7
Example 3: Normal Cell (No Error)
If cell A3 has a number:
=ERROR.TYPE(A3)
Result: #N/A
Using ERROR.TYPE with IF
Tip: Combine ERROR.TYPE with IF to show custom messages.
Custom Message for Division by Zero
=IF(ERROR.TYPE(A1)=2,"Cannot divide by zero",A1)
- If A1 has #DIV/0!, shows: “Cannot divide by zero”
- If A1 has no error, shows the value in A1
Message for Any Error
=IF(ISERROR(A1),"Error type: "&ERROR.TYPE(A1),A1)
- If A1 has any error, shows “Error type: [number]”
- If A1 has no error, shows the value in A1
Check Multiple Error Types
=IF(OR(ERROR.TYPE(A1)=2,ERROR.TYPE(A1)=3),"Math error",A1)
Shows “Math error” for #DIV/0! or #VALUE! errors.
When to Use ERROR.TYPE
- Find which error happened (not just that an error exists)
- Different messages for different error types
- Debug formulas – see exactly what went wrong
- Advanced error handling in complex spreadsheets
Quick Comparison
| Function | What it does |
|---|---|
| ISERROR | TRUE/FALSE – any error? |
| ERROR.TYPE | Number 1-8 – which error? |
| IFERROR | Replace error with value |
Best Practice: Use ERROR.TYPE when you need to know which specific error occurred.
Summary
ERROR.TYPE is simple but powerful. It identifies exactly which error happened in your cell. Use it with IF statements to create helpful error messages. This makes your spreadsheets more professional and user-friendly.
