- Excel Functions
- Information
Summary
The Excel ERROR.TYPE function is designed to identify the type of error in a cell, returning a numeric code corresponding to a specific error type. This function is particularly useful for error handling in formulas, allowing users to categorize errors and respond to them conditionally within their Excel models or reports.
Syntax
=ERROR.TYPE(error_val) - error_val: The error value or expression that potentially results in an error
Return value
A number from 1 to 8, representing different types of errors. If error_val does not correspond to an error, ERROR.TYPE returns #N/A.
How to use
To use ERROR.TYPE, input a cell reference or an expression that might result in an error. The function evaluates the input and returns a numeric code that represents the type of error, if any. These numeric codes correspond to the following error types:
- 1: #NULL! – Intersect operator used incorrectly.
- 2: #DIV/0! – Division by zero.
- 3: #VALUE! – Wrong type of argument in a function.
- 4: #REF! – Invalid cell reference.
- 5: #NAME? – Excel doesn’t recognize text in a formula.
- 6: #NUM! – Invalid numeric values in a formula or function.
- 7: #N/A – Value not available to a function or formula.
- 8: #GETTING_DATA – A placeholder for getting data from a remote source (less common).
This function can be combined with IF or other logical functions to handle errors gracefully in your calculations, allowing for custom error messages or alternative calculations when an error is detected.
Examples
Simple ERROR.TYPE
Identifying a Division by Zero Error: To check for and identify a division by zero error in a calculation.
=ERROR.TYPE(A1/B1) If dividing A1 by B1 results in a division by zero error, ERROR.TYPE returns 2, as this is the code for #DIV/0! errors.
ERROR.TYPE for Invalid Cell Reference
Handling an Invalid Cell Reference Error: To detect an invalid cell reference error in your worksheet.
=ERROR.TYPE(A1) Assuming A1 contains an invalid reference error (#REF!), ERROR.TYPE will return 4, the numeric code associated with #REF! errors.
ERROR.TYPE with IF Function
Creating Custom Error Messages: To display a custom message instead of an Excel error.
=IF(ISERROR(A1), "Error Detected: " & ERROR.TYPE(A1), A1) This formula checks if A1 contains an error. If so, it displays “Error Detected: ” followed by the error type code. Otherwise, it simply shows the value of A1.
Additional Notes
- ERROR.TYPE is a diagnostic tool rather than a corrective function. It identifies errors but does not fix them. Combining ERROR.TYPE with conditional logic can help manage how errors are presented in your data analysis or reporting workflows.
Related Functions
Content Navigation
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of all the cookies.
