How to Use the ERROR.TYPE Function in Excel

Welcome to our comprehensive guide on the ERROR.TYPE function in Microsoft Excel and Google Sheets. This function is invaluable for identifying the types of errors within a cell, enabling you to diagnose and address issues more effectively in your spreadsheets.

Understanding the ERROR.TYPE Function

The ERROR.TYPE function provides a way to ascertain the type of error in a cell. It returns a numerical value each corresponding to a different type of error. This is particularly useful when managing complex formulas or handling large datasets.

Syntax

The syntax for the ERROR.TYPE function is consistent across both Microsoft Excel and Google Sheets:

ERROR.TYPE(error_val)
  • error_val: This argument is the error value or a reference to the cell that contains the error you need to identify.

Examples of Using the ERROR.TYPE Function

Below are scenarios demonstrating the application of the ERROR.TYPE function:

Example 1: Identifying the Error Type

In this example, cell A1 contains a formula that results in a #DIV/0! error. We will deploy the ERROR.TYPE function to pinpoint the error type.

Cell A1 Formula
#DIV/0! =1/0

Applying the ERROR.TYPE function:

=ERROR.TYPE(A1)

This formula returns 2, indicating a #DIV/0! error.

Example 2: Handling Errors in IF Statements

You may also use the ERROR.TYPE function to integrate error handling into your formulas. Consider the following usage within an IF statement:

=IF(ERROR.TYPE(A1) = 2, "Division by zero error", "No error")

In this scenario, if cell A1 contains a #DIV/0! error, the formula will display “Division by zero error”; otherwise, it will indicate “No error”.

Summary

In summary, the ERROR.TYPE function is an essential tool for pinpointing error types within your Excel or Google Sheets spreadsheets. By utilizing this function, you can enhance error management and boost the precision of your data analysis and computations.

More information: https://support.microsoft.com/en-us/office/error-type-function-10958677-7c8d-44f7-ae77-b9a9ee6eefaa

Other functions
Returns information about the formatting, location, or contents of a cell
Returns information about the current operating environment
Returns TRUE if the value is blank
Returns TRUE if the value is any error value except #N/A
Returns TRUE if the value is any error value
Returns TRUE if the number is even
Returns TRUE if there is a reference to a cell that contains a formula
Returns TRUE if the value is a logical value
Returns TRUE if the value is the #N/A error value
Returns TRUE if the value is not text
Returns TRUE if the value is a number
Returns TRUE if the number is odd
Returns TRUE if the value is a reference
Returns TRUE if the value is text
Returns a value converted to a number
Returns the error value #N/A
Returns the sheet number of the referenced sheet
Returns the number of sheets in a reference
Returns a number indicating the data type of a value