How To Use Excel ISERROR Function? [ 3 Easy Examples]

The Excel ISERROR function is part of the Information functions category. If the supplied value represents an error, the function will return TRUE; otherwise, it will return FALSE. #NAME?, #NUM!, #N/A,  #REF!, #VALUE!, #DIV/0!, or #NULL are all valid errors.

The ISERROR function in Excel is used in conjunction with the IF function to detect a potential formula mistake and display other formulas or text strings as messages or blanks. If an error is detected, it can also be utilised with the IF function to show a custom message or conduct some other calculation.

We must deal with algorithms and data in financial analysis. Our spreadsheet frequently has a huge number of formulas, and they don’t always behave as expected.

Formula:   =ISERROR(value)

ISERROR function in Excel  applies the argument as shown below:

Value: This is the targeted cell that you want to test. It is mostly provided as an address of the cell.

So, quickly scroll down to this post to learn how to use the Excel ISERROR function…

How To Use ISERROR Excel Function?

Consider the following scenarios to better understand the ISERROR Excel function:

Example 1:

Let’s look at the function’s output when we submit the following information:

Example 2:

We may utilise the ISERROR function wrapped in the SUMPRODUCT function to count the number of cells with errors.

Let’s say we’re given the following information:

Using the formula =SUMPRODUCT(–ISERROR(G22:CH27)), we can get the count of cells with an error, as shown below.

In the above formula:

The SUMPRODUCT function accepted one or more arrays and calculated the sum of the products of corresponding numbers.

ISERROR now evaluates each of the cells in the given range. The result will be TRUE,FALSE,FALSE,FALSE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,FALSE,FALSE.

As we used double unary, the results TRUE/FALSE were transformed to 0 and 1. It made the results look like {0,1,1,1,0,0,1,0,0,0,1,1}.

SUMPRODUCT now sums the items in the given array and returns the total, which, in the example, is the number 6.

As an alternative, the SUM function can be used. The formula to use will be =SUM(–ISERROR(range)). Remember to put the formula in an array. For the array, we need to press Control + Shift + Enter instead of just Enter.

Example 3:

Here in this case we will see for instance if we wish to provide a custom message, for example, instead of getting #DIV/0 error, we want the value to be 0. We can use the formula =IF(ISERROR(A4/B4),0, A4/B4) instead of A4/B4.

Suppose we are given the following data:

Instead of TRUE, if we want to enter the value 0, the formula to use will become=IF(ISERROR(B6),0,B6) and not B6, as shown below:

We get the result below as shown:

Method for Manually Replacing ERROR Values

However, we may use the ISERROR, formula to replace the error; there is one manual approach for doing so: the discovered and replace method.

1. After applying the formula, copy and paste only the values.
2. To open the replace box, press Ctrl + H and type the error value (#N/A, #DIV/0!, etc.). Make a note of the sort of error you’d like to replace.
3. Substitute “Data Not Found” for the replace with values.
4. Select Replace All from the drop-down menu.

All of the previously specified error values would be replaced with Data Not Found as a result of this.

Note: If you’ve applied a filter, the visible cells only technique should be used to replace them.

Things to Keep in Mind:

Excel ISERROR function may beautify your numerical reports by eliminating all types of mistakes. If the data contains an error type and pivot tables are used, the error will appear in the Pivot table as well. By detecting erroneous values, ISERROR can enhance the report’s appearance and spare you from embarrassment. Only one cell can be affected by ISERROR at a time. It can’t take a lot of cell ranges. When applied to a range of cells, only the first cell of the range is taken into account.

Apart from this if you face any problem related to Excel then I recommend you to Download the powerful Excel repair tool. This advanced tool can fix all your  Excel corruption errors and issue. Not only this tool can also restore formulas, charts, tables, chart sheets, filters, sorts, cell comments.

If you have any queries related to this topic then do share with us on our official Facebook and Twitter page and don’t forget to Like and share this post with your friends…

Good Luck….