Yes, you can create rules in Excel using Conditional Formatting. But if you build them without understanding evaluation order, your rules will silently override each other — and you won’t even know why.
The Setup
You’re auditing a vendor payment log for Acme Corp’s AP team. Eight vendors, five payment statuses, amounts ranging from $1,250 to $89,400, and dates spanning Q1 2024. No formulas yet — just raw data in A1:E9.
| Vendor | Invoice # | Amount | Due Date | Status |
|---|---|---|---|---|
| Nexus Logistics | INV-7721 | $12,500 | 2024-03-15 | Paid |
| Veridian Systems | INV-7722 | $89,400 | 2024-02-28 | Overdue |
| Skyline Fabrication | INV-7723 | $3,200 | 2024-04-10 | Pending |
| TerraBuild Contractors | INV-7724 | $45,200 | 2024-01-22 | Overdue |
| Orion MedEquip | INV-7725 | $18,750 | 2024-03-05 | Paid |
| Lumina Design Group | INV-7726 | $6,900 | 2024-04-01 | Pending |
| Cedar Ridge Tech | INV-7727 | $22,300 | 2024-02-10 | Overdue |
| Aurora Textiles | INV-7728 | $9,150 | 2024-03-22 | Pending |
The Challenge
You need three visual rules: overdue invoices (red fill), high-value pending payments ($20k+ in yellow), and paid items (green text). Sounds simple. But here’s the trap: Excel evaluates rules top-down, and stops when one applies — unless you check ‘Stop If True’. Most people skip that box. So your $89,400 overdue invoice gets red — but never sees the yellow rule, because red fired first. Worse: you’ll think the yellow rule is broken. It’s not. It’s just ignored.
You also can’t use Status = "Overdue" alone — because the cell contains text, not a date comparison. You must build the logic from scratch using TODAY() and DATEVALUE(). And yes — you *can* reference cells outside the selected range. That’s legal. Most don’t know it.
Walking Through It
Select E2:E9 — the Status column. Press Alt + H + L to open Conditional Formatting > New Rule.
Rule 1 (Overdue): Use formula: =AND($E2="Overdue", $C2>0). Set red fill. Click OK.
| Vendor | Status | Amount | Rule Applied? |
|---|---|---|---|
| Veridian Systems | Overdue | $89,400 | ✓ (Red) |
| TerraBuild Contractors | Overdue | $45,200 | ✓ (Red) |
| Cedar Ridge Tech | Overdue | $22,300 | ✓ (Red) |
Rule 2 (High-value Pending): Select E2:E9 again. Alt + H + L > New Rule > Use formula. Enter: =AND($E2="Pending", $C2>=20000). Yellow fill. Check “Stop If True” — this is non-negotiable. Without it, red overrides yellow.
Rule 3 (Paid text): Select E2:E9 again. Alt + H + L > New Rule > Format only cells that contain → Text that contains “Paid”. Set font color to green. Leave “Stop If True” unchecked — we want this to apply *after* the first two.
The Result
Here’s what E2:E9 looks like after all three rules:
| Vendor | Status | Amount | Visual Effect |
|---|---|---|---|
| Nexus Logistics | Paid | $12,500 | Green text |
| Veridian Systems | Overdue | $89,400 | Red fill (no green text) |
| Skyline Fabrication | Pending | $3,200 | No formatting |
| TerraBuild Contractors | Overdue | $45,200 | Red fill |
| Orion MedEquip | Paid | $18,750 | Green text |
| Lumina Design Group | Pending | $6,900 | No formatting |
| Cedar Ridge Tech | Overdue | $22,300 | Red fill |
| Aurora Textiles | Pending | $9,150 | No formatting |
What Could Go Wrong
Mistake 1: Absolute vs. relative references inside formulas. If you type =E2="Overdue" instead of =$E2="Overdue", Excel shifts the reference as it applies down the column. Row 3 becomes =E3="Overdue" — correct. But if you select the whole range *before* writing the formula, Excel auto-adds $ signs inconsistently. Always double-check the formula bar after clicking OK.
Mistake 2: Applying rules to headers. If you select A1:E9 instead of A2:E9, Excel tries to evaluate “Vendor” against $E2="Overdue". That throws no error — it just returns FALSE for every header row, and wastes CPU. Worse: it makes rule management messy later. Always exclude headers.
Mistake 3: Using cell color to trigger another rule. You cannot write a formula like =CELL("color",E2)=3 to detect red fill. Excel doesn’t expose fill color in formulas. That’s a hard limit. If you need downstream logic based on formatting, use helper columns with the same original condition — not the visual result.
One last tip: Press Alt + H + L + M anytime to open the Conditional Formatting Rules Manager. Sort by “Order” to see evaluation sequence. Drag rules up/down to change priority — no need to delete and rebuild.
| Shortcut | Action | When to Use It |
|---|---|---|
| Alt + H + L | Open Conditional Formatting menu | Starting any new rule |
| Alt + H + L + M | Rules Manager | Debugging or reordering active rules |
| Alt + H + L + O | Clear rules from selection | Before rebuilding — prevents ghost rules |
| Ctrl + 1 | Format Cells dialog | Quickly copy format settings into a new rule |
