How to Use Conditional Formatting in Excel
Conditional formatting changes a cell's color automatically based on its value — to highlight, rank, or flag data. Select your cells, open Home > Conditional Formatting, and pick a rule. Here is how to use every type, step by step.
Conditional formatting makes Excel color your cells automatically based on what they contain — turning a plain table into something you can read at a glance. Use it to highlight values above a target, flag overdue dates, rank the top performers, or spot duplicates. Once a rule is set, the colors update on their own as the data changes.
How to apply conditional formatting
- Select the cells you want to format.
- On the Home tab, click Conditional Formatting.
- Choose a rule type from the menu (see the types below).
- Set the condition and pick a color or style, then click OK.
The rule types, explained
| Rule type | What it does |
|---|---|
| Highlight Cells Rules | Color cells greater than, less than, between, equal to, or containing text |
| Top/Bottom Rules | Highlight the top 10, bottom 10, or above/below average |
| Data Bars | Draw a small bar in each cell, sized by its value |
| Color Scales | Shade cells on a gradient (for example, red-to-green) |
| Icon Sets | Add arrows, traffic lights, or ratings based on value |
A quick example: highlight values over a target
- Select the sales figures in column B.
- Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
- Type your target (say 1000) and choose a green fill. Click OK.
Every value over 1000 turns green instantly, and any new number you type is colored automatically.
Custom rules with a formula
For anything the built-in rules do not cover, use a formula. Choose Conditional Formatting > New Rule > "Use a formula to determine which cells to format", then enter a test that returns TRUE or FALSE. For example, to shade an entire row when column C says "Overdue":
=$C2="Overdue"The dollar sign locks the column so the whole row is tested. This is the same TRUE/FALSE logic behind the IF function.
Edit, find, or clear rules
- To change a rule: Conditional Formatting > Manage Rules, pick the rule, and edit it.
- To remove formatting: Conditional Formatting > Clear Rules, then choose selected cells or the whole sheet.
- Rules are listed in priority order in Manage Rules — the top rule wins when two overlap.
Conditional formatting also pairs well with other tools: highlight repeats before you remove duplicates in Excel, or color the results of a drop-down list so each choice stands out.
Frequently asked questions
How do I highlight an entire row with conditional formatting?
Use a formula rule. Select the whole range, choose New Rule > "Use a formula", and lock the column with a dollar sign — for example =$C2="Overdue". Because the column is fixed, Excel formats the full row whenever the test is true.
Why is my conditional formatting not working?
The usual causes are the wrong range selected, a formula missing the dollar sign, or a higher-priority rule overriding it. Open Conditional Formatting > Manage Rules to check the range, the formula, and the order of rules.
How do I copy conditional formatting to other cells?
Select a formatted cell, click Format Painter on the Home tab, then drag over the target cells. The rules copy across, adjusting their references to the new cells.