IntermediateLesson 17 of 305 min read

Conditional formatting

Colour cells automatically from rules: highlight values, mark duplicates, shade whole rows with formula rules and add data bars and colour scales.

In this lesson you will

  • Apply quick highlight rules
  • Write formula rules for rows
  • Mark duplicates
  • Manage and clean up rules

About 15 minutes

Let the sheet point at what matters

Faisal reviews a sales sheet every Monday. He wants the best sales to jump out in green, the weak ones in red and every overdue invoice to shout at him. Reading each number and colouring by hand would take half an hour and be out of date by tomorrow. Conditional formatting colours cells automatically from rules, so when the numbers change, the colours follow.

The quick way

  1. Select the cells, for example F2:F13.
  2. On the Home tab, choose Conditional Formatting.
  3. Pick Highlight Cells Rules (Greater Than, Between, Text that Contains, Duplicate Values) or Top/Bottom Rules.
  4. Type the value, choose a colour, click OK.

Visual styles

Style Good for
Data Bars Seeing size at a glance inside cells
Color Scales Showing low to high across a range
Icon Sets Traffic lights or arrows for status
Top 10 / Above Average Highlighting leaders and laggards

Use these with restraint. One or two rules per sheet are easy to read, and a rainbow is not.

Rules that use a formula

The most powerful choice is New Rule, Use a formula to determine which cells to format. The formula must give TRUE for the cells to colour, and it is written for the top-left cell of your selection. Two details matter: use a dollar sign on the column to colour a whole row (=$F2>=500 colours the entire row whenever column F is at least 500), and leave the row number free so the rule moves down.

What would be highlighted

The twelve sales rows were put in a test sheet, and three formulas that could be used as rules were really calculated:

Rule formula Rows that would turn on
=F2>=400 (revenue at least 400) 5 rows: 4, 5, 10, 12, 13
=AND(B2=“East”,D2>=10) 1 row: 5
=COUNTIF($B$2:B2,B2)>1 (repeat region) 8 rows (every repeat after the first)

The numbers refer to sheet rows, where row 2 is the first sale. These matched an independent check made in Python. The third rule is the trick for marking duplicates: the expanding range $B$2:B2 counts how many times the region has appeared so far, so only the second and later occurrences turn on.

Rules stored in a file

The program below creates a workbook with five rules: green for revenue of at least 400, red for under 250, a formula rule that colours whole rows at 500 or more, a colour scale and data bars. It saves the file, reloads it and lists the stored rules. The output is real:

import openpyxl
from openpyxl.formatting.rule import CellIsRule, FormulaRule, ColorScaleRule, DataBarRule
from openpyxl.styles import PatternFill
wb = openpyxl.Workbook(); ws = wb.active
for i, v in enumerate([200, 300, 480, 400, 180, 300, 360, 240, 540, 240, 500, 420], start=2):
    ws[f"F{i}"] = v
red = PatternFill(start_color="F8C8C8", end_color="F8C8C8", fill_type="solid")
green = PatternFill(start_color="C8F0D0", end_color="C8F0D0", fill_type="solid")
ws.conditional_formatting.add("F2:F13", CellIsRule(operator="greaterThanOrEqual", formula=["400"], fill=green))
ws.conditional_formatting.add("F2:F13", CellIsRule(operator="lessThan", formula=["250"], fill=red))
ws.conditional_formatting.add("A2:F13", FormulaRule(formula=["$F2>=500"], fill=green))
ws.conditional_formatting.add("F2:F13", ColorScaleRule(start_type="min", start_color="FFFFFF", end_type="max", end_color="5A28C8"))
ws.conditional_formatting.add("F2:F13", DataBarRule(start_type="min", end_type="max", color="4A6EDD"))
wb.save("cf.xlsx")
wb2 = openpyxl.load_workbook("cf.xlsx")
for rng, rules in wb2.active.conditional_formatting._cf_rules.items():
    for r in rules:
        print(str(rng.sqref), "|", r.type, "|", r.operator or "-", "|", (r.formula or ["-"])[0])
F2:F13 | cellIs | greaterThanOrEqual | 400
F2:F13 | cellIs | lessThan | 250
F2:F13 | colorScale | - | -
F2:F13 | dataBar | - | -
A2:F13 | expression | - | $F2>=500

This shows the rules were stored correctly. The sandbox cannot show you how Excel paints the cells, so look at the result in your own copy.

Managing rules

  • Conditional Formatting, Manage Rules lists every rule on the sheet. Choose ‘This Worksheet’ to see them all.
  • Rules are applied from top to bottom; use Stop If True or reorder them when they conflict.
  • Copying cells copies their rules too, which can leave messy overlapping rules. Clean up in Manage Rules.
  • Clear Rules removes them from the selection or the sheet.

Good practice

  • Do not use colour alone. Add a status column with words so people who cannot see colours, and printouts in black and white, still work.
  • Pick calm colours. Pale red and pale green are easy to read.
  • Use a rule tied to a cell (such as =F2>=$K$1) so the limit lives in one visible place.
  • For dates, the rule =A2<TODAY() highlights overdue items automatically.

Practice

Open a list with dates, amounts and status. Add a rule that colours overdue dates, a data bar on amounts, and a formula rule that shades the entire row when status is Overdue. Then move the limit to a cell and make the rule refer to it.

Practice answer

For the whole row use a rule like =$C2=”Overdue” applied to A2:C50. If the colours land on the wrong rows, the usual cause is a missing dollar sign or a formula written for the wrong first cell.

Common mistake

Writing the rule formula for the wrong cell. The formula is relative to the top-left cell of the selection, so if you select from row 2 write it for row 2, not row 1.

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What must a formula rule return to colour a cell?

  1. A colour name
  2. A number
  3. TRUE
  4. Text

TRUE switches the format on.

Why use $F2 instead of F2 to colour whole rows?

  1. The column stays fixed while the row moves
  2. It is faster
  3. It changes the font
  4. It removes the rule

The dollar locks the column.

Where do you edit all the rules on a sheet?

  1. Page Setup
  2. Find and Select
  3. Manage Rules
  4. Sort

Choose This Worksheet to see all.

Why add a status column with words as well?

  1. Colour alone may not be seen or printed
  2. Colours are not allowed
  3. Excel needs it
  4. It sorts faster

Do not rely on colour alone.

Which rule marks repeated values using COUNTIF with an expanding range?

  1. =SUM(B2)
  2. =COUNTIF($B$2:B2,B2)>1
  3. =LEN(B2)
  4. =ROUND(B2,1)

Only later repeats turn on.

Summary

  • Conditional formatting colours cells from rules and updates itself.
  • Formula rules are written for the top-left cell; use $ on the column for whole rows.
  • Do not rely on colour alone.
  • Use Manage Rules to keep the sheet tidy.

Next lesson

Next: PivotTables, summarising thousands of rows in seconds.

Questions

Does conditional formatting slow down a workbook?

A few rules are fine. Hundreds of rules over huge ranges can slow it, so keep ranges tight.

Can I highlight a row based on another sheet?

Modern Excel allows references to other sheets in rules. Older versions need a named range.

Does it work in Google Sheets?

Yes. Use Format, Conditional formatting, with a custom formula for row rules.

Progress is saved in this browser.