Free demo · Google Sheets · Lesson 7 · Basics · Conditional formatting

Conditional formatting: colour what matters

Set a rule once and Sheets keeps checking it: every mark below 40 turns red by itself, and the colour goes away when the mark improves.

Child | Marks Asha | 78 Kabir | 32 Zoya | 91 Arjun | 38 Diya | 64
  1. 1Select the marks › Format › Conditional formatting
  2. 2The rules panel shows your range
  3. 3Format cells if… Less than 40, light red fill
  4. 4Done: marks below 40 turn red
  5. 5Change 32 to 75: the colour goes away by itself
  6. ✓The sheet shows you where to look
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. Enrol

Tip: press → or Space to step through, ← to go back, R to replay.

About this lesson

Conditional formatting changes the look of a cell according to a rule, so that the important values stand out without anyone colouring cells by hand. In Google Sheets you select the cells and choose Format → Conditional formatting; the Conditional format rules panel opens on the right with the range filled in. Under Format cells if you pick a condition such as Less than and type a value, then choose a formatting style, for example a light red fill, and click Done. In this lesson every quiz mark below 40 turns red. The rule stays live: when a mark is corrected, Sheets checks it again and the colour appears or disappears by itself. Other conditions include Text contains, Date is before and Is empty, and the Color scale tab shades numbers from the lowest to the highest like a heat map. The full course goes on to custom formulas that colour a whole row, the order in which several rules are checked, and asking Gemini to set up a rule from a plain sentence.

Key points

  • Select the cells, then Format → Conditional formatting
  • Format cells if… Less than 40, with a fill colour
  • The rule is live: the colour follows the value