The ERROR.TYPE function in Excel returns a number corresponding to a specific type of error in your spreadsheet. Instead of manually inspecting errors like #DIV/0! or #N/A, you can use ERROR.TYPE to categorize them quickly. This makes identifying the root cause much faster and more efficient.
For example, if a formula returns #VALUE!, using ERROR.TYPE on that cell will produce the number 3. This numerical output corresponds to a predefined error type, helping you automate error management and troubleshooting.
Syntax of ERROR.TYPE
The syntax for the ERROR.TYPE function is straightforward:
=ERROR.TYPE(value)
value: The argument can be a cell reference, formula, or any value you suspect contains an error.
If the provided value does not contain an error, the function will return #N/A.
---
Error Types and Their Corresponding Numbers
Here’s a quick reference table for the numbers returned by ERROR.TYPE:
#NULL!: Null intersection, ERROR.TYPE returns 1.
#DIV/0!: Division by zero, ERROR.TYPE returns 2.
#VALUE!: Invalid value, ERROR.TYPE returns 3.
#REF!: Invalid cell reference, ERROR.TYPE returns 4.
#NAME?: Undefined name or function, ERROR.TYPE returns 5.
#NUM!: Invalid numeric value, ERROR.TYPE returns 6.
#N/A: Value not available, ERROR.TYPE returns 7.
Other: No error or unknown value, ERROR.TYPE returns #N/A.
This mapping makes it easy to identify specific issues in your formulas and data.
---
How to Use ERROR.TYPE in Excel
Here’s a simple step-by-step guide to using the ERROR.TYPE function:
1. Identify a Cell with an Error
Start by locating a cell that contains an error, such as #DIV/0! or #REF!.
2. Enter the ERROR.TYPE Formula
In an adjacent cell, input the following formula:
=ERROR.TYPE(A1)
Replace A1 with the reference to the error cell.
3. Interpret the Output
The function will return a number corresponding to the error type. For instance:
If A1 contains #VALUE!, the formula will output 3.
If A1 contains #NAME?, the formula will output 5.
---
Practical Examples of ERROR.TYPE
Example 1: Troubleshooting a Formula
Imagine you have a division formula:
=100/B1
If B1 is empty, Excel will return #DIV/0!. By applying ERROR.TYPE to this formula, you can quickly identify the error type and create logic to handle it, such as displaying a custom message or correcting the input.
Example 2: Highlighting Errors in a Dataset
Suppose you’re working with a large dataset, and some cells contain errors. Use ERROR.TYPE with conditional formatting to color-code cells based on the type of error. For example:
Highlight #REF! errors in red.
Highlight #N/A errors in yellow.
Example 3: Creating Custom Error Messages
Combine ERROR.TYPE with the IF function to display user-friendly error messages:
=IF(ERROR.TYPE(A1)=2, "Division by zero error", "Check your formula")
This formula checks if a division error exists and suggests a solution.
---
Why Use ERROR.TYPE?
Efficiency: Quickly identify specific error types without manual inspection.
Automation: Integrate error handling into larger workflows, such as data validation or cleanup.
Troubleshooting: Pinpoint issues faster, saving time and reducing frustration.
---
Limitations of ERROR.TYPE
While incredibly useful, the ERROR.TYPE function has a few limitations:
It only works with error values. If there’s no error, it returns #N/A.
It cannot fix errors; it simply identifies them. You’ll need additional logic or corrections to resolve issues.