📎 This article includes 1 downloadable practice file ↓
The preset rules (greater than, top 10) colour single cells. Formula rules can colour a whole row based on any condition, which is what makes a register readable.
How a formula rule works
- Select the whole data area, e.g.
A2:L81(top-left cell A2 active). - Home > Conditional Formatting > New Rule > Use a formula.
- Write the formula for the first row only, as if it were in A2. Format when TRUE.
Excel applies it to every cell, shifting row numbers. The $ before the column letter keeps every cell of a row looking at the same column.
Rules worth stealing
| Highlight | Formula (A2 active) |
|---|---|
| Overdue rows | =$L2="Overdue" |
| Above-average amounts | =$K2>AVERAGE($K$2:$K$81) |
| A chosen month (month in cell N1) | =TEXT($B2,"yyyy-mm")=$N$1 |
| Top 3 invoices | =$K2>=LARGE($K$2:$K$81,3) |
| Sundays | =WEEKDAY($B2)=1 |
| Every other row (banding) | =MOD(ROW(),2)=0 |
Manage rules
Conditional Formatting > Manage Rules shows rule order. Tick Stop If True when an overdue row shouldn’t also get the above-average colour.
Common mistakes
- No $ before the column: only one column highlights, in a diagonal pattern.
- $ before the row too (
$L$2): every row copies row 2’s answer. - Hundreds of duplicated rules after copy-pasting rows. Clean up in Manage Rules now and then.
Practice
Download this lesson’s workbook below. Each task is a rule tested as a normal formula; then add the rules to the Sales sheet. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.
📎 Practice files for this article
- 📗Lesson 11 practice workbookSales register + 7 rule tests.⬇ XLSX · 19 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.