ISERROR Function in Excel (Table of Contents)

• ISERROR in Excel
• ISERROR Formula in Excel
• How to Use ISERROR Function in Excel?

ISERROR in Excel

When doing calculations or applying formula we often get errors. To check whether the cells have an Error or not we can use Iserror Formula in Excel.

The word “IS” means it is checking whether the selected cell has an error or not. If the referred cell has an error, it will return the result as TRUE, and if the referred cell does not have an error, it will return the result as FALSE.

ISERROR function is very helpful to check the errors we got from the formulas in the sheet. This formula determines the result based on the cell value.

ISERROR finds all the bellow errors in excel. Error lists are below.

#N/A, #VALUE!, #REF, #DIV/0!, #NUM!, #NAME?, #NULL!

If the function ISERROR finds any one of this in the cell then it will return the result as TRUE or else FALSE.

ISERROR Formula in Excel

The Formula for the ISERROR Function in Excel is as follows:

The Formula of ISERROR includes only one parameter i.e. value

Value: This is nothing but the targeted cell that you want to test.

This function is very helpful if we want to replace the error value with any other value.

How to Use ISERROR Function in Excel?

ISERROR Function in Excel is very simple and easy to use. Let understand the working of ISERROR in Excel by Some Examples.

ISERROR in Excel Example #1

Let’s look at the simple example of ISERROR Function in Excel. I have target vs actual data from Jan to May.

In column A, I have month list, in column B, I have a target list and in column C, I have achieved list.

I have calculated the efficiency level by dividing the achieved number by target number.

The problem is we got some errors as #DIV/0!.

If anyone of denominator or numerator is, zero we get the error as #DIV/0!When we are dividing numbers.

Let me find out which cell has an error value by using ISERROR function.

ISERROR Function returns the result as given below:

Since I have applied ISERROR Formula in Excel, if there is an error it is showing as TRUE or else showing as FALSE. Drag & drop this function and press CTRL +D for the below values.

However, this is not serving my end purpose. I want the data to be shown as zero if there is an error or else do the normal calculation.

Here I am using IF condition with ISERROR function.

A formula I have used here is:

=IF (ISERROR (C2/B2), 0%, C2/B2)

IF condition is checking if the calculation of C2/B2 is returning as an error. If the calculation returning as the error it will show the result as 0%,.or else do the calculation and show the actual percentage.

Now drag & drop this function and press CTRL +D for the below values.

How cool it is? Showing error values and replacing those error values with the 0% will make the report look beautiful.

ISERROR in Excel Example #2

We have another error function called IFERROR. It works the similar way that ISERROR works but there is slight modification are there.

Consider the below example for illustration of IFERROR function. Table 1 is the main data source and table 2 is the Vlookup table.

In column F, I have applied Vlookup formula to find the sales amount for laptop brands.

This VLOOKUP Function returns the result as given below:

Now drag & drop this function and press CTRL +D for the below values.

In the above table, I got an error for the brands Apple and Notepad. If you look at the main data table, there are no Apple and Notepad brands. That is why Vlookup has returned an error type as #N/A.

We can fix this issue by using the IFERROR Function.

Apply IFEEROR before VLOOKUP function. We need to write the Vlookup formula inside the IFERROR Formula.

=IFERROR (VLOOKUP (E3, $A: $B, 2, 0),”Data Not Found”). It returns the result as shown below:

Now drag & drop this function and press CTRL +D for the below values.

Firstly, IFERROR trying to find the value for the VLOOKUP formula.

Secondly, If VLOOKUP does not find a value then it will return an error. Therefore, if there is an error we will show the result as “Data Not Found”.

We have replaced all the #N/A values with “Data Not Found” text. I think this will look better than the #N/A.

Manual Method for replacing ERROR Values

However, we can replace error with ISERROR, IFERROR formula there is one manual method to do it, and that is found and replace method.

Step 1: Once the formula is applied and copy and paste only values.

Step 2: Press Ctrl + H to open replace the box and type the error value (#N/A, #DIV/0! etc). Whatever the error type you want to replace mention that error.

Step 3: Now write the replace with values as “Data Not Found”.

Step 4: Click on replace all button.

This would instantly replace all the mentioned error values with Data Not Found.

Note: If you have applied filter please choose visible cells only method to replace.

Things to Remember About ISERROR Function in Excel

• ISERROR can make your numerical reports beautiful by removing all kinds of errors.
• If the data contains error type and if you apply pivot tables then the same kind of error will occur in Pivot table too.
• ISERROR can make the report look beautiful by finding error values and saves you from embarrassments.
• ISERROR can be applied only to one cell at a time. It cannot take many ranges of cells.
• Only the first cell of the range is considered if it is applied to a range of cells.

You can download this ISERROR Function Excel template here – ISERROR Function Excel Template

Recommended Articles

This has been a guide to ISERROR in Excel. Here we discuss the ISERROR Formula in Excel and how to use ISERROR Function in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

1. RIGHT Function in Excel – MS Excel
2. LEN Function in Excel
3. HLOOKUP Function in Excel
4. EVEN Function in MS Excel

The post ISERROR Excel Function appeared first on EDUCBA.