Theory
The report that watches itself
Meera wants her stock sheet to shout when something runs low: any item under 10 units should turn red the moment it drops, without Aryan re-checking manually every day.
Manual formatting is static, colour a cell red today and it stays red even after stock is replenished. That is useless for a living sheet.
Conditional formatting is different: you set a rule, and Excel re-applies it automatically whenever the data changes. The formatting follows the value. A red cell goes green the instant stock is topped up. The sheet watches itself.
Theory
A thermometer, not a paint stroke
A paint stroke is permanent, once red, always red, regardless of what happens. A thermometer shows red when hot and blue when cold, always reflecting the current temperature. Manual formatting is the paint stroke; conditional formatting is the thermometer: the colour is tied to the value, so it changes as the value changes. You are not colouring cells; you are writing a rule that colours them for you, forever.
At a glance
Conditional formatting types
| Type | Shows | Example |
|---|---|---|
| Highlight rules | Cells meeting a condition | Stock < 10 turns red |
| Data bars | An in-cell bar of magnitude | Sales bar per row |
| Colour scales | A heat-map gradient | High green, low red |
| Icon sets | Arrows / traffic lights | Up/down by value band |
Theory
Rules that re-evaluate
From Home > Conditional Formatting:
- Highlight Cells Rules: greater than, less than, between, text contains, and Duplicate Values (flags repeats visually).
- Top/Bottom Rules: top 10, above average.
- Data Bars / Colour Scales / Icon Sets: turn a column into an instant visual, a heat-map or in-cell bars, so trends jump out.
- Formula-based rules for custom logic:
=B2<10flags low stock,=$C2="Overdue"colours a whole row.
Every one is live: change the data and the formatting re-computes. This is what makes dashboards feel alive.
Quiz
Aryan sets a rule: cells under 10 turn red. Stock for Sugar is 5 (red). His assistant restocks Sugar to 40. What happens to the cell colour?
- It turns back to normal automatically, the rule re-evaluates and 40 is not under 10
- It stays red until Aryan manually clears the formatting
- It turns green permanently
- The rule is deleted when the value changes
Show the answer
It turns back to normal automatically, the rule re-evaluates and 40 is not under 10
Conditional formatting is live: the rule re-checks every time the value changes. Once Sugar is 40 (not under 10), the condition is false, so the red disappears automatically. This is the whole advantage over manual formatting, which would stay red forever. The format follows the value, like a thermometer, not a paint stroke.
Think first
Highlight vs Remove duplicates
Aryan has repeated customer IDs. Conditional formatting's 'Highlight Duplicate Values' and BCA105's 'Remove Duplicates' both deal with duplicates. What is the crucial difference, and when would he use each?
Show the answer
Highlight Duplicate Values only flags repeats with colour, the data stays intact, so Aryan can review them before deciding. Remove Duplicates (BCA105) permanently deletes the repeat rows. Use highlight to investigate ('are these really duplicates or two different Rahuls?'), and Remove Duplicates to clean once he is sure. Flagging is reversible and safe; deleting is permanent. Never delete before you have highlighted and checked.
Watch out
Where marks leak
Confusing conditional formatting (rule-based, live, re-evaluates) with manual formatting (static). Not knowing the types (highlight rules, data bars, colour scales, icon sets). And mixing up Highlight Duplicates (flags, keeps data) with Remove Duplicates (deletes). For formula-based rules, remember the $ locking rules from Unit 2 (lock the column to colour a whole row). These live-vs-static and flag-vs-delete distinctions are standard elective marks.
Theory
The visual language of dashboards
Red for danger, green for good, arrows for trend, these instant visual signals are what make a dashboard readable at a glance (coming soon). Meera should understand her whole chain's health in one look, and conditional formatting is how numbers become colour. Next lesson upgrades your charts: beyond BCA105's basic column/line/pie into combo, waterfall and radar charts for richer stories.
Summary
Key takeaways
- Conditional formatting applies formatting by a rule that re-evaluates LIVE as data changes.
- It differs from manual formatting, which is static (stays put regardless of the value).
- Types: highlight cells rules, top/bottom, data bars, colour scales (heat-maps), icon sets, formula rules.
- Highlight Duplicate Values FLAGS repeats (data intact); Remove Duplicates (BCA105) DELETES them.
- Formula rules use $ locking to colour whole rows (from Unit 2).
- Memory hook: a thermometer (follows the value), not a paint stroke (permanent).