How to Use Conditional Formatting with the IFERROR Function in Excel – 4 Examples

 

The dataset showcases Name, Salary, Week, and Per Week Salary.

To determine per week salary by dividing Salary by Week:

  • Use the following formula in E5.
=C5/D5

Dataset for Excel conditional formatting IFERROR

  • Press ENTER.
  • Since D5 contains 0, the result in E5 result is an error.
  • Drag down the formula with the Fill Handle.

  • There are several error values in this column.

You can  highlight these error values using Conditional Formatting.


Example 1 – Combining the IF and the IFERROR Functions to Highlight Errors

Combine the IF and IFERROR functions.

Steps:

  • Select E5:E10.
  • Go to the Home tab >> select Conditional Formatting >> select New Rule.

  • In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
  • Enter the following formula in Format values where this formula is true.
=IF(IFERROR(E5,TRUE)=TRUE,TRUE,FALSE)
  • Click Format.

Using IF and IFERROR Functions for Excel conditional formatting IFERROR

  • In Fill  >> select a color. Here, light Green.
  • You can see the Sample of the selected color.
  • Click OK.

  • Click OK in the New Formatting Rule dialog box.

  • Error values are highlighted in light Green.

Read More: How to Use IF and IFERROR Combined in Excel


Example 2 – Using the ISERROR Function

Use the ISERROR function.

Steps:

  • Follow the steps described in Method 1 to display the New Formatting Rule dialog box.
  • Select Use a formula to determine which cells to format.
  • Enter the following formula in Format values where this formula is true.
=ISERROR(E5)
  • Click Format.

Use of ISERROR Function for Excel conditional formatting IFERROR

  • In Fill  >> select a color. Here, light Pink.
  • You can see the Sample of the selected color.
  • Click OK.

Selecting a Color for Excel conditional formatting IFERROR

  • Click OK in the New Formatting Rule dialog box.

  • Error values are highlighted in light Pink color.

Read More: Excel ISERROR vs IFERROR Functions


Example 3 – Formatting Cells With Errors Only

Steps:

  • Follow the steps described in Method 1 to display the New Formatting Rule dialog box.
  • Select Format only cells that contain.
  • Click Format only cells with >> select Errors.

Selecting Error Format for Excel conditional formatting IFERROR

  • Click Format.

  • In Fill group >> select a color. Here, light Yellow.
  • You can see the Sample of the selected color.
  • Click OK.

  • Click OK in the New Formatting Rule dialog box.

  • Error values are highlighted in light Yellow.

Read More: How to Use IFERROR with VLOOKUP in Excel


Example 4 – Hiding Error Values by Applying Conditional Formatting

Convert errors to 0 using the IFERROR function, and hide the 0 values.

Steps:

  • Enter the following formula in E5.
=IFERROR(C5/D5,0)
  • The IFERROR function returns 0 when there is an error.

Use of IFERROR Function for Excel conditional formatting IFERROR

  • Press ENTER.
  • You can see the result in E5.
  • Drag down the formula with the Fill Handle.

Use of Fill Handle Tool for Excel conditional formatting IFERROR

  • In the Per Week Salary column, 0 replaced error values.
  • Hide 0 values.

  • Follow steps described in Method 1 to display the New Formatting Rule dialog box.
  • Select Format only cells that contain.
  • In Format only cells with: enter Cell Value in the first box.
  • In the second box, select equal to.

Excel conditional formatting IFERROR

  • In the third box, enter 0.
  • Click Format.

  • In the Format Cells dialog box, in Number >> select Custom.
  • Enter: ;;; (three semicolons) in Type.
  • Click OK.

  • Click OK in the New Formatting Rule dialog box.

  • Error values are hidden.


Practice Section

Download the Excel file and practice.


Download Practice Workbook

Download the Excel file here.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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