Conditional formatting changes the colour of cells automatically based on what’s in them. Overdue invoices turn red, top sales go green, duplicates light up — and it all updates as your numbers change.
Everything lives under Home > Conditional Formatting.
Highlight cells above (or below) a value
- Select the cells you want to format — for example, a column of sales figures.
- Go to Home > Conditional Formatting > Highlight Cells Rules.
- Choose Greater Than…
- Type a value, such as 1000.
- Pick a colour from the drop-down, then click OK.
The same menu has Less Than, Between, Equal To, Text that Contains and A Date Occurring (great for “yesterday”, “this week” or “next month”).
Spot duplicates instantly
- Select the column.
- Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values…
- Click OK.
Every value that appears more than once is highlighted. Switch the first drop-down to Unique to highlight the one-offs instead.
Highlight the top or bottom results
Conditional Formatting > Top/Bottom Rules can highlight:
- Top 10 Items (change 10 to any number)
- Top 10%
- Bottom 10 Items
- Above Average / Below Average
Quick visuals: data bars, colour scales and icons
These turn a column of numbers into something you can read at a glance.
| Option | What it does | Good for |
|---|---|---|
| Data Bars | A bar inside each cell, longer for bigger numbers | Sales, stock levels |
| Color Scales | Shades cells from one colour to another | Temperatures, scores, heat maps |
| Icon Sets | Arrows, traffic lights, ticks and crosses | Targets, trends |
Hover over each option to preview it before you click.
Colour a whole row based on one cell
This is the most useful trick of all — for example, turning the entire row red when the Status column says “Overdue”.
- Select all your data except the headings (for example, A2:F200).
- Go to Conditional Formatting > New Rule…
- Choose Use a formula to determine which cells to format.
- Type the formula, for example:
=$D2="Overdue" - Click Format…, choose a fill colour on the Fill tab, and click OK twice.
The $ before D locks the column, so every cell in the row checks column D. Leaving the 2 without a $ lets each row check its own status.
A few more formula ideas:
=$E2<TODAY()— due date has passed=$C2>=1000— sale of £1,000 or more=$F2=""— a required column is empty
Edit or remove rules
- Change a rule: Conditional Formatting > Manage Rules… Select This Worksheet at the top to see every rule, then click Edit Rule.
- Remove formatting: Conditional Formatting > Clear Rules, then choose Clear Rules from Selected Cells or Clear Rules from Entire Sheet.
Troubleshooting
The wrong cells are highlighted. Usually a formula problem. Check the row number in your formula matches the first row you selected (if you selected from row 2, use $D2).
Numbers aren’t being highlighted. They may be stored as text — look for a small green triangle in the corner of the cells. Select them, click the warning icon and choose Convert to Number.
Rules multiply when copying. Copying and pasting cells copies their rules too. Use Manage Rules to tidy up duplicates.
Wrapping up
Start with Highlight Cells Rules for quick wins, try Data Bars for instant charts, and learn the Use a formula option to colour whole rows. Your spreadsheets will be far easier to read.
