Intermediate Excel · Interactive lesson 6 of 12
Conditional formatting
Conditional formatting reformats a cell automatically based on a rule — no formula result to read, just color doing the explaining. The same toolkit spots outliers, duplicates, and trends at a glance.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialHow to Use Conditional Formatting in ExcelSkill check
Try these first:
- Highlight cells where a value crosses a threshold.
- Highlight duplicate values in a column.
- Highlight the top or bottom values in a range.
- Add data bars to visualize relative size at a glance.
All familiar? Skip ahead to I7 — Data Validation & Input Control.
Targeted teach
What you're learning
- Highlight cell rules
- Greater than, less than, between — a plain threshold rule, no formula required.
- Top/bottom rules
- Highlight the top N values, bottom N%, or above/below average — relative to the range, not a fixed number.
- Duplicate values
- Flags any value that repeats anywhere in the selected range — useful for catching a repeated invoice or ID number.
- Formula-based rules
- The most flexible option — write a formula that returns TRUE or FALSE, and the rule applies wherever it's true.
- Data bars & color scales
- A bar or color gradient drawn inside the cell itself, sized or shaded relative to the other values in the range.
- Rule manager & order
- Rules apply top to bottom — when two rules could both fire on the same cell, whichever is listed first (or has "stop if true" checked) wins.
Where to find it in Excel
Home›Conditional Formatting
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Sales | Target | |||||
| 2 | Ana | 9,400 | 94% | |||||
| 3 | Ben | 6,150 | 58% | |||||
| 4 | Chris | 8,900 | 84% | |||||
| 5 | Dana | 5,200 | 49% | |||||
| 6 | Eli | 7,700 | 73% | |||||
| 7 | ||||||||
| 8 |
Swipe sideways to see the whole window.
- 1Home › Conditional Formatting.
- 2Highlight Cells Rules covers greater than, between, text and duplicates.
- 3Greater Than colors every value over the number you type.
- 4Data Bars and Color Scales draw the values inside the cells.
- 5Manage Rules lists every rule, its range and its order; New Rule › Use a formula is here too.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided practice
Toggle each rule on and off below — they can combine, just like layered rules in the Rule Manager.
Independent variation
Rule a real table
Take a table you track regularly — expenses, tasks, inventory — and apply at least two conditional formatting rules to it: one plain threshold rule, and one formula-based or duplicate rule. Decide, and write down, which rule should win if they ever overlap on the same cell.
Spot a mistake or have an idea for this lesson? Send feedback