What Dynamic Arrays Changed
Before Microsoft 365's dynamic array engine (rolled out to general availability in January 2020), Excel formulas returned exactly one value per cell. VLOOKUP gave you one result. SUMIFS gave you one number. Every formula was implicitly contained to its cell.
Dynamic arrays broke that contract. FILTER can return 500 rows. UNIQUE returns an unpredictable number of distinct values. SEQUENCE generates a grid. Excel needs a contiguous block of empty cells - the "spill range" - to land those results. When it can't, you get #SPILL! instead.
According to Microsoft's official documentation, "a spill range is the range of cells that a dynamic array formula populates with results." If any cell in that range contains anything - a value, a formula, a space character, a merged border - the formula throws #SPILL!.
The 5 Causes (and Which One Is Probably Yours)
1. Data or a formula already sits in the spill range
Most common by far. You drop a FILTER formula in D2, but D15 has a hardcoded number from last quarter's close. Excel won't overwrite it.
Fix: select the formula cell, look at the blue dotted border showing the intended spill range, find the occupied cell, clear it.
2. Merged cells in the spill range
Financial model templates love merged cells for section headers. A SEQUENCE formula generating a 12-month column will hard-stop if any row in that column is merged.
Fix: unmerge. There's no workaround here. Merged cells and dynamic arrays are fundamentally incompatible.
3. The formula lives inside an Excel Table (ListObject)
This one catches people off guard. Excel Tables don't allow formulas to spill outside the Table boundary. A UNIQUE formula in column H of a Table that would return 15 values - but the Table only has 8 rows - throws #SPILL!.
Fix: convert the Table back to a normal range (Table Design → Convert to Range) before using spilling formulas. Or move the formula outside the Table entirely.
4. The spill range is too large
=SEQUENCE(1048576) tries to fill an entire column. Excel refuses. You'll also hit this if a formula tries to spill beyond column XFD or row 1,048,576.
Fix: constrain the formula. If you're generating a date series for a 5-year model, use SEQUENCE(60) not SEQUENCE(ROWS(A:A)).
5. Volatile formula conflict
Less common. RAND() or TODAY() inside a dynamic array formula can occasionally cause a spill conflict on recalculation. Microsoft's documentation describes this as "spill into merged or non-blank cells due to volatile dependencies." Usually transient.
Fix: press Ctrl+Alt+F9 to force a full recalculation. If it persists, the volatility is interacting with something structural.
Diagnosing Fast
Select the cell with #SPILL!. You'll see two things: a blue dotted border showing where Excel wants to spill, and an error dropdown with a specific sub-reason ("Spill range isn't blank", "Spill range has merged cells", etc.).
That sub-reason maps directly to one of the 5 causes above. Read it before you start hunting.
FP&A Scenarios Where This Bites
Pulling variance line items with FILTER
=FILTER('P&L'!B4:N50,'P&L'!B4:B50=Assumptions!$B$3,"No match")
This lands in your variance analysis tab and spills down as many rows as it finds matching the selected cost center. If someone pasted actuals 30 rows below the formula while you were on the Assumptions tab, you get #SPILL! the moment the sheet recalculates.
Solution: give spilling formulas their own dedicated columns with nothing below them. Column D for FILTER output, column E for a helper calc, never mixed.
XLOOKUP across a debt schedule
=XLOOKUP(B3,'Debt Schedule'!$A$4:$A$40,'Debt Schedule'!$D$4:$N$40,"Missing",0,1)
XLOOKUP returning an entire amortization row spills across 11 columns. If any cell to the right has content - a stale comment, a stray apostrophe, a formula that returned "" - #SPILL! kills the whole lookup. This is how people discover that a cell containing only a space character can take down a model.
UNIQUE for distinct cost centers in a budget model
=UNIQUE(FILTER('GL Export'!C:C,'GL Export'!A:A=Assumptions!$B$1))
This builds a dynamic cost center list that updates when the GL data refreshes. If the model template has "placeholder" text a few rows below where this lands, #SPILL! appears every time someone pastes fresh GL data.
Fix: put the UNIQUE formula in a dedicated section and document it. A named range pointing at the spill output (=SpillStart#) lets other tabs reference it cleanly without knowing how long the list is.
The @ Operator When You Don't Want to Spill
If #SPILL! is appearing because you want the formula to return only the first result - not the full array - prefix with @:
=@XLOOKUP(B3,'Debt Schedule'!$A$4:$A$40,'Debt Schedule'!$D$4:$D$40,"Missing")
The @ forces implicit intersection: same behavior as pre-2020 Excel. One result per cell, no spill. This is what you want when building a helper column where each row looks up its own value.
Referencing Spill Output from Another Tab
Once a formula spills correctly, reference the entire output with the # operator:
=SUMIFS('Returns Analysis'!E2#,'Returns Analysis'!D2#,">="&Assumptions!$B$5)
The # after the cell address tells Excel "the whole spill range from this formula." If the spill range grows because underlying data changed, any reference using # grows with it. No OFFSET hacks, no hardcoded row counts.
As of June 2026, the # spill reference operator works in Excel for Microsoft 365 (subscription) and Excel 2021+. It doesn't work in Excel 2019 or earlier, which don't support dynamic arrays at all.
Where ModelMonkey Fits
A #SPILL! error buried in a 12-tab model can take 20 minutes to track down if the blocking cell is 40 rows below the formula and isn't obvious on screen. ModelMonkey's Excel add-in can audit the spill range of a flagged formula and pinpoint which cell is blocking it. It won't restructure your model, but it's faster than manually scanning a column in a 3,000-row GL export.
Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.