Course / Conditional formatting
Highlight what needs attention
Colour the cells that pass a test, such as amounts over 1,000, and watch the colour follow the data. Beginner · 4 min · Conditional formatting, Greater than, Between
Colouring cells by hand goes out of date the moment a number changes. Conditional formatting colours them by a rule instead: highlight every amount over 1,000. The sheet checks the rule again whenever the data changes.
It always takes the same three moves: select the cells, choose a rule, choose a colour.
In Excel, all of this is under Home → Conditional Formatting. The simple tests are in Highlight Cells Rules. Here, the Conditional formatting button in the toolbar opens a panel with the same rules.
Steps
Which invoices are large? Select the amounts,
C2:C9, and click Conditional formatting in the toolbar. Choose Greater than, type 1000, and click Apply rule.Invoice 1006 was typed wrong: it should be 1,450, not 145. Change
C7to 1450 and watch the cell.Now the overdue days. Select
D2:D9, choose Between, and highlight 30 to 60 in Yellow.Cells can carry more than one rule. With
D2:D9still selected, add Greater than 60 in Red, for the invoices that are badly overdue.
Remember
- Select the cells, choose a rule, choose a colour.
- The colour follows the data: change a value and the rule is checked again.
- The same cells can carry several rules. Where two apply, the newest wins here; in Excel, the one higher in Manage Rules wins.
- In Excel, all of this is under Home → Conditional Formatting.
Previous: Stacked columns: the parts and the total · Next: Find duplicates at a glance