This article covers the 6 causes, how to fix them fast, and - more usefully - how to structure an 8-tab financial model so the error never appears in the first place.
The 6 #SPILL! Causes in Excel at a Glance
| # | Sub-error message | Common trigger | Quick fix |
|---|---|---|---|
| 1 | Spill range isn't blank | A value or formula is in the output cells | Delete the blocking cell (after checking dependents) |
| 2 | Spill range is too large | Output would exceed 1,048,576 rows | Constrain the filter criteria |
| 3 | Spill range is indeterminate | RAND() or RANDBETWEEN() nested in a dynamic array | Replace volatile with a static seed |
| 4 | Dynamic array inside a Table | FILTER/UNIQUE inside a Ctrl+T Table object | Move formula outside the Table |
| 5 | Spill range has merged cells | Merged header row blocks the output | Unmerge, or use Center Across Selection |
| 6 | Out of resources | Output is too large for available memory | Aggregate before spilling |
Cause 1: Spill Range Isn't Blank
The most common cause. A cell in the output range contains data - a formula, a value, even a space character - and Excel refuses to overwrite it.
The silent danger: If you blindly clear the blocking cell, check what feeds from it first. In a three-statement model where ='P&L'!B47 drives your cash flow opening balance, clearing B47 zeros that reference. Your model ties out to $0 FCF and nobody notices until the board pack is due.
Fix: Click the #SPILL! cell, then use "Select Obstructing Cells" from the error dropdown. Excel highlights exactly what's in the way. Before deleting anything, run Formulas > Trace Dependents to map every formula that references that cell.
Cause 2: Spill Range Is Too Large
The formula's output would push past Excel's grid boundary of 1,048,576 rows by 16,384 columns. A FILTER returning every non-blank row of a large transactions dataset can hit this.
Fix: Constrain the filter criteria so the output stays within bounds. =FILTER('Transactions'!A:F, 'Transactions'!C:C="Q1 2026") returning 18,000 rows is fine. =FILTER('Transactions'!A:F, 'Transactions'!C:C<>"") on a multi-year ledger probably isn't.
Cause 3: Indeterminate Spill Size
Excel can't calculate the output size before evaluating the formula. This almost always means a volatile function like RAND() or RANDBETWEEN() is nested inside a dynamic array - the array dimensions depend on a value that changes every recalculate.
Fix: Replace the volatile function with a static seed value, or restructure to separate the volatile lookup from the array-returning formula.
Cause 4: Dynamic Array Inside an Excel Table
Excel Tables - the structured objects created via Insert > Table or Ctrl+T - don't support dynamic array formulas. A FILTER, UNIQUE, or SORT inside a Table column returns #SPILL! every time. According to Microsoft's dynamic arrays documentation, "dynamic array formulas aren't supported inside Excel tables."
This catches a lot of analysts who built their comp models using structured Table references before 2019 and are now mixing old and new formula styles.
Fix: Move the formula to a normal cell range outside the Table, or convert the Table to a range (Table Design > Convert to Range) before using dynamic arrays.
Note: Google Sheets has no equivalent restriction. FILTER and UNIQUE work inside any formatted range in Sheets. See our guide on dynamic array formulas and spill ranges for a fuller comparison.
Cause 5: Spill Into Merged Cells
Merged cells block the spill range the same way data does. Common in formatted report templates where section headers span multiple columns.
Fix: Unmerge the blocking cells (Home > Merge & Center > Unmerge Cells). For cosmetic column-spanning that doesn't block formulas, use Format Cells > Alignment > Center Across Selection instead.
Cause 6: Out of Resources
The formula output would consume more memory than Excel can allocate. A FILTER returning 500,000 rows with 6 columns generates 3 million cells in one shot - enough to exhaust available memory, especially with other large workbooks open.
Fix: Aggregate before spilling. Instead of pulling all 500k transaction rows into a dynamic array, use SUMIFS to collapse to a single value per period:
=SUMIFS('Transactions'!E:E, 'Transactions'!C:C, ">=" & Assumptions!$B$3,
'Transactions'!C:C, "<=" & Assumptions!$B$4,
'Transactions'!D:D, Returns!$B$7)
If you genuinely need the full row-level dataset, load it into Power Query and summarize there before it hits the sheet.
#SPILL! vs. Google Sheets: Cause-by-Cause
| Cause | Excel | Google Sheets |
|---|---|---|
| Spill range blocked | #SPILL! - refuses to overwrite | Overwrites silently (no warning) |
| Output too large | #SPILL! - grid boundary enforced | Errors only if sheet is completely full |
| Indeterminate size | #SPILL! | Allowed - volatile arrays work fine |
| Inside a Table object | #SPILL! - Tables block dynamic arrays | No equivalent - no Table object |
| Merged cells | #SPILL! | Same behavior |
| Out of memory | #SPILL! | Returns partial output or crashes the tab |
The Sheets behavior on cause 1 is arguably worse. No error surface means wrong numbers that look right.
How to Diagnose #SPILL! in Under 30 Seconds
- Click the #SPILL! cell
- Click the yellow warning diamond at the cell border
- Read the sub-error message - this maps directly to one of the 6 causes above
- For cause 1, click "Select Obstructing Cells" to jump to the blocking cell(s)
- Before clearing anything, run Formulas > Trace Dependents on the blocking cell
Causes 1 through 5 resolve in under a minute once you know the sub-error. Cause 6 takes longer because you need to profile which formula is the memory offender.
How to Structure a Multi-Tab Model So #SPILL! Never Appears
Fixing #SPILL! reactively costs you minutes. Preventing it costs you nothing except a few layout decisions when you're building the model. Here's what that looks like in practice.
Leave a 500-row buffer below every dynamic array formula. If your FILTER on the transaction comps tab currently returns 85 rows, it might return 140 after the next data refresh. A 500-row gap below the formula costs you nothing - Excel ignores blank rows in SUMIFS scans and named ranges - and prevents a growing spill from silently overwriting whatever sits below it.
Never place dynamic array output adjacent to a hardcoded assumption block. Your Assumptions tab is the source of truth for WACC, terminal growth rate, and revenue CAGR. A FILTER result that expands one column sideways into that block overwrites your 8.3% WACC with a company name. Separate by at least one blank column, and better yet, put dynamic outputs on a dedicated tab entirely.
Use a Staging tab for all dynamic array work. For a three-statement LBO or a quarterly board pack, this tab structure prevents almost every spill conflict:
- Assumptions - static inputs only, no dynamic arrays
- P&L / BS / CF - formula-driven, references Assumptions; no dynamic arrays
- FCFF / Returns - calculated outputs, no dynamic arrays
- Staging - all FILTER, SORT, UNIQUE, and SEQUENCE formulas live here
- Data - raw import or paste; never formula-driven
The Staging tab absorbs all the spill variability. If a FILTER on comparable transactions returns 200 rows instead of 50, it spills safely on Staging without touching a single cell in the three-statement model. Your model tabs reference Staging outputs via static lookup formulas, which don't care how far the underlying array has grown.
Reference spill outputs with the # operator. Instead of hardcoding =Staging!A2:A300 in your downstream formulas, reference the live spill as =Staging!A2#. This reference expands and contracts automatically:
// Hardcoded range breaks when the spill grows past row 300
=SUMIFS('P&L'!C:C, Staging!A2:A300, ">=" & Assumptions!$B$3)
// Spill range reference adapts automatically
=SUMIFS('P&L'!C:C, Staging!A2#, ">=" & Assumptions!$B$3)
This also eliminates the habit of pre-allocating ranges "big enough to hold the output" - which breaks silently the moment the dataset grows past the allocation.
Use @ to suppress spill when you only need a single value. If you're pulling one WACC assumption from a lookup table and don't need an array, prefix with @ to force single-value output: =@XLOOKUP(Returns!$B$4, Assumptions!$A:$A, Assumptions!$B:$B). No spill, no spill conflict.
Freeze dynamic output before delivering. For a bank syndicate DCF or a board pack, paste-special (Values only) any FILTER or SORT result before sharing the file. A recipient's older Excel version, missing data connection, or renamed source tab can turn a working dynamic array into #SPILL! the moment they open it. Static values have no dependencies.
When #SPILL! Doesn't Show But Numbers Are Wrong
There's a harder version of this problem. The formula doesn't flag #SPILL! - it spills into cells that look empty but aren't. A "blank" cell containing ="", a formatting artifact, or a zero masked by custom number formatting will block a spill range on some Excel builds and not others, depending on recalculation order.
The model calculates, looks clean, but your $4.2M revenue line on the P&L tab is pulling from a spilled value that's overwritten a prior-period assumption. The numbers are wrong and there's no error to click.
ModelMonkey can identify which cells in a spill range contain invisible non-blank content and trace what downstream formulas they feed - useful when you're 14 tabs into a model and something is off by $2.3M but no error triangle is showing.
Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.