Penrose Scaffolding's aged-debt sheet turns an invoice red at sixty days, and credit control works the red rows every Monday. For five weeks three invoices worth £41,800 sat at ninety days and over, in plain black and white.
The rule was right. It was wrong about where it lived: the Applies to box read =$A$2:$G$8, and those three invoices had been pasted in at row 9.
| Measure | What the sheet showed | What the ledger held |
|---|---|---|
| Unpaid invoices over 60 days | 3 | 6 |
| Value highlighted red | £40,800.00 | £82,600.00 |
| Value never chased | £0.00 | £41,800.00 |
| Rows inside the rule's range | 7 | 10 |
A conditional format is not a property of your data. It is a property of a range, and a range is a thing that stops somewhere.
01The Rule Was Right; Its Range Was Not
Six Invoices Over Sixty Days, Three of Them Red
Ten invoices from Penrose Scaffolding's aged-debt sheet, sitting in A2:H11. Column C is days overdue and column E is the status; column F records whether the row falls inside the conditional formatting rule's Applies to range, and column G is what the sheet actually did. The rule is `=AND($C2>60,$E2="Open")` with a red fill, and its Applies to box reads `=$A$2:$G$8` — written when the sheet ended at row 8. Six rows are unpaid and over sixty days: INV-4118, INV-4133 and INV-4151 inside the range and red, worth £40,800.00 between them, and INV-4172, INV-4181 and INV-4190 pasted in at rows 9 to 11 and white, worth £41,800.00. INV-4166 is the control: ninety-seven days old, inside the range, and correctly not red because it is marked Paid.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Column C is days overdue and column G is what the sheet did with each row. Rows 3, 5 and 7 are red and right. Rows 9, 10 and 11 are over sixty days, unpaid, and white.
The difference between them is not in the data. INV-4172 at ninety-three days is further past due than anything above it; it was pasted in below the last cell the rule had ever been told about.
Row 8 is the control. INV-4166 is ninety-seven days old and inside the range, and it is correctly not red, because it is marked Paid and the rule asks for both conditions at once.
Scenario: Select a cell in the last row of your own highlighted sheet and read Home > Conditional Formatting > Manage Rules > Applies to. Then put =COUNTIFS($C$2:$C$11,">60",$E$2:$E$11,"Open") in a spare cell and compare its answer with the number of red rows you can count.
02Applies To: Where a Rule Actually Lives
Every rule carries a range, and Excel stores that range with the rule rather than with the cells. Home > Conditional Formatting > Manage Rules shows it in the Applies to box, and the box is editable in place.
Set the drop-down at the top of that dialog to This Worksheet. On Current Selection it lists only the rules touching the cell you happen to be on, which is the usual reason a rule looks as though it has vanished.
The range never extends itself. Typing a row underneath it adds data and nothing else, which is how a sheet that worked all year stops working in the week it grows.
Editing Applies to is the one-line fix. Click into the box, type =$A$2:$G$5000, press Enter. Excel keeps the rule's formula exactly as written and evaluates it across the larger range.
Scope. This is the Excel desktop app, 2016 through Microsoft 365. The same dialog lives under Format > Conditional Formatting on the Mac, and the Applies to box behaves identically.
Excel for the web shows the rules and lets you edit the range, but will not reorder them, so a Stop If True problem has to be fixed in the desktop app.
Scenario: Open Manage Rules, switch the drop-down to This Worksheet, and read every Applies to on the sheet. Widen the one that stops short to =$A$2:$G$5000 and watch the rows below the old boundary take the format immediately.
03The Paste That Splits One Rule Into Forty
Copying a cell copies its conditional formats with it. Paste it where the rule does not reach and Excel does not extend the rule — it writes a second copy of it over the cells you pasted into.
Do that for a few months and Manage Rules lists the same condition eight, twenty or forty times, each with its own ragged range: $G$4:$G$7,$G$12,$G$18:$G$21.
The gaps between those fragments are rows with no rule at all. They are also a file that takes ten seconds to scroll, because Excel evaluates every fragment separately on every recalculation.
Fix it in one pass. Select the columns, use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells, then write the rule once over the range you actually want.
To move data without dragging its formatting along, copy it and use Home > Paste > Values. The destination keeps its own rules, and nothing new is added to the list.
Scenario: Copy a formatted cell and paste it three rows below the rule's range, then open Manage Rules with This Worksheet selected and count the entries. Clear them all with Clear Rules from Selected Cells and write one rule over =$A$2:$G$5000 instead.
04Rule Order, and Stop If True
Rules run top to bottom in Manage Rules, and the first rule to set a property wins it. A later rule can still set a different property, so a fill from one rule and a bold from another can land on the same cell.
Stop If True ends the list. Tick it and nothing below is evaluated for the rows that rule matched — which is exactly what a grey banding rule for Paid invoices does to the red you wrote underneath it.
The arrows at the top of the dialog move a rule up and down. Put the narrow, specific rules above the broad ones, and never leave a Stop If True where the next person will not look for it.
Stop If True is a survivor from the files where it was the only way to hide an icon set on some rows. In a sheet of plain fill rules it is almost always somebody's accident.
Scenario: In Manage Rules, tick Stop If True on your top rule and watch every rule below it stop firing on the rows the top one matches. Untick it, then move your red rule to the top with the up arrow and confirm it wins.
Try it in the grid05$C2, C2 and $C$2 Inside a Rule
A rule's formula is written once, for the top-left cell of Applies to, and Excel then copies it across the rest of the range exactly the way it copies a formula across a block of cells.
So with Applies to =$A$2:$G$8, the formula is written as though it sits in A2. =AND($C2>60,$E2="Open") locks both columns and lets the row number travel, which is what highlights an entire row.
Drop the dollars and =AND(C2>60,E2="Open") tests D2 and F2 from column B, E2 and G2 from column C, and nonsense from there on. Lock them fully and =AND($C$2>60,$E$2="Open") paints all seven rows the same colour as the first.
| In the rule | What each cell tests | Use it for |
|---|---|---|
$C2 | Column C, its own row | Highlighting a whole row |
C$2 | Its own column, row 2 | A rule driven by the header |
$C$2 | Always C2 | One threshold cell for all |
C2 | Shifts both ways | Almost nothing |
Select the top-left cell of the range before you open the dialog. Excel writes the formula against the active cell, and a rule typed while row 40 was selected is out by thirty-eight rows from the start.
Scenario: Select A2:G8, write the rule =AND($C2>60,$E2="Open") and check that whole rows turn red. Now edit the rule to remove the dollar signs and watch the fill scatter diagonally across the block.
06Rules That Never Return TRUE
A rule's formula has to evaluate to TRUE or FALSE. Anything else and Excel treats it as FALSE or refuses the formula outright, and neither outcome tells you which rows were skipped.
Text that looks like a number is the common one, and it fails in the loud direction. Excel ranks any text above any number, so a cell holding "93" as text makes =$C2>60 TRUE — and so does a cell holding "9".
Count your way to it. =COUNT($C$2:$C$11) counts the real numbers and =COUNTA($C$2:$C$11) counts everything that is not empty. A gap between them is the number of cells your comparison cannot be trusted on.
Blanks fail in the quiet direction. An empty cell reads as zero, so =$C2<30 is TRUE on every empty row below the data, and a rule meant to flag fresh invoices paints the rest of the sheet. Guard it: =AND($A2<>"",$C2<30).
Errors stop a rule dead. One #N/A in a column the rule reads makes the whole formula an error on that row, an error is not TRUE, and the row quietly keeps its default fill. =IFERROR($C2>60,FALSE) puts the decision back in your hands.
Scenario: Type an apostrophe in front of one number in your threshold column, so the cell holds text, and watch the row go red whatever the figure is. Then compare =COUNT($C$2:$C$11) with =COUNTA($C$2:$C$11) and fix every cell in the gap.
07Make the Range Grow: Tables and Columns
The range that never falls short is the one Excel maintains for you. Select the data, press Ctrl+T, and write the rule over the table column: every row added to the table arrives with the rule already on it.
A table grows when you type in the row beneath it or paste into it, and the Applies to follows. Manage Rules still shows cell addresses rather than Table1[Days Overdue], and Excel rewrites those addresses as the table moves.
Whole columns are the blunt alternative. An Applies to of =$A:$G never runs out, at the price of a rule evaluated over a million rows — noticeable on a heavy file, invisible on a small one.
The middle road is a generous fixed range such as =$A$2:$G$5000, with the blank rows handled in the formula rather than in the range: =AND($A2<>"",$C2>60,$E2="Open").
Whichever you choose, write it once. One rule over one honest range is the only version of this that another person can still read a year later.
Scenario: Select your data, press Ctrl+T to make it a table, and rewrite the rule over the table's column. Then paste three new rows onto the end and confirm they arrive formatted without anybody opening Manage Rules.
Try it in the grid08Count What Should Be Red
Colour is not data. Nothing in the sheet totals it, SUMIF cannot see it, and the only honest way to audit a highlight is to write its condition out as a formula beside the grid.
=COUNTIFS($C$2:$C$11,">60",$E$2:$E$11,"Open") returns 6 in this sheet, against three red rows. =SUMIFS($D$2:$D$11,$C$2:$C$11,">60",$E$2:$E$11,"Open") returns £82,600.00, against the £40,800.00 anybody was actually working.
=SUMPRODUCT(--($C$2:$C$11>60),--($E$2:$E$11="Open")) answers the same question in one argument-free line for anyone whose Excel is old enough to need it.
To list the rows rather than count them, spill them. =FILTER($A$2:$E$11,($C$2:$C$11>60)*($E$2:$E$11="Open")) returns all six, inside the rule's range or outside it, and =SORT() around it puts the oldest first.
Then check the range itself: =COUNTA($A$2:$A$5000) against the last row your Applies to mentions. If the data is longer than the rule, you already know what the colours are not telling you.
Scenario: Put the COUNTIFS and the red-row count side by side in two cells at the top of your sheet, with =IF(COUNTIFS($C$2:$C$11,">60",$E$2:$E$11,"Open")>3,"Check the range","OK") beside them, and leave them there.
09Nine Ways a Rule Goes Quiet
- Applies to stops short. The rule is fine, the rows below its last cell have no rule at all, and nothing in the sheet says so.
- Manage Rules is on Current Selection. The list looks empty because you are standing on a cell no rule touches. Switch it to This Worksheet.
- A paste split the rule into fragments. Twenty copies of one condition, each over a few rows, with unformatted gaps between them.
- Stop If True above it. A rule higher in the list matched the row and ended the evaluation before yours was reached.
- Rule order. Two fills on one row: the higher rule wins, and the lower one never shows even though it is firing.
- Missing dollar signs.
C2instead of$C2makes every column test a different column, so the fill scatters across the block. - Text where numbers should be. Text outranks every number in a comparison, so
=$C2>60is TRUE on a cell holding"9". - Blank rows below the data. An empty cell is zero, so any "less than" rule paints every empty row in the range.
- Clear Formats took it away. Home > Clear > Formats removes conditional formatting from the selection along with the bold and the borders, without a word.
10Mini Exercises
- Read the Applies to of every rule on a sheet with This Worksheet selected. Expect at least one range that ends before the data does.
- Copy a formatted cell, paste it outside the rule's range, and reopen Manage Rules. Expect a second copy of the same condition.
- Write
=AND($C2>60,$E2="Open")over A2:G8, then delete the dollar signs. Expect the fill to move diagonally instead of by row. - Put the text
"9"in the days-overdue column with a leading apostrophe. Expect the row to turn red under a rule that asks for more than 60. - Compare
=COUNTIFS($C$2:$C$11,">60",$E$2:$E$11,"Open")with the number of red rows. Expect 6 and 3.
What to Take Away
Nobody in this story typed a wrong figure or wrote a wrong rule. Ten invoices were aged correctly, the condition was exactly the condition credit control wanted, and the three rows it could reach went red on the right morning.
The rule's Applies to box stopped at row 8 because that is where the sheet ended on the day somebody wrote it. The three invoices that arrived afterwards were outside it, and a cell outside a rule's range is not a cell the rule declined to colour — it is a cell the rule has never heard of.
So widen the range, or better, make the range a table and let Excel keep it. Then write the count beside it, because a colour is the only output in Excel that nothing else can check.