How to Use Conditional Formatting with IFERROR in Excel

Get FREE Advanced Excel Exercises with Solutions!

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

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


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


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: How to Use IFERROR with VLOOKUP in Excel


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.


Download Practice Workbook

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


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo