Quick answer: Conditional formatting paints cells automatically by their values: Highlight Cells Rules (greater than, duplicates, dates), Top/Bottom Rules (top 10, above average), and three data-visual modes — Data Bars, Color Scales, Icon Sets. The pro tier is formula-based rules, which can highlight whole rows from any logic you write. Setup lives on Home → Conditional Formatting; the art is in the Applies to range and rule order. Formulas that feed these rules: essential Excel formulas.
The built-in rules that cover 90% of needs
| Need | Rule path |
|---|---|
| Flag overdue items | Highlight Cells Rules → Less Than → =TODAY() (on date column) |
| Spot duplicate IDs | Highlight Cells Rules → Duplicate Values |
| Top 10 customers by revenue | Top/Bottom Rules → Top 10 Items |
| Above-average performers | Top/Bottom Rules → Above Average |
| Heat map of monthly sales | Color Scales → Red-Yellow-Green |
| In-cell progress bars | Data Bars → Gradient Fill |
| Status arrows next to KPIs | Icon Sets → 3 Arrows |
Formula rules: highlight the whole row
Select the entire data range (say A2:F500), then New Rule → Use a formula. The formula is written for the top-left cell of the range, with the column locked but the row free: =AND($D2="Chennai", $E2>1000) highlights every row where column D says Chennai and E exceeds 1000. The $D2 pattern is the whole trick — locked column, relative row — and it reuses exactly the reference logic from the formulas guide. Confirm, and the entire rows glow, not just single cells.
The three debugging habits
- Rules apply to the selection at creation time — the classic bug is selecting one cell, building the rule, and wondering why the table ignores it. Manage Rules → This Worksheet shows every rule’s Applies to range; edit it there without rebuilding.
- Rule order matters: rules evaluate top-down; two rules can fight over the same cell. Manage Rules lets you reorder and tick Stop If True to make a priority final.
- Paste-trashing: pasting rows over formatted ranges duplicates or breaks rules. Re-check Manage Rules after big paste operations — a sheet can accumulate hundreds of zombie rules and slow down.
Conditional formatting pairs naturally with the summaries from the PivotTable guide (color scales on a pivot make seasonality jump out) and with trend views from the charts and Power Query guide. For dashboards built with an instructor reviewing every rule, Ampersand Academy teaches Advanced Excel one-to-one.
Frequently asked questions
How do I highlight an entire row based on one cell value?
Select the whole data range, create a formula rule written for the top-left cell, and lock only the column: =AND($D2=Chennai, $E2 greater than 1000). The locked column with relative row makes the rule evaluate per row across the selection.
Why is my conditional formatting not applying to all cells?
The rule’s Applies to range is probably smaller than your data. Open Manage Rules, choose This Worksheet, and fix the range – or select everything before creating the rule next time.
Can I use TODAY() in a conditional formatting rule?
Yes – a Highlight Cells Rule with Less Than =TODAY() flags past dates dynamically, and the highlighting updates every day the file opens. Combine with AND for deadline windows.
How do I make one conditional formatting rule override another?
Rules evaluate top to bottom. In Manage Rules, drag the priority rule higher and tick Stop If True so lower rules never touch the cells it already formatted.
Do conditional formatting rules slow down large workbooks?
They can – thousands of rules from repeated pasting, or whole-column Applies to ranges on huge sheets, add real overhead. Clean up zombie rules in Manage Rules and bound the ranges to actual data.