Conditional formatting changes how cells look based on their values — highlighting, color-scaling, or adding icons automatically. It turns a wall of numbers into something you can read at a glance. Here's how to use it well.
What it does
Conditional formatting applies formatting (colors, bars, icons) to cells based on rules you set. Instead of manually coloring cells, you tell Excel "highlight anything over 100 in green" and it does it automatically, updating as values change. It's presentation and analysis in one.
Getting there
Select your cells, then go to Home > Conditional Formatting. You'll see several categories of rules.
Highlight Cells Rules
The most common type — format cells meeting a condition:
- Greater Than / Less Than / Between — highlight numbers in a range.
- Equal To — highlight specific values.
- Text That Contains — highlight cells containing certain text.
- A Date Occurring — highlight dates (last week, this month, etc.).
- Duplicate Values — highlight repeats (great for finding duplicates).
Example: select a sales column, choose Greater Than, enter 1000, pick a green fill. Every sale over 1000 turns green instantly.
Top/Bottom Rules
Highlight the extremes:
- Top 10 items (or top 10%).
- Bottom 10 items.
- Above or below average.
Useful for spotting your best and worst performers without sorting.
Data Bars, Color Scales, and Icon Sets
These add visual gauges inside cells:
- Data Bars — a colored bar in each cell proportional to its value, like a mini bar chart. Great for comparing magnitudes at a glance.
- Color Scales — a gradient (e.g. red-yellow-green) showing where each value falls between low and high. Perfect for heatmaps.
- Icon Sets — icons like arrows or traffic lights indicating tiers (up/flat/down, good/okay/bad).
These require no rule-writing — just select the range and pick the style. They're the fastest way to make data visually scannable.
Formula-based rules (the powerful option)
For conditions the built-in rules can't express, use a formula:
- Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- Enter a formula that returns TRUE for cells to format.
Example — highlight an entire row when its Status column says "Overdue." Select the rows, and use:
=$D2="Overdue"
The $D locks the column (so the whole row is checked against column D) while the row number stays relative. This highlights every row where D says Overdue — something the basic rules can't do.
The key trick: use $ to lock the column ($D2) when you want to format entire rows based on one column's value.
Managing rules
Conditional Formatting > Manage Rules shows all rules on the sheet, lets you edit or delete them, and control their order (rules higher in the list take precedence). If formatting looks wrong, this is where to check for conflicting rules.
Clearing it
Conditional Formatting > Clear Rules removes formatting from the selection or the whole sheet.
The takeaway
Conditional formatting makes data readable automatically — highlight values over a threshold, add data bars or color scales for instant visual comparison, or write a formula rule to highlight entire rows. Start with the built-in Highlight and Top/Bottom rules, add data bars and color scales for at-a-glance reading, and graduate to formula rules (with $ to lock columns) when you need full control.