What Conditional Formatting Actually Does
Conditional Formatting recolors cells automatically when values change — overdue dates turn red, top performers glow green, data bars grow inside cells. For assignments, it turns a flat table into an analytical visual with zero chart work.
The 6 Rules Worth Knowing
1. Highlight top / bottom performers
Select scores → Home → Conditional Formatting → Top/Bottom Rules → Top 10%. Instant "who excelled" view for any report.
2. Flag overdue dates
Select due dates → New Rule → Format only cells that contain → Cell Value less than =TODAY() → red fill. Add a second rule: equal to =TODAY() → amber ("due today").
3. Data bars for in-cell charts
Select amounts → Data Bars → pick a solid fill. Widen the column and numbers become bars — perfect for budget vs actual tables.
4. Color scales for heatmaps
Select a matrix (months × regions) → Color Scales → Green-Yellow-Red. One click turns a boring grid into a heatmap worthy of your appendix.
5. Formula rule: highlight entire rows
=$D2>10000
Select A2:F500 → New Rule → Use a formula → enter the formula with a locked column ($D) and relative row (2). Entire rows light up where totals exceed 10,000 — the rule markers call "advanced".
6. Icon sets for status dashboards
Select % complete → Icon Sets → traffic lights, then Manage Rules → show icons only. Combine with the numbers in the next column for a KPI panel.
Rule Hygiene (Where Students Lose Marks)
- Manage Rules order matters: Home → Conditional Formatting → Manage Rules — top rule wins when "Stop If True" is ticked.
- Scope creep: rules applied to whole columns slow huge sheets — limit Applies To to your table.
- Copy-paste clones: pasting formats duplicates rules; use Paste Special → Values, or Clear Rules on the destination first.
- Print check: light fills vanish on paper — test Print Preview and darken fills if needed.
Want a dashboard that formats itself? We build templated sheets where colors, icons, and flags all update from your raw data.
