Conditional formatting

Module 10 of 11 Spreadsheet in your browser

Shortcut drill What you'll do

Intro

Make Excel colour the cells that matter: duplicates, the top 3, overdue dates.

Select the cells, then add a rule. The colours update by themselves when the numbers change.

Styles

This lesson is about keyboard shortcuts, so it needs a computer with a keyboard.

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

In a bit more detail

What is conditional formatting?

A rule that colours cells when they meet a condition: over a limit, a duplicate, in the top 3. Change the numbers and the colours follow.
Where is it in Excel?

Home → Conditional Formatting. Select the cells first: the rule applies to the selection. Highlight Cells Rules has Greater Than, Less Than, Between, Equal To, Text that Contains and Duplicate Values; Top/Bottom Rules has the top and bottom items and Above Average.
Can a rule compare with another cell?

Yes: type a reference instead of a number, like =$F$2. The rule then follows that cell, so changing the report date re-colours the overdue invoices. In real life, =TODAY() keeps "overdue" up to date by itself.
How do I colour a whole row?

Select the whole table and use a formula rule, written for the top-left cell: =$C2>1000. The $ before C makes every cell in the row look at column C; the 2 has no $, so each row checks its own amount.
How do I change or remove a rule?

Conditional Formatting → Manage Rules lists every rule on the sheet. Where two rules colour the same cell, the one at the top of the list wins.

Know someone fighting with a spreadsheet? Send them this page.