Skip to content
Book a demo Try the demo MacWindows

Conditional formatting

Colour cells by their values: highlight values above or below a number, shade a range on a colour scale, add data bars or show arrow and traffic-light icons.

All plansUpdated

Conditional formatting colours cells according to what is in them, and keeps up as the values change. Highlight every amount over budget, shade a column from red to green, draw a bar in each cell to compare sizes, or mark each figure with an arrow. The values themselves are never changed.

Before you start

  • Plans. Conditional formatting is on every plan.
  • Select the cells first. A rule applies to the range you have selected when you add it, such as D2:D50.

Open conditional formatting

Select the cells, then click Conditional formatting for the selection (the palette) in the toolbar, or choose Format > Conditional formatting. The Conditional formatting window opens and shows the range it applies to.

Choose a style along the top: Single colour, Colour scale, Data bar or Icon set.

Highlight cells that match a rule

  1. Choose Single colour.

  2. Choose a condition:

    Condition Matches cells that
    Greater than, Less than, Greater or equal, Less or equal Hold a number compared with the one you type.
    Equal to, Not equal to Hold that number, or exactly that text.
    Between Hold a number from the first value to the second, inclusive.
    Text contains Contain the text you type, in capitals or not.
    Is not empty, Is empty Have something in them, or nothing.

  3. Type the value. For Between, type the second value in and.

  4. Choose a Fill colour from the swatches, and tick Bold if the text should be bold too.

  5. Click Add rule.

Matching cells are coloured at once, and change as the values change.

Shade a range on a colour scale

  1. Choose Colour scale.
  2. Pick a Low colour and a High colour. Tick 3-colour to add a Mid colour.
  3. Click Add rule.

Each number in the range is shaded between the colours by where it sits between the range's lowest and highest values. This works well for a quick heat map of a table.

Add data bars

  1. Choose Data bar.
  2. Pick a Bar colour.
  3. Click Add rule.

Each number gets a bar behind it, as long as its share of the largest value in the range, so you can compare sizes at a glance.

Show icons

  1. Choose Icon set.
  2. Pick a set: arrows (↑ → ↓), traffic lights (● ● ●) or triangles (▲ ◆ ▼).
  3. Click Add rule.

The range is split into thirds between its lowest and highest values. Values in the top third get the green icon, the middle third amber, and the bottom third red.

See and remove rules

Open the Conditional formatting window again. Each rule on the sheet is listed with a colour sample, its range and what it does. Click the bin (Remove rule) beside one to delete it.

When several single-colour rules match the same cell, the one added last sets the colour.

Common questions

My fill colour disappeared. A conditional rule that matches the cell colours it over your own fill. Remove the rule, or change its range.

Can I change a rule? Remove it and add a new one.

The rule highlights nothing. Check the range in the rule list, and that the cells hold numbers if you used a number condition. Values typed as text, such as "1,200 approx", are not compared as numbers.

Still need help? Contact support at [email protected].