Calderstone Joinery marks a disputed invoice amber and pays everything else. On Thursday the run paid £40,917.80 where £31,707.80 was due. Every amount on the sheet was correct.
Two invoices under query went out together at £18,640.00. A third invoice that nobody had queried stayed behind, and the supplier put the account on stop over £9,430.00.
No formula in Excel can read a fill colour. The amber was the only thing on that sheet saying which rows to skip, and the only thing that could read it was a person.
| Thursday's run | By the fill colour | By a status column |
|---|---|---|
| Paid | £40,917.80 | £31,707.80 |
| Paid under query | £18,640.00 | £0.00 |
| Due, but held back | £9,430.00 | £0.00 |
| Formula behind it | none | =SUMIFS(C2:C13,E2:E13,"Due") |
Scope. Fill colours, Filter by Colour, Sort by Colour,
SUBTOTAL,AGGREGATEand conditional formatting, in Excel for Microsoft 365, 2021, 2019 and Excel for Mac.
FILTER,UNIQUEandSORTneed Microsoft 365 or Excel 2021.SUMPRODUCTdoes the same work in every version. Throughout, column C is the invoice amount, column E is the status, and the ledger runs from row 2 to row 13.
01The Week the Colour Was the Only Control
Twelve open invoices, £67,048.00 in total. Amber means a query is open and the invoice waits. Green means it has already been settled by card. No fill means pay it on Thursday.
That rule has never been written down anywhere in the file. It lives with the credit controller, who was on leave that week.
Three invoices should have been amber on Thursday morning. INV-4471's query was raised by email while nobody was there to paint it, and INV-4473's amber came off in a paste.
INV-4475 went the other way. Nobody had queried it, it arrived under an amber row, and it spent the week looking like a dispute.
Twelve Invoices, Three Meanings, One Column of Colour
Calderstone Joinery's open purchase ledger on Thursday morning, in A2:F15. Column D is the fill that was actually on each row, written out as text because a colour cannot be copied into an article. Column E is the column the ledger did not have: the status, reconstructed afterwards from the credit controller's emails. The two rows at the bottom are the payment run as it happened and the payment run as it should have been, and the gap between them is £18,640.00 paid under query against £9,430.00 held back from a supplier who was owed it.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
02No Formula Can See a Fill
=SUM(C2:C13) returns £67,048.00 whatever colour the rows are. So does =SUMIF(E2:E13,"",C2:C13) and every other conditional function: they read values, and a fill is not a value.
=CELL("color",C2) looks like the exception and is not. It returns 1 when the cell's number format colours negatives, and 0 otherwise. It never mentions the fill.
A colour is formatting, and formatting is not an input to anything. Fills are the one thing on a worksheet that only a human eye can audit.
Scenario: Put =SUM(C2:C13) beside your own colour-coded column, then change one row's fill. Press F9 and watch the total refuse to move. That cell is the control you thought you had.
03Filter by Colour Is a Measurement, Not a Rule
Excel does read fills in one place: Data ▸ Filter ▸ Filter by Colour, which offers every fill present in the column. Pick the amber and only the amber rows stay on screen.
Totals under a filter need the right function. =SUM(C2:C13) still adds the hidden rows; =SUBTOTAL(109,C2:C13) adds only the visible ones, and =AGGREGATE(9,5,C2:C13) does the same.
| Filter by Colour ▸ No Fill | Formula | Returns |
|---|---|---|
| Whole column | =SUM(C2:C13) | £67,048.00 |
| Visible rows only | =SUBTOTAL(109,C2:C13) | £40,917.80 |
| Visible rows only | =AGGREGATE(9,5,C2:C13) | £40,917.80 |
That £40,917.80 is exactly what the run paid. The filter measured the colour correctly; the colour was wrong.
Scenario: Filter your own ledger by the colour that means hold, then put =SUBTOTAL(109,C2:C13) and =SUM(C2:C13) side by side above it. Note the gap, clear the filter, and watch only one of the two cells change.
04How INV-4473 Lost Its Amber
Nobody removed it. An ordinary edit did, and that edit leaves no trace at all.
Pasting carries the source's formatting. A supplier emailed a revised line, somebody copied it from the email into row 4, and the paste brought white with it. One keystroke, and the invoice stopped being on hold.
An inserted row copies the formatting of the row above. Row 6 was added under an amber row, so it arrived amber. INV-4475 was due for payment and looked exactly like a query.
Paste Values (Ctrl+Alt+V, V) leaves the destination's formatting alone, and the Insert Options button that appears beside a new row offers Clear Formatting. Both are easy, and both rely on somebody remembering.
Scenario: Copy any cell from another sheet, paste it over a coloured row in your own ledger, and look at what the fill does. Then undo, paste again with Ctrl+Alt+V ▸ Values, and compare.
Try it in the grid05Conditional Format Colour Is Not Fill Colour
A sheet can be amber two ways: somebody applied a fill, or a conditional formatting rule turned the row amber. They look identical and behave differently.
Filter by Colour sees both. Find and Replace only sees the fill. Search by format, choose the amber from a cell, Find All, and the conditionally formatted rows never appear in the list.
Clearing is the sharper difference. Home ▸ Clear ▸ Clear Formats strips a fill and leaves a conditional rule in place, so the colour comes straight back and looks like Excel ignoring you.
Which one is it? Select the row and open Home ▸ Conditional Formatting ▸ Manage Rules ▸ This Worksheet.
If a rule lists the row in its Applies To range, the colour is an output of that rule. If the list is empty, somebody painted it by hand and there is nothing behind it.
06GET.CELL Works Until Somebody Changes a Colour
The classic workaround is an Excel 4 macro function. Name Manager ▸ New, call it FillCode, and give it =GET.CELL(63,INDIRECT("rc[-1]",FALSE)). Type =FillCode beside a cell and you get its fill as a number.
It has three problems, and the third is the one that costs money.
The workbook has to be saved as .xlsm, because it now contains a macro function. The result is a colour index, so your formula compares against a number nobody can read.
Changing a fill does not trigger a recalculation. Excel recalculates when values change, and a colour is not a value, so FillCode keeps returning yesterday's answer until something else on the sheet forces a rebuild.
Scenario: Build FillCode, put =COUNTIF(F2:F13,40) beside it, then repaint one row and press F9. The count will not move. Press Ctrl+Alt+F9 and watch it jump — that is how stale a colour-driven total can be.
07Write the Status in a Column
Everything above is a workaround for a missing column. Add column E, call it Status, and type what the colour was supposed to mean: Due, Query, Paid.
Three words make the whole ledger countable. =COUNTIF(E2:E13,"Query") returns 3, =SUMIF(E2:E13,"Query",C2:C13) returns £21,405.20, and both of them update themselves.
Keep the vocabulary closed with Data ▸ Data Validation ▸ List pointing at $H$2:$H$4. A typed column invents its own spellings otherwise, and a status nobody chose from a list is a status nobody can total.
Free text has its own failure.
SUMIFSmatchesQueryand does not matchQuerywith a trailing space, so one stray keystroke drops an invoice out of the total silently.
=SUMPRODUCT(--(LEN(E2:E13)<>LEN(TRIM(E2:E13))))returns the number of cells carrying stray spaces. It should return 0, and on an imported ledger it rarely does the first time.
Scenario: Add a Status column to your own sheet, fill it in from the colours you can still see, then put =SORT(UNIQUE(E2:E13)) in an empty cell. Anything in that list you did not mean to type is a status your totals are missing.
08Make the Colour the Output, Not the Input
The amber does not have to go. It has to stop being the only copy of the fact.
Select A2:F13, open Home ▸ Conditional Formatting ▸ New Rule ▸ Use a formula, and enter =$E2="Query" with an amber fill. The row goes amber because the status says Query, and it stops being amber the moment the status changes.
Now the colour cannot drift. A paste that wipes a fill changes nothing, because the rule repaints from column E; an inserted row inherits nothing, because its status cell is empty until somebody fills it in.
Rules can carry logic the eye cannot. =AND($E2="Query",$C2>5000) picks out the large queries, and =OR($E2="Query",$E2="Paid") marks everything the run must skip.
Scenario: Delete every manual fill on your ledger with Home ▸ Clear ▸ Clear Formats, then add the rule =$E2="Query". Change one status to Due and watch the row go white on its own.
09SUMIFS, COUNTIFS and a Total That Agrees
With a status column the payment run is one formula: =SUMIFS(C2:C13,E2:E13,"Due") returns £31,707.80, which is the number Thursday should have produced.
The counts come from the same place. =COUNTIFS(E2:E13,"Due") returns 7, =COUNTIFS(E2:E13,"Query",C2:C13,">5000") returns 2, and =MAX(FILTER(C2:C13,E2:E13="Query")) returns £12,480.00 as the largest invoice on hold.
| Question | Formula | Thursday |
|---|---|---|
| To pay | =SUMIFS(C2:C13,E2:E13,"Due") | £31,707.80 |
| On hold | =SUMIFS(C2:C13,E2:E13,"Query") | £21,405.20 |
| Already settled | =SUMIFS(C2:C13,E2:E13,"Paid") | £13,935.00 |
| Whole ledger | =SUM(C2:C13) | £67,048.00 |
The first three add to the fourth, and that is the control. =LET(d,SUMIFS(C2:C13,E2:E13,"Due"),q,SUMIFS(C2:C13,E2:E13,"Query"),p,SUMIFS(C2:C13,E2:E13,"Paid"),ROUND(d+q+p-SUM(C2:C13),2)) returns 0 when every row has a status Excel recognises.
On Excel 2019 and earlier, =SUMPRODUCT(--(E2:E13="Due"),C2:C13) returns the same £31,707.80, and =INDEX($I$2:$I$4,MATCH(E2,$H$2:$H$4,0)) maps a status to an instruction the way =XLOOKUP(E2,$H$2:$H$4,$I$2:$I$4,"Check this") does on 365.
The instruction beside each row is one question asked once per row. =IF(E2="Query","Hold","Pay") covers two outcomes, =IFS(E2="Query","Hold",E2="Paid","Settled",TRUE,"Pay") covers three, and =IFERROR(INDEX($I$2:$I$4,MATCH(E2,$H$2:$H$4,0)),"Status not recognised") says so out loud when a row holds something else.
Scenario: Put the four totals above in four cells on your own ledger and add the control. If it returns anything but 0, a row holds a status none of your formulas is looking for.
Try it in the grid10What Colour Does Not Survive
A fill is the least portable thing in a workbook, and the payment file leaves the workbook every week.
Save as CSV and every fill is gone. Paste as values into an email, and the colour goes with the fill. Print in black and white, and amber and green are two greys.
About one man in twelve cannot separate the amber from the green at all, and no screen reader has ever announced a fill colour.
A status column survives all of it. It exports, it pastes, it prints, it reads aloud, and it still totals.
Scenario: Save a copy of your own coloured ledger as CSV, close it, and open it again. Whatever meaning you cannot reconstruct from the columns is meaning that was never really in the file.
Try it in the grid11Five Checks Before the Payment File Goes Out
- Foot the statuses against the ledger.
=SUMIFS(C2:C13,E2:E13,"Due")+SUMIFS(C2:C13,E2:E13,"Query")+SUMIFS(C2:C13,E2:E13,"Paid")should equal=SUM(C2:C13), which is £67,048.00. - Count what has no status.
=COUNTA(A2:A13)-COUNTIF(E2:E13,"Due")-COUNTIF(E2:E13,"Query")-COUNTIF(E2:E13,"Paid")returns 0 when every invoice has been classified. - Read the vocabulary.
=SORT(UNIQUE(E2:E13))should list three words and no near-misses. - Find the stray spaces.
=SUMPRODUCT(--(LEN(E2:E13)<>LEN(TRIM(E2:E13))))returns 0 on a clean column. - Compare the colour to the column. Filter by Colour on the fill, then
=SUBTOTAL(109,C2:C13)against=SUMIFS(C2:C13,E2:E13,"Due"). A gap means a fill has drifted from the fact behind it.
12Nine Traps
- Colour as the only record. If the fill is deleted the fact is deleted, and nothing in the file remembers it was ever there.
- A pasted cell repainting a row. Paste carries formatting; Ctrl+Alt+V ▸ Values does not.
- An inserted row inheriting a fill. New rows copy the row above, so a blank invoice arrives already on hold.
- Sorting one column on its own. Decline "expand the selection" and the values move while the fills stay where they were.
SUMunder a filter. It adds the rows the filter is hiding.SUBTOTAL(109,…)andAGGREGATE(9,5,…)do not.- Clear Formats against a conditional rule. The fill goes, the rule repaints it, and the sheet looks broken.
GET.CELLin a live total. It does not recalculate when a colour changes, so it reports the colours of some earlier moment.- Two ambers. Two fills a shade apart are one meaning to a reader and two separate filters to Excel.
=CELL("color",C2)as a colour test. It answers a question about number formats and has nothing to say about fills.
13Five Exercises
- Reproduce it. Build the twelve rows, colour three of them amber, and confirm
=SUM(C2:C13)ignores the colour entirely. - Measure the filter. Filter by Colour ▸ No Fill and check
=SUBTOTAL(109,C2:C13)returns £40,917.80 against=SUM(C2:C13)at £67,048.00. - Break a fill. Paste a cell from another sheet over an amber row, then insert a row under one. Note which invoice silently changed side.
- Add the column. Type
Due,QueryorPaidinto E2:E13 and put=SUMIFS(C2:C13,E2:E13,"Due")in a cell. It should return £31,707.80. - Invert the colour. Clear the fills, add the rule
=$E2="Query", and change one status. The row should repaint itself.
What to Take Away
The ledger was not wrong. Twelve invoices, twelve correct amounts, and a total that footed to the penny on a sheet where nothing was broken.
What was missing was a column. Amber meant "a query is open", and that sentence lived in the formatting, where no formula could reach it and two ordinary edits could erase it.
Write the fact in a cell, let a rule paint the colour from the cell, and the colour becomes a report of something rather than the only copy of it.
One column of three words, against £18,640.00 paid under query and a supplier on stop for £9,430.00.