That's the short answer. The longer one is that understanding spill behavior - not just clearing the error - changes how you build multi-tab models. Dynamic arrays landed in Excel 365 in 2019 and rewrote the rules for multi-value formulas. Before them, returning all cost centers above a spend threshold meant CSE array formulas, helper columns, or a SMALL/IF stack that your successor would spend a week reverse-engineering. Now =FILTER('P&L'!A2:E500, 'P&L'!D2:D500 > Assumptions!$B$5) just spills. The #SPILL! error is what happens when that mechanism hits a wall.
Why #SPILL! Fires: The 5 Common Causes
Microsoft's documentation describes the error as occurring "when a formula returns multiple results, and Excel cannot return the results to the grid." In practice, these are the situations you'll actually hit:
| Cause | What's happening | Fix |
|---|---|---|
| Blocked cells | Non-empty cell in spill path | Clear or relocate blocking content |
| Merged cells | Spill range crosses a merged region | Unmerge, or move the formula |
| Inside a Table | Dynamic arrays can't spill within Excel Tables | Move formula outside the Table boundary |
| Spill too large | Output would exceed sheet limits | Constrain the source range |
| Circular dependency | Spill range overlaps the formula's own inputs | Restructure formula logic |
The merged-cell case catches people off guard. Your spill range looks clean, but if someone merged B3:D3 two years ago for a section header, your =FILTER() hits it silently and dies. Excel won't always make the culprit obvious.
How to Clear It
Click the warning triangle on the formula cell. You'll see "Select Obstructing Cells" - this highlights exactly what's in the way. Delete those cells.
The subtle version: cells that look empty aren't always empty. A formula returning "" registers as occupied. A space character registers as occupied. Pressing Delete on a cell that displays nothing but contains ="" will actually clear it - that step matters. If the obstructing cell is inside a merged range, you either unmerge it or move the formula.
If you're inside an Excel Table and getting #SPILL!, the formula needs to move outside the Table boundary. You can still reference Table columns from outside - =UNIQUE(Table1[Cost Center]) is fine. The formula just can't live inside the Table itself.
The # Operator: What Most Analysts Haven't Found Yet
When a formula spills, Excel creates a spill range - the full set of output cells, sized dynamically. You reference it with # after the top-left cell. If =UNIQUE('P&L'!B2:B500) sits in F2 and returns 23 cost centers, F2# references all 23 cells as a single range. When a 24th cost center appears in the data, F2# extends automatically.
This is where the real leverage is. Drop the UNIQUE formula in F2, then in G2:
=SUMIFS(
'P&L'!D:D,
'P&L'!B:B, F2#,
'P&L'!C:C, ">=" & Assumptions!$B$3,
'P&L'!C:C, "<=" & Assumptions!$B$4
)
G2 spills 23 EBITDA figures - one per cost center, for the date range in Assumptions. Change the date range in Assumptions and every figure recalculates. Add a cost center to the P&L and both the list and the totals extend without you touching a formula. That's the pattern: UNIQUE feeds a #-referenced SUMIFS, and the whole thing becomes self-maintaining.
FILTER: Replace Your Helper-Column Workarounds
If you've built a system of helper columns or SMALL/IF stacks to extract qualifying rows from a P&L or transaction log, FILTER replaces all of it:
=FILTER(
'P&L'!A2:E500,
('P&L'!D2:D500 > Assumptions!$B$5) * ('P&L'!C2:C500 = Assumptions!$B$6),
"No items match"
)
The * between conditions works as AND. The third argument is what spills when nothing qualifies - cleaner than a blank that looks like a formula error. The output is a full table: account, description, period, amount, department. Reference that spill range downstream to build subtotals or feed an executive summary tab.
For a contribution margin analysis by SKU, this pattern works the same way:
=FILTER(
'Revenue'!A2:F800,
('Revenue'!E2:E800 > Assumptions!$C$2) * ('Revenue'!B2:B800 = Assumptions!$C$3),
"No SKUs match criteria"
)
Pull SKUs above your threshold margin for a given channel, dynamically, into a summary that recalculates when margin assumptions shift.
XLOOKUP Spill: Multi-Column Returns Without INDEX/MATCH Stacking
=XLOOKUP() spills when you give it a multi-column return array. For deal-level data in an LBO or DCF model:
=XLOOKUP(
'Returns Analysis'!A2,
'Deal Registry'!A:A,
'Deal Registry'!C:I
)
Returns 7 columns per deal: entry multiple, entry EBITDA, revenue, debt, equity, hold period, IRR target. One formula per deal row, no VLOOKUP column-index gymnastics.
The #SPILL! version of this failure: columns D through J have leftover values from a prior model version. Every cell in the spill path needs to be clear. Run "Select Obstructing Cells" and delete whatever Excel flags.
Chaining Spill Ranges Across Tabs
The real payoff is chaining spill ranges across your model. A UNIQUE in Assumptions feeds a SUMIFS in P&L, which feeds a waterfall in Executive Summary. Each layer references the prior one with #. When cost centers change, the entire chain updates.
A three-statement model where line items auto-extend without manual range adjustments is meaningfully faster to maintain under board-pack pressure. The alternative - hardcoded ranges that someone updates manually - breaks the moment scope changes, which it always does.
The Google Sheets Equivalent
Google Sheets uses =ARRAYFORMULA() to achieve similar output. The mechanics are different: spill is implicit in Excel (any dynamic array formula spills automatically), while Sheets requires the explicit wrapper. The bigger practical gap is the # operator - Sheets has no equivalent for referencing a dynamically-sized output range. You typically reference the entire column or guess the extent, which is less precise in models with variable-length outputs.
If you're running parallel models in both tools, that's the main friction point.
ModelMonkey can build and debug spill-range chains in both Excel and Google Sheets. If you're working through a FILTER-to-SUMIFS structure and #SPILL! is buried somewhere in a dependent formula three tabs downstream, describe what you're trying to build and it'll find the blockage. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.