Calderhay Wholesale claims a volume rebate from nine suppliers. For three quarters it claimed from eight. Nothing on the sheet was wrong, nothing was red, and £24,587.50 was never asked for.
The claim sheet is two columns. Column H spills the supplier list out of the purchase ledger, and column I totals each supplier's net purchases beside it.
Column H is one formula that writes as many rows as it needs. Column I was eight formulas, typed once. In January a ninth supplier was added, the list grew by a row, and the column beside it did not.
| Q3 2026 rebate | Figure |
|---|---|
| Earned on purchases | £67,257.75 |
| Claimed from suppliers | £58,724.75 |
| Short by | £8,533.00 |
| Quarters affected | 3 |
| Still inside the claim window | £8,533.00 |
Scope. Spill ranges, the
#spill operator,#SPILL!, the@of implicit intersection, andUNIQUE,SORT,FILTERandSUMIFSin Microsoft 365 and Excel 2021 and later.Excel 2019 and earlier have no dynamic arrays at all. Section 11 says what those builds make of a file written this way.
Throughout,
Linesis the purchase ledger table, column H holds the supplier list and column I the totals beside it.
01One Supplier Fell Off the Bottom of the Claim
Calderhay buys from a short list of suppliers and claims a volume rebate from each one at the end of every quarter. The rates sit in the supply agreements; the purchases sit in a ledger table called Lines.
The claim sheet was built in 2023 with eight suppliers on it. H2 held =SORT(UNIQUE(Lines[Supplier])), which spilled eight names down to H9, and I2 held =SUMIFS(Lines[Net],Lines[Supplier],H2), filled down to I9.
In January 2026 the buying team opened an account with Calderbrook Organics. The list sorted it into third place, every name below it moved down one row, and the list now ended at H10.
Column I still ended at I9. Each of its eight formulas re-read the name that had moved into its row, so all eight totals were right — for the first eight names on a list of nine.
Harlings Dry Goods is the ninth name, and no formula points at it. It had supplied Calderhay for nine years, on the best rebate rate the firm holds, and it was claimed every quarter until the quarter somebody else was added.
Nine Suppliers, Eight Totals and One Rebate Nobody Asked For
Calderhay Wholesale's quarterly rebate claim, in A1:F13. Column B is the cell on the claim sheet that holds each supplier's name, written by one formula in H2 that spills as far down as the ledger needs. Column D is the rebate each supplier's purchases earned under its supply agreement; column E is what was actually claimed. The totals column beside the list ends at I9, so the ninth name has no formula pointing at it, and the three rows at the bottom are the quarter, the two quarters before it and the part still inside the ninety-day claim window.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
02A Spilled List Is One Formula, Not Nine Cells
=SORT(UNIQUE(Lines[Supplier])) lives in one cell. Everything under it is output: Excel writes the answer into as many cells as it needs, and those cells hold no formula of their own.
Click H5 and the formula bar shows the formula greyed out, with H2 as the only cell you can edit. Delete H2 and the whole list goes. That block is the spill range, and the data decides its size.
A column of filled-down formulas is the opposite. Each cell is its own formula, each was put there by a person, and the column is exactly as long as the day somebody dragged it.
Those two things side by side is the fault. One grows on its own and the other needs a person, so they agree only until the data changes.
Scenario: Click a cell below the top of a spilled list in your own workbook. A greyed formula bar means you are inside a spill range; type =ROWS(H2#) in a spare cell to see how many rows it writes today.
03H2# Is the Range That Grows With It
Excel has a reference for the whole spill: the top cell with a # after it. H2# is every row the formula in H2 currently writes — nine rows today, ten tomorrow, without anybody editing anything.
So the totals column should have been one formula as well:
=SUMIFS(Lines[Net],Lines[Supplier],H2#)
That spills its own answer down from I2, one total for each name, however many names there are. The claim total then becomes =SUM(I2#), which reads whatever the column wrote.
| Written as | Rows it covers | What a ninth supplier gets |
|---|---|---|
SUMIFS filled down I2:I9 | Eight, forever | Eight totals, nine names |
=SUMIFS(Lines[Net],Lines[Supplier],H2#) | As many as H2 writes | Nine totals |
=SUM(I2:I9) | Eight, forever | Misses the ninth |
=SUM(I2#) | The whole spill | Includes it |
Scenario: Put =ROWS(H2#) beside =ROWS(I2:I9) on your own sheet. Any time those two disagree, something on the right-hand side is being left out of a total.
04What #SPILL! Means and How to Clear It
#SPILL! is the opposite problem, and it is the loud one. The formula wants to write into cells that are not empty, so rather than overwrite them it writes nothing and returns an error instead.
Click the warning triangle and Excel names the reason. Spill range isn't blank is the common one, with Select Obstructing Cells under it, which jumps the cursor to whatever is in the way — usually a stray space or an old heading.
The other four are worth knowing by name, because each has a different fix.
| What the triangle says | What is actually wrong |
|---|---|
| Spill range isn't blank | A value, a space or a note sits in the output |
| Spill range has merged cells | A dynamic array cannot write into a merged cell at all |
| Spill range is unknown | The size depends on something volatile, such as RANDBETWEEN |
| Spill range is too big | The result runs past the last row or column of the sheet |
| Spill range in table | The formula is inside an Excel Table, which cannot hold a spill |
Scenario: Type anything into the cell directly under a spilled list and watch the whole list collapse to #SPILL!. Press Ctrl+Z, then count the clear rows you actually have beneath it.
05The @ Excel Writes to Stop a Spill
Excel will sometimes stop a formula spilling rather than report #SPILL!, by writing an @ in front of it. =@SORT(UNIQUE(Lines[Supplier])) returns one name instead of a list, and it returns it without an error.
That @ is implicit intersection: take the single value on the same row as the formula and throw the rest away. It is how every version of Excel before 2019 read a range handed to a one-value argument.
It appears when an older workbook is opened in 365, when a formula is pasted into a Table, and whenever somebody copies a formula out of a sheet written on an older build.
A number is the dangerous answer. #SPILL! stops a claim being submitted; @ totals one row, leaves the rest of the list unasked for, and nobody checks a cell that holds a number.
Scenario: Press Ctrl+F, set Look in to Formulas, and search your workbook for @. Delete one and see whether the cell was answering with a list or with a single row of it.
06Spills Do Not Work Inside a Table
An Excel Table manages its own rows: insert one and every formula column fills down into it. A dynamic array manages its own rows too, and the two cannot both be in charge of the same cells.
So a formula that spills, typed into a Table column, returns #SPILL! with Spill range in table as the reason. Excel usually offers the @ instead, which turns the formula into a one-row answer and no error.
The claim sheet is not a Table, which is the only reason H2 works at all. The ledger it reads is a Table, and that is the right way round: Tables for the data, plain cells for anything that spills.
Converting back. Table Design → Convert to Range turns a Table into ordinary cells, and structured references pointing at it become ordinary ranges.
Do that to the sheet holding the spill, never to the sheet holding the data. A ledger that is a Table is what lets
Lines[Supplier]grow when a row is appended.
Scenario: Type =UNIQUE(A2:A20) into a spare column of any Table in your workbook and read the error. Then type the same formula outside the Table and watch it spill.
07Never Read a Spill by Row Number
A sorted list has no fixed addresses. H4 was Carrowdale Bakery for three years and became Calderbrook Organics in January, and nothing in the workbook changed to make that happen.
Any formula pointing into a spill with a row number is a bet that the sort will not move. =H4 and =INDEX(H2#,4) are two ways of making that bet, and both of them lose quietly rather than loudly.
Read it by name instead:
=XLOOKUP("Harlings Dry Goods",H2#,I2#)
That follows the supplier wherever the sort puts it, and returns #N/A if the supplier stops appearing in the ledger at all — which is a question somebody should be asked.
Scenario: Count the cells in your workbook that point into a spilled block with a row number. Replace one with an XLOOKUP against the label column and check the figure does not move today.
08Leave Room Under Anything That Spills
A spill needs its room before it needs it. A nine-row list has to be able to become a ten-row list on the same sheet, which means the cell under H10 has to be empty on the day that happens.
So nothing goes directly beneath a spilled block. No total, no note, no second table. The claim total belongs above the list or in a column of its own, where growth cannot reach it.
A grand total under a dynamic list is the most common #SPILL! in a finance workbook, because under the column is where a total has always gone.
| Where the total goes | What happens when the list grows |
|---|---|
| Directly under the list | The whole list becomes #SPILL! |
| Two rows under the list | Fine until the list grows by two |
| Above the list, in H1 | Never in the way |
A column over, =SUM(I2#) | Never in the way, and reads the whole spill |
Scenario: Move one grand total from under a spilled list into the row above it and change its formula to read the spill with #. The figure should not move; the risk should.
09Three Checks That Catch a Short Column
The claim went out eight times and footed eight times, because every figure on it was right. What was missing was a row, and no total can report a row that was never added to it.
Count the rows on both sides. =ROWS(H2#)-COUNT(I2:I9) is zero while the two agree and non-zero the moment they stop. Park it beside the claim where somebody will read it.
Reconcile to the source. =SUM(Lines[Net])-SUM(I2#) should be zero every quarter. Every pound in the ledger belongs to exactly one supplier, so those are two routes to the same number.
Count the names. =COUNTA(UNIQUE(Lines[Supplier])) reads the ledger rather than the claim sheet, so a supplier appearing for the first time changes it in the week it happens.
Scenario: Put the reconciliation in a cell beside your own summary and format it so that a non-zero result goes red. It is one formula, and it is the one that would have caught this in January.
Try it in the grid10When a Fixed Range Is the Right Answer
A spill is not always what you want. A claim form with eight numbered lines, a report laid out to fit a page, a template somebody types into: all of those need a fixed shape, and a block that grows would break them.
The question is which side decides the size. If the data decides, use a spill and read it with #. If the layout decides, use fixed cells and check that the data still fits inside them.
What cannot happen is one of each. A list that grows on its own, read by a column that does not, is the fault this whole article is about, and it reports a sensible-looking number while it does it.
Scenario: Pick one summary in your workbook and decide out loud which of the two it is. Then make both of its columns match that decision, and put =ROWS(H2#) somewhere to prove it.
11Older Excel, the Web and Shared Files
Spill ranges need Microsoft 365 or Excel 2021. Open this claim sheet in Excel 2019 and UNIQUE, SORT and the # operator are all unknown names, so the list is a column of #NAME? and every total beside it is zero.
Excel for the web spills normally and is a fair test of what a colleague on 365 sees. Google Sheets has its own array behaviour and its own function list, so a round trip through it is not a round trip.
A shared workbook makes the growth somebody else's doing. One person appends a ledger row, the list grows on everybody's screen, and the person who owns the claim sheet is not the person who made it short.
Scenario: Open the workbook in Excel for the web and read the row count of the spill. If it differs from a colleague's desktop, their build is the difference rather than the data.
Try it in the grid12Nine Traps
- A spilled list beside a filled-down column. One side grows by itself and the other does not, and the gap between them is silent.
=SUM(I2:I9)over a dynamic block. The total is right about the rows it was given and says nothing at all about the rest.- A grand total directly under a spill. The list does not grow by a row; it becomes
#SPILL!and the whole thing disappears. - Reading a sorted list by row number.
H4is whichever supplier sorts fourth today, and sorting is what the formula is for. - Treating
@as punctuation. It is a call that throws away everything but the one value on its own row. - Typing a spilling formula into a Table. Tables and dynamic arrays both own their rows, so one of the two has to go.
- Merged cells anywhere in the output. A spill will not write into them at all, whatever else is true about the sheet.
- Assuming
#SPILL!is the only failure. The error is the version of this fault that announces itself. - Sending the workbook to Excel 2019. No spills, no
#, and a column of #NAME? where the suppliers used to be.
13Five Exercises
- Reproduce it. Spill eight names with
=SORT(UNIQUE(A2:A40)), fill aSUMIFSdown eight rows beside it, then add a ninth name that sorts into the middle and read the total. - Fix it with
#. Replace the eight formulas with one=SUMIFS(Lines[Net],Lines[Supplier],H2#), then add the ninth name again and watch the column grow. - Break it on purpose. Type a space into the cell under the list, read the triangle's reason, and use Select Obstructing Cells to find the space.
- Meet the
@. Type=@SORT(UNIQUE(A2:A40))and compare what it returns with the same formula without the@in front of it. - Reconcile it. Write
=SUM(Lines[Net])-SUM(I2#)beside your summary and confirm it reads zero before and after each change above.
What to Take Away
Every figure on every one of those claims was correct. Eight suppliers, eight totals, eight rebates, each one agreeing with the ledger to the penny.
The sheet had nine suppliers on it. One formula had grown to say so and the column beside it had not, and £24,587.50 of rebate went unclaimed across three quarters.
A spill range decides its own size. Anything reading it has to be told to do the same, with # rather than a row number, and anything sitting beneath it has to move.
£8,533.00 of the £24,587.50 is still inside the ninety-day claim window. The rest expired quietly, in a workbook that never once showed an error.