Conditional formatting
8 minutes · 4 steps · free, no sign-up
Colouring cells by hand works until the numbers change. Conditional formatting colours cells by a rule instead, so a low stock level turns red the moment it drops, and goes back to normal when you restock. You will set up four rules on a small stock list.
- Create a rule that colours cells above or below a number
- Add a second rule to the same range
- Colour cells that contain certain text
- Understand that the first matching rule wins
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceHow a rule is built
Apply to range which cells the rule watches
Format cells if greater than, less than, text contains, between ...
Value the number or text to compare with
Fill colour what the cell turns into when it matchesThe key ideasSummary: Conditional formatting
A formatting rule has three parts: where it applies (a range), when it fires (a condition such as "less than 10") and what it does (a fill colour). The colour is not stored in the cell. It is worked out each time from the current value, so it always matches the data.
You can add several rules to the same range. They are checked in order and the first one that matches wins, so put the most important rule first. Conditional formatting changes how a cell looks only, never its value, so it is safe to add to a finished sheet.
Watch out forCommon mistakes
- Colouring cells by hand. Hand-coloured cells never change when the data does. Use a rule for anything that depends on a value.
- Forgetting which rule wins. Two rules can match the same cell. Only the first one decides the colour.
- Applying a rule to the wrong range. A rule on B2:B7 does not touch column C. Check the "apply to range" box.
- Relying on colour alone. Add a word such as "Order" as well, for people who cannot tell the colours apart or who print in black and white.
Next stepTry it in the real Google Sheets
In Google Sheets choose Format → Conditional formatting. The "Custom formula is" option can colour a whole row from one cell, which is covered at the Adept level.