Course contents · Data validation and conditional formatting

Conditional formatting with formulas

Open the practice in the trainer →

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.

  1. Select the range and note the active cell (the light one, usually top-left).
  2. New Rule → Use a formula to determine which cells to format.
  3. 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.

Check yourself

  1. Range A2:C9, active cell A2. Which formula highlights the whole row when column C > 500?
  2. The rule formula returned #N/A. Is the format applied?