The ERROR.TYPE function in Excel is designed to return a numeric code that corresponds to a specific error type found within a formula or expression. This function is particularly useful for identifying and troubleshooting various error messages in spreadsheets, allowing users to handle errors gracefully and improve data accuracy.

Syntax

ERROR.TYPE(error_val)

• error_val: This is the value or expression for which you want to determine the error type. It can be a reference to a cell, a formula that returns an error, or a direct error value.

Example #1

ERROR.TYPE(DIV/0!)

When an attempt to divide by zero occurs, this function returns ‘2’, indicating a division error. Example output: 2.

Example #2

ERROR.TYPE(VALUE!)

This example checks for a value error where an operand or argument is of the wrong type, resulting in a return of ‘3’. Example output: 3.

Example #3

ERROR.TYPE(A1)

If cell A1 contains an error like N/A, this function will return ‘7’, signifying a ‘not available’ error. Example output: 7.

Error handling

The ERROR.TYPE function can provide insights into different error messages, helping users manage their data more effectively. Here are common error messages and their corresponding codes:

• DIV/0!: Returns ‘2’, indicating a division by zero error.
• VALUE!: Returns ‘3’, signifying that a value error has occurred due to an incorrect operand.
• N/A: Returns ‘7’, indicating that a value is not available.
• REF!: Returns ‘4’, showing that a reference is not valid.

Conclusion

In summary, the ERROR.TYPE function serves as an essential tool for identifying error types in Excel. By returning specific numeric codes for different error situations, users can effectively troubleshoot issues within their workbooks. This enhances the overall usability and reliability of Excel spreadsheets, allowing for more accurate data management.