Conditional formatting

Conditional formatting changes how cells look depending on their values — red text for overdue amounts, a bar whose length shows a number’s size, an arrow for up or down. The formatting follows the values: change a number and its formatting updates.

All conditional formatting commands are in the Rules menu in Home › Styles. Its tooltip says how many rules the sheet has.

Highlight cells

1. Select the cells.
2. Choose Home › Styles › Rules › Highlight cells….
3. In the Highlight cells dialog, choose a Condition:

4. Type the Value (and And for is between) where the condition needs one. Numbers compare as numbers; anything else is compared as text.
5. Choose a Format: Light red fill, dark red text, Yellow fill, dark yellow text, Green fill, dark green text, Red text, Red border or Bold.
6. Click Apply.

Empty cells never meet a greater-than, less-than or between condition.

Top and bottom rules

1. Select the cells.
2. Choose Home › Styles › Rules › Top and bottom….
3. In the Top and bottom rules dialog, choose a Condition: is in the top N, is in the bottom N, is in the top N% or is in the bottom N%.
4. In How many, type N. It starts at 10.
5. Choose a Format, as for highlight rules.
6. Click Apply.

Data bars

A data bar draws a bar behind each value, its length in proportion to the value within the selection.

1. Select the cells.
2. Choose Home › Styles › Rules › Data bars….
3. Under BAR COLOUR, click one of the five colours.
4. Click Apply.

Colour scales

A colour scale shades each cell along a range of colours according to its value.

1. Select the cells.
2. Choose Home › Styles › Rules › Colour scales….
3. Under Colours, choose Red · yellow · green, Green · yellow · red, White · red, White · green or Blue · white. The first colour is for the lowest values.
4. Click Apply.

Icon sets

An icon set puts an icon at the left of each cell, chosen by where its value falls in the selection.

1. Select the cells.
2. Choose Home › Styles › Rules › Icon sets….
3. Under Icons, choose 3 traffic lights, 3 arrows, 3 symbols, 3 flags, 4 arrows, 4 ratings, 5 arrows, 5 ratings or 5 quarters.
4. Optionally turn on:
   • Low values get the top icon — reverses the order, for values where lower is better.
   • Show the icon only, not the number — hides the value and leaves the icon.
5. Click Apply.

The values are split into equal shares by rank — thirds for a three-icon set — so one very large value doesn’t push everything else into the lowest icon. Text and empty cells get no icon.

How rules combine

The dialogs show the range a rule will cover — for example Format cells in B2:B40 where the value or Across B2:B40.

When you add a rule, it takes first place and existing rules move down one, because the rule you wrote last is the one you most likely want to win. Formatting from a rule is drawn over the cell’s own formatting; a rule’s fill, text colour, bold, italic, underline or strikethrough replaces the cell’s own for as long as the condition holds.

The status bar confirms each new rule with Rule added to followed by its range.

Manage rules

1. Choose Home › Styles › Rules › Manage rules….
2. In Conditional formatting rules, each rule on the sheet is listed with its range and a short description — for example Where the value greaterThan 100, Highlight duplicates, Colour scale of 3 or Icon set — 3Arrows.
3. Click the bin beside a rule (Delete this rule) to delete it.
4. Click Done.

If the sheet has no rules, the dialog says so. To change a rule, delete it and add a new one.

Clear rules

The status bar says Cleared followed by the number of rules, or No rules to clear.

Rules from other workbooks

Conditional formatting in workbooks made in other apps is shown, including formula-based rules, text rules such as “ends with” and “does not contain”, and rules for blanks and errors. Rules you add here are saved in the workbook in the same format, so they appear in Excel.

To find every cell covered by a rule, use Go to Special › Conditional formatting — see Selecting and navigating.

The accessibility checker warns about a rule that changes only colours, because some readers can’t see the difference. See Checking a workbook before you share it.