2.4Conditional formatting
The essentials in 30 seconds
- The color follows the rule: the format applies only if the condition is true — it recalculates by itself.
- Ready-made first: thresholds, duplicates, data bars and icons — formula only for custom work.
- Whole row = $ on the column: =$C2<$D2 freezes the column, not the row — without the $, the rule drifts.
Running mini-case — Wassim and the table that lights up
Wassim (2.3) computes his delays, but his boss no longer reads his 200-row table: everything looks the same. Wassim applies conditional formatting: red when the delay exceeds 30 days, data bars on the amounts to see the big ones at a glance, an icon set on the statuses. To highlight the whole row of an invoice whose actual exceeds the target, he writes a formula-based rule =$C2>$D2. The table lights up and updates itself on every entry. In 2.5, these rules will live in a structured table that grows without breaking them.
Continuation of the 2.3 thread: Wassim's calculations become visible at a glance — and continue in 2.5.
1. A formatting that depends on the value
Conditional formatting (CF) applies a format (color, bold…) only if a condition is true: red if delay > 30 days, green if invoice validated. It updates automatically when the data changes — far more reliable than painting the cells by hand. Wassim no longer has to recolor his tracker: the rule works on every entry.
2. Ready-made rules & visuals
Excel offers ready-made rules: greater/less than, between, text that contains, top 10, duplicates. And visuals: data bars (mini-gauges in the cell), color scales (heat map), icon sets (arrows, traffic lights). Wassim puts bars on the amounts, scales on the delays, lights on the statuses: his boss reads the month in ten seconds.
3. Formula-based rules
For custom work, you write a formula that returns TRUE/FALSE, e.g. =$C2>$D2 to highlight the whole row when the actual exceeds the target. Tip: freeze the column with $ (mixed reference) to apply the rule row by row over the whole range. Wassim colors the entire row of the off-target invoices with a single rule set on A2:E200.
🔎 Concrete example — 🔎 Concrete example — Wassim's off-target rows
Actual in C, target in D; highlight the row if the actual exceeds the target:
Rule «use a formula» on A2:E200:
=$C2 > $D2 → format: light orange fill
# $ before the COLUMN (C, D) → the rule tests each ROW
# no $ before the 2 → it goes down row by rowReading: the mixed reference $C2 freezes the column, not the row: each row is tested on its own C and D.
Lesson: a single formula rule dynamically colors the whole table, new rows included.
Section vocabulary
- CF
- Conditional formatting: format applied only if a condition is true, recalculated automatically.
Simple example: Wassim colors the delays of more than 30 days red.
What it is not: a color painted by hand, frozen.
Reflex: living data = rule, never paint.
- Data bars
- Proportional mini-gauges drawn in the cells: large amounts jump to the eye.
Simple example: Wassim gauges the invoice amount column.
What it is not: a separate chart on another sheet.
Reflex: compare at a glance.
- Icon sets
- Symbols (arrows, lights, circles) assigned according to thresholds: the status becomes visual.
Simple example: Wassim puts lights on the validation statuses.
What it is not: free text typed by hand.
Reflex: visible thresholds, immediate reading.
- Formula rule
- Custom condition returning TRUE/FALSE (e.g. =$C2>$D2), with $ on the column to act row by row.
Simple example: Wassim highlights the whole off-target row.
What it is not: a simple threshold rule without a formula.
Reflex: $ on the column, not on the row.
In practice — In practice — Making colors talk
- Apply «greater than 30 → red» on a column of delays.
- Add data bars on a column of amounts.
- Spot duplicate suppliers with the built-in rule.
- Highlight a whole row with a formula rule (=$C2>$D2).
Ready-to-say script — presenting the table to the boss (30 s)
« The red is the delays of more than thirty days; the bars show the large amounts; the orange rows exceed the target. Everything updates itself on each entry: no need to reread two hundred rows, the priorities jump to the eye. »
Say it while scrolling through Wassim's tracker, colors and icons in support.
Copy-paste template — whole-row rule
Range: A2:E200
Formula: =$C2>$D2 → light orange fill
Threshold: delay > 30 → light red fillCritical delays
Flag unpaid items of more than 30 days: Wassim sets a red threshold on the delay column computed in 2.3.
Large amounts
See the large invoices without sorting: Wassim adds data bars on the amounts.
Duplicate suppliers
Suppliers entered twice: Wassim runs the built-in duplicates rule after his 2.3 cleaning.
Off-target row
Color the whole row, not one cell: Wassim writes =$C2>$D2 with the $ on the column.
✓ Best practices
- Let CF react to the data (no manual painting).
- To highlight a row: formula rule + $ on the column.
- Stay sober: color must carry meaning, not decorate.
✗ Common pitfalls
- Painting the cells by hand (does not update).
- Stacking too many rules → unreadable and slow table.
- Forgetting the $ → the rule drifts and colors the wrong rows.
Key takeaways
- CF applies a format if a condition is true, and updates itself.
- Ready-made rules: thresholds, between, duplicates, top 10.
- Visuals: data bars, scales, icon sets.
- Formula rule + mixed reference ($C2) to act row by row.
48h challenge — apply tomorrow
On your real tracker: a red threshold on the delays, data bars on the amounts, and a formula rule that highlights the whole off-target row.
- I can apply thresholds, duplicates and visuals (bars, scales, icons).
- I can write a TRUE/FALSE formula rule for custom work.
- I freeze the column with $ to color the whole row.
Frequently asked questions
My CF colors only one cell instead of the row?
Use a «formula» rule applied to the whole range, with the column frozen in mixed mode ($C2). Without the $ on the column, the condition does not point to the right cell for each row.
Does CF slow down the workbook?
Reasonable use, no; but thousands of rules or rules on entire columns can weigh. Limit the number of rules and target precise ranges (A2:E200, not A:E).
To highlight the whole row when C exceeds D, which rule formula?