Summary

Conditional formatting highlights cells based on rules you define — such as coloring values above a threshold or adding data bars to visualize trends. This article explains how to apply, customize, and manage conditional formatting rules.

Before You Start

• Microsoft Excel (desktop or web version).

Instructions

1. Apply a Highlight Cells Rule

Quickly color cells based on their value.

1. Select the range of cells you want to format.
2. Go to Home > Conditional Formatting > Highlight Cells Rules.
3. Choose a rule type:
   • Greater Than — Highlight cells above a value.
   • Less Than — Highlight cells below a value.
   • Between — Highlight cells within a range.
   • Equal To — Highlight cells matching a specific value.
   • Text That Contains — Highlight cells containing specific text.
   • Duplicate Values — Highlight duplicates or unique values.
4. Enter the value or text in the dialog that appears.
5. Choose a formatting style from the dropdown (e.g., Light Red Fill with Dark Red Text).
6. Click OK. The cells update immediately.

2. Apply Data Bars, Color Scales, or Icon Sets

Visualize data patterns at a glance.

1. Select the range of cells.
2. Go to Home > Conditional Formatting.
3. Choose one of the following:
   • Data Bars — Adds gradient or solid bars proportional to the cell value.
   • Color Scales — Applies a two-color or three-color gradient across the range.
   • Icon Sets — Adds icons (arrows, flags, stars) based on value thresholds.
4. Hover over the options to preview the effect, then click to apply.

3. Create a Custom Rule

Build a rule with your own formula for advanced scenarios.

1. Select the range of cells.
2. Go to Home > Conditional Formatting > New Rule.
3. Select Use a formula to determine which cells to format.
4. Enter a formula that returns TRUE or FALSE (e.g., =A1>AVERAGE($A$1:$A$100) to highlight above-average values). The dollar signs pin the range so every cell is measured against the same list, while A1 stays relative and follows each cell. For guidance, see Understanding Absolute and Relative Cell References in Excel.
5. Click Format, set the fill color, font, or border, and click OK.
6. Click OK to apply the rule.

4. Manage and Remove Rules

Edit, reorder, or delete existing rules.

1. Go to Home > Conditional Formatting > Manage Rules.
2. In the Show formatting rules for dropdown, select This Worksheet or a specific selection.
3. To edit, select a rule and click Edit Rule.
4. To delete, select a rule and click Delete Rule.
5. To reorder, use the Move Up / Move Down arrows. Rules higher in the list take priority.
6. Click Apply > OK.

Troubleshooting

TIP

To quickly clear all conditional formatting from a range, select the cells, then go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

Symptom / Error	Potential Cause	Solution
Formatting not appearing	Rule applies to wrong range	Open Manage Rules and check the Applies to column for the correct cell range.
Multiple colors showing unexpectedly	Overlapping rules	Open Manage Rules, reorder rules, and check Stop If True on high-priority rules to prevent lower rules from applying.
Custom formula rule not working	Formula error or wrong reference	Verify the formula returns TRUE/FALSE. Use the first cell of the range as the reference (e.g., =A1>100, not =A5>100 when the range starts at A1).

Related Articles

• How to Create a PivotTable in Excel
• How to Fix Common Excel Formula Errors
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do

Did this solve your problem?