How to Use Conditional Formatting with IFERROR in Excel

If you are looking for Excel conditional formatting with IFERROR, then you have come to the right place. In this article, we will describe 4 easy and effective examples of conditional formatting with IFERROR in Excel.

How to Use Conditional Formatting with IFERROR in Excel: 4 Suitable Examples

In the following dataset, you can see the Name, Salary, Week, and Per Week Salary columns. Now, we will determine per week salary by dividing Salary by Week.

Therefore, we write the following formula in cell E5.

=C5/D5

At this point, we press ENTER.
Since cell D5 contains 0, the result would be an error.
Here, you can see the result in cell E5.
Moreover, we will drag down the formula with the Fill Handle tool.

Therefore, you can see the complete Per Week Salary column.
You can see several error values in this column.

Next, we will show you 4 easy examples for highlighting these error values with the help of Conditional Formatting. Here, we used Excel 365. You can use any available Excel version.

1. Combining IF and IFERROR Functions to Highlight Errors

In this example, we will combine the IF and IFERROR functions to highlight errors using conditional formatting in Excel.

Steps:

In the beginning, we will select cells E5:E10.
Then, we will go to the Home tab >> select Conditional Formatting >> select New Rule.

At this point, a New Formatting Rule dialog box will appear.
Then, we will select Use a formula to determine which cells to format.
After that, we will type the following formula in the Format values where this formula is true box.

=IF(IFERROR(E5,TRUE)=TRUE,TRUE,FALSE)

Moreover, click on Format.

Furthermore, from the Fill group >> we will select a color.
Here, we selected light Green color.
You can see the Sample of the selected color.
Then, click OK.

In addition, click OK in the New Formatting Rule dialog box.

Therefore, you can see the error values are highlighted with a light Green color.

2. Employing ISERROR Function

Here, we will use the ISERROR function for doing conditional formatting for IFERROR.

Steps:

First of all, we followed the steps described in Method 1 to bring out the New Formatting Rule dialog box.
Then, we will select Use a formula to determine which cells to format.
After that, we will type the following formula in the Format values where this formula is true box.

=ISERROR(E5)

Moreover, click on Format.

Then, from the Fill group >> we will select a color.
Here, we selected light Pink color.
You can see the Sample of the selected color.
Furthermore, click OK.

In addition, click OK in the New Formatting Rule dialog box.

As a result, you can see the error values are highlighted with a light Pink color.

Read More: Excel ISERROR vs IFERROR Functions

3. Formatting Only Cells With Errors

In this example, we will use the Error option from Conditional Formatting to format only cells with errors. Hence, we will use conditional formatting for IFERROR in Excel.

Steps:

First of all, we followed the steps described in Method 1 to bring out the New Formatting Rule dialog box.
Then, we will select the Format only cells that contain option.
After that, click the drop-down icon of the Format only cells with box >> select Errors.

Then, we will click on Format.

After that, from the Fill group >> we will select a color.
Here, we selected light Yellow color.
You can see the Sample of the selected color.
Furthermore, click OK.

In addition, click OK in the New Formatting Rule dialog box.

As a result, you can see the error values are highlighted with a light Yellow color.

4. Hiding Error Values by Applying Conditional Formatting

In this section, we will convert the errors to 0 using the IFERROR function, and then, we will hide the 0 values.

Steps:

First, we will type the following formula in cell E5.

=IFERROR(C5/D5,0)

Here, the IFERROR function returns 0 when there is an error in the value.

At the moment, press ENTER.
Hene, you can see the result in cell E5.
Furthermore, we will drag down the formula with the Fill Handle tool.

Therefore, you can see the complete Per Week Salary column.
Here, you can easily notice 0 in the places of error values.
Next, we will hide these 0 values.

After that, we followed the steps described in Method 1 to bring out the New Formatting Rule dialog box.
Then, we select the Format only cells that contain option.
Here, the first box will contain Cell Value.
Then for the second box, we will click on the drop-down icon >> select equal to.

Along with that, in the third box, we will type 0.
In addition, click on Format.

At this point, a Format Cells dialog box will appear.
Then, from Number >> select Custom.
Afterward, type ;;; (three semicolons) in the Type box.
In addition, click OK.

Along with that, click OK in the New Formatting Rule dialog box.

As a result, you can see that the error values have been hidden.

Practice Section

You can download the following Excel file from the Download Practice Workbook section and practice the explained examples.

Download Practice Workbook

You can download the Excel file from the link below and practice while reading this article.

Conditional Formatting IFERROR.xlsx

Conclusion

Here, we have shown you 4 easy examples of using Excel conditional Formatting for IFERROR. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section.

<< Go Back to Excel IFERROR Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!