Home → Styles → Conditional Formatting. Built-in rules: “contains a value”, “top 3”, “above average”, data bars, color scales (heat maps), icon sets.
Formula-based rules
The most flexible rules use a logical formula (returns TRUE/FALSE). Formatting is applied when the formula returns TRUE or a non-zero number, and not applied for FALSE, zero or an error.
- Select the range and note the active cell (the light one, usually top-left).
- New Rule → Use a formula to determine which cells to format.
- Write the formula from the point of view of the active cell, as if entering it there and copying it over the range.
To highlight the whole row, lock the column: =$C2<AVERAGE($C$2:$C$9) — column C is locked, the row moves.
Complex criteria: =AND($B2="North",$C2>500), =OR(...).
Tip from the manual: move heavy calculations to helper cells — the rule formula is calculated in every cell of the range.