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

  1. 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.

  2. Invoice 1006 was typed wrong: it should be 1,450, not 145. Change C7 to 1450 and watch the cell.

  3. Now the overdue days. Select D2:D9, choose Between, and highlight 30 to 60 in Yellow.

  4. Cells can carry more than one rule. With D2:D9 still selected, add Greater than 60 in Red, for the invoices that are badly overdue.

Remember

Previous: Stacked columns: the parts and the total · Next: Find duplicates at a glance