Conditional Formatting
Conditional formatting colours cells automatically based on a rule, so problems and highlights jump off the page.
What you will learn
- Explain what conditional formatting does
- Colour cells that meet a number rule
- Use colour scales and data bars to spot patterns
Let the colours do the work
Conditional formatting changes how a cell looks (its colour, for example) when it meets a rule you set. A row of numbers all looks the same; with conditional formatting, the big ones turn green and the small ones turn red on their own. It updates instantly when the data changes.
A simple rule: highlight high sales
Select your sales numbers, then go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than, and type the cut-off. Say we highlight every sale greater than 100:
Home > Conditional Formatting > Highlight Cells Rules > Greater Than > 100Note: Output: For sales 120, 90, 60 and 130, the cells holding 120 and 130 turn green; 90 and 60 stay plain. Excel checked each cell against the rule greater than 100 and coloured only the ones that passed. Change a 90 to 150 and it colours itself at once.
Three popular styles
| Style | What it shows | Good for |
|---|---|---|
| Highlight rules | Colours cells above/below a value, or equal to text | Flagging targets or problems |
| Colour scales | A heat-map: high = one colour, low = another | Seeing high and low at a glance |
| Data bars | A little bar inside each cell, longer = bigger | Comparing sizes inside the column |
| Icon sets | Small icons like up/down arrows | Quick status (good/ok/bad) |
A colour scale is especially handy for a whole table of numbers: it turns your data into a mini heat-map so the hot spots and cold spots are obvious without reading a single number.
Make the rule point at a target cell
Typing a fixed number like 100 into a rule works, but it is better to point the rule at a cell that holds your target. Then changing the target in one place re-colours the whole sheet. Put your target in E1, then in the Greater Than box select E1 (it appears as $E$1) instead of typing a number.
Conditional Formatting > Greater Than > =$E$1Note: Output:
With E1 holding 100, sales of 120 and 130 turn green. Change E1 to 125 and only 130 stays green — the colours follow the new target instantly.
The dollar signs in $E$1 lock the rule onto the one target cell (just like the absolute references you met earlier), so you set the target once and adjust it in a single place.
Tip: This “point at a cell” trick is the professional way: one target cell drives every highlight, so a manager can change the goal and watch the whole report recolour without touching any rules.
Watch out: Do not over-colour. If everything is highlighted, nothing stands out. Use conditional formatting for the few things that truly matter, like values over target or below zero.
Q. What does conditional formatting do?
✍️ Practice
- Enter eight sales numbers and highlight every value greater than 100 in green.
- Apply a colour scale to the same numbers and see the heat-map effect.
🏠 Homework
- Make a table of monthly expenses and use conditional formatting to colour any month above your budget in red. Then change one month and confirm the colour updates.