# 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`

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

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

### Example 2 – Using 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.

• In FillÂ  >> select a color. Here, light Pink.
• 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 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.

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

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

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

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

## Related Articles

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

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

Advanced Excel Exercises with Solutions PDF