Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


4 Suitable Examples of Using Conditional Formatting with IFERROR in Excel

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

Dataset for Excel conditional formatting IFERROR

  • 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.

Using IF and IFERROR Functions for Excel conditional formatting IFERROR

  • 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.

Read More: How to Use IF and IFERROR Combined in Excel (3 Examples)


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.

Use of ISERROR Function for Excel conditional formatting IFERROR

  • 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.

Selecting a Color for Excel conditional formatting IFERROR

  • 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 (2 Suitable Examples)


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.

Selecting Error Format for Excel conditional formatting IFERROR

  • 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.

Read More: IFERROR with VLOOKUP in Excel (5 Different Uses)


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.

Use of IFERROR Function for Excel conditional formatting IFERROR

  • 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.

Use of Fill Handle Tool for Excel conditional formatting IFERROR

  • 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.

Excel conditional formatting IFERROR

  • 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.


Conclusion

Here, we show 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. You can visit our website Exceldemy for more related articles.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo