📎 This article includes 1 downloadable practice file ↓
The built-in conditional formatting presets (Greater Than, Top 10, Data Bars) cover simple cases. The real power is Use a formula to determine which cells to format: any formula that returns TRUE colours the cell. Here are the ten rules I use most.
In this article
How to add a formula rule
- Select the range you want to format, starting from the top-left cell (e.g.
A2:F200). - Home → Conditional Formatting → New Rule → Use a formula…
- Write the formula as if for the top-left cell only (row 2), choose a format, OK.
💡 The dollar signs matter.
$C2 locks the column but lets the row change — that is what makes a rule colour the whole row based on one column.The 10 rules
| # | Goal | Formula (range A2:F200) |
|---|---|---|
| 1 | Colour the whole row when Status is “Overdue” | =$C2="Overdue" |
| 2 | Due date in the past and not done | =AND($D2<TODAY(), $C2<>"Done") |
| 3 | Due in the next 7 days | =AND($D2>=TODAY(), $D2<=TODAY()+7) |
| 4 | Duplicate IDs | =COUNTIF($A$2:$A$200, $A2)>1 |
| 5 | Top 5 amounts | =$E2>=LARGE($E$2:$E$200, 5) |
| 6 | Zebra stripes | =MOD(ROW(), 2)=0 |
| 7 | Weekend dates | =WEEKDAY($D2, 2)>5 |
| 8 | Required field empty | =AND($A2<>"", ISBLANK($B2)) |
| 9 | Text contains “urgent” (any case) | =ISNUMBER(SEARCH("urgent", $F2)) |
| 10 | ID missing from another list | =COUNTIF(Master!$A:$A, $A2)=0 |
Worked example: an overdue tracker
- Columns: A = Task, B = Owner, C = Status, D = Due date.
- Select
A2:D100, add rule 2 with a light red fill. - Add rule 3 with a light yellow fill.
- Open Manage Rules and make sure the red rule is above the yellow one; tick Stop If True on the red rule so overdue rows are not also marked yellow.
Because TODAY() recalculates, the colours update every day by themselves.
Common problems
- Everything or nothing is coloured: the formula refers to the wrong starting row, or is missing
$. Re-select the range from row 2 and edit the rule. - Rules multiply when copying rows: Manage Rules → delete duplicates and fix “Applies to” to one range.
- Slow workbook: avoid whole-column references like
$A:$Ain rules on large sheets; use exact ranges or Tables.
Want visual bars instead of colours? See in-cell bar charts.
📎 Practice files for this article
- 📗Task tracker with 5 formula rulesOverdue, due-soon, duplicate, keyword and top-5 rules already applied u2014 open Manage Rules to study them.⬇ XLSX · 7 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.