No Ticket Needed

Appearance

6 min fix 3 min read Recently reviewedBeginner

By , IT support technician

Quick Answer

Highlight your data, click Conditional Formatting on the Home tab, select Highlight Cells Rules, choose a rule (like 'Greater Than'), and pick a color. Excel will automatically color cells that match.

How to Use Conditional Formatting in Excel ​

Applies to: Microsoft 365 (Excel desktop, Excel for the Web)
Article Type: How-To
Last Updated: 2026-03-09

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.

Prerequisites ​

  • 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).
  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 / ErrorPotential CauseSolution
Formatting not appearingRule applies to wrong rangeOpen Manage Rules and check the Applies to column for the correct cell range.
Multiple colors showing unexpectedlyOverlapping rulesOpen Manage Rules, reorder rules, and check Stop If True on high-priority rules to prevent lower rules from applying.
Custom formula rule not workingFormula error or wrong referenceVerify 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).