The fix is almost always structural. This article covers the 5 most common causes in financial models, with the exact fix for each - and a note on how Google Sheets handles the equivalent situation (it usually doesn't error; it silently overwrites, which is often worse).
The 5 Causes at a Glance
| Cause | Sub-message Excel Shows | Common FP&A Context |
|---|---|---|
| Value blocking the spill range | "A value in the spill range isn't blank" | Returns tab, FCFF model |
| Formula inside a Ctrl+T Table | "Spilling into a table isn't supported" | Structured input tables |
| Volatile size argument | "Spill range is indeterminate" | Rolling period models |
| Cross-workbook dynamic array | "Reference isn't valid" | Linked workbook packages |
| Migration from pre-365 file | Formula collapses after @ removed | Converted three-statement models |
Cause 1: A Value Is Blocking the Spill Range (#SPILL! Error Fix)
Your FILTER formula sits in B3 and wants to write 15 results down through B17, but B11 has a hardcoded value someone typed during last quarter's LBO review. Excel refuses to overwrite it.
This is the most common cause in financial models. Your Returns Analysis tab pulls scenario rows with something like:
=FILTER('LBO Model'!C:H, 'LBO Model'!B:B=Assumptions!$B$2)
An associate pastes a manual "4.2x" two rows into the output range and the whole formula dies.
The fix: Click the #SPILL! cell. Excel draws a blue dashed border around the intended spill range and highlights the blocking cell. Clear it. If that value needs to stay, move manual overrides to a dedicated Overrides tab and build per-row conditional logic:
=IF(Overrides!$B11<>"",
Overrides!$B11,
INDEX(FILTER('LBO Model'!C:H,'LBO Model'!B:B=Assumptions!$B$2), ROW()-ROW($B$3)+1, 1)
)
Dynamic array for the base case, conditional override per row. Keeps the spill range permanently clear.
In Google Sheets: FILTER and SORT don't throw #SPILL! - they silently overwrite any values in their output path. A hardcoded "4.2x" in your Returns tab disappears without a trace. The structural fix is identical: keep manual overrides in a separate range and reference them conditionally. Use ARRAYFORMULA with IF to build the per-row logic rather than relying on a clean output path.
Cause 2: #SPILL! Error When the Formula Is Inside an Excel Table
Ctrl+T structured tables and dynamic arrays are fundamentally incompatible. According to Microsoft's official Excel support documentation, "spilling into a table isn't supported" - tables expect a fixed column structure, and a formula that returns a variable-length result breaks that contract entirely.
This hits financial models when someone formats the Assumptions tab as a table for auto-fill and structured reference benefits, then tries to generate a dynamic period header row:
=SEQUENCE(1, Assumptions!$B$3)
Inside a table, that throws #SPILL! immediately.
The fix: Convert the table to a plain range first. Select any cell in the table, go to Table Design → Convert to Range. Your dynamic array formula then works normally. If you need both - table features for data entry and spill output for derived calculations - keep them in adjacent but separate regions. The table handles input validation and auto-fill; a normal range next to it handles the spill output.
If you must stay inside the table, pre-calculate your output size and use static arguments. SEQUENCE(1, 5) instead of SEQUENCE(1, COUNTA(Periods[Period])). Less flexible, but stable.
In Google Sheets: Google Sheets has no Ctrl+T structured tables. This specific conflict doesn't exist. If you're migrating an Excel model to Sheets, this cause evaporates on its own - every range in Sheets can receive spill output.
Cause 3: Volatile Size Argument Makes Spill Indeterminate (#SPILL! Error)
SEQUENCE(COUNTA('P&L'!B:B), 1) looks reasonable - count the periods, generate that many rows. But COUNTA is volatile. It recalculates every time anything in the workbook changes. If the count shifts during a multi-formula recalculation chain, Excel can't lock a stable spill size before writing output, and throws #SPILL!.
The same problem occurs with OFFSET or INDIRECT as size arguments. In a quarterly board pack model with 52 columns per year across 8 linked tabs, this produces intermittent #SPILL! errors that appear on some machines and not others - depending on recalculation order.
The fix: Anchor the count to a static assumption cell:
// Unstable - volatile COUNTA as size argument
=SEQUENCE(COUNTA('P&L'!B:B), 1)
// Stable - static cell reference
=SEQUENCE(Assumptions!$B$3, 1)
Where Assumptions!$B$3 holds your hardcoded period count (20 for a 5-year quarterly model). If the count genuinely needs to be dynamic, at minimum bound the COUNTA to a specific range ('P&L'!B2:B100) rather than the full column - whole-column references on a 1,048,576-row grid are both volatile and slow.
In Google Sheets: Google Sheets doesn't surface a "spill range is indeterminate" error for volatile size arguments as of May 2026. SEQUENCE(COUNTA(B:B), 1) typically runs without error - but it triggers constant recalculation and causes visible lag in models over 10,000 rows. The structural fix is the same: put your period count in Assumptions!$B$3 and reference it everywhere instead of computing it on the fly.
Cause 4: Cross-Workbook Dynamic Arrays
Dynamic array formulas that spill across workbooks only function when both files are open simultaneously in the same Excel instance. If your Returns Analysis.xlsx contains:
=FILTER('[LBO Model.xlsx]Outputs'!C:C, '[LBO Model.xlsx]Outputs'!A:A=Assumptions!$B$2)
...and LBO Model.xlsx closes, that formula drops to a static cached value. On next open, if the closed file can't resolve, you get #SPILL! or #REF! depending on the Excel version.
This is a reliability problem for any model you share externally. A bank syndicate DCF package where the recipient doesn't have your source file open will break immediately.
The fix: For shared files, paste-values the dynamic output before sending, or restructure so all data lives in one workbook. For internal use where both files always run together, the reference is stable - document the dependency explicitly with a named range comment or a check cell that validates the source file is open.
For persistent cross-file data pulls, Power Query is more reliable than cross-workbook dynamic arrays. It handles the closed-file case cleanly.
In Google Sheets: Cross-file referencing uses IMPORTRANGE, which doesn't support dynamic array spill in the same sense. IMPORTRANGE returns a static snapshot (refreshed every 30 minutes or on demand) that can be wrapped in FILTER or SORT. The equivalent failure mode is IMPORTRANGE losing permission or timing out - which shows as #REF!, not #SPILL!. Fix: isolate IMPORTRANGE to a dedicated helper column and filter from that range.
Cause 5: Removing the @ Operator Causes Unexpected Spill
When you open an Excel file built before dynamic arrays launched (Excel 365 version 1809, released September 2018), Excel adds an @ implicit intersection operator to formulas that previously returned single values by coincidence. You didn't write it; Excel added it during conversion. The formula still works, but it now returns one value instead of an array.
The problem occurs when you remove @ trying to "restore" the formula, and it suddenly tries to spill 20,800 values into a range that has years of hardcoded entries below it. That's when #SPILL! fires.
The fix: Use Ctrl+F with "Look in: Formulas" and search for @. Evaluate each one before removing it. If the formula genuinely should return multiple values and the @ is a conversion artifact, remove it - but first clear the spill path. If the formula should return a single value (e.g., a scalar lookup), leave the @ in place; it's correct there.
In Google Sheets: The @ implicit intersection operator doesn't exist in Sheets. Functions that return arrays have always returned arrays. This cause is entirely Excel-specific and disappears when moving to Sheets.
Merged Cells: A Sixth Cause
Merged cells blocking a spill range deserve their own treatment - including why unmerging cells sometimes doesn't fully clear the error, how to detect hidden merge conflicts across a 10-tab model, and what the cross-tab reference pattern looks like when merges span headers. That's covered in detail in Excel Merged Cells and Spill Formulas: The Real Fix.
Diagnosing Any #SPILL! Error in Under a Minute
- Click the
#SPILL!cell. The blue dashed border shows the intended spill range. - Read the warning triangle - Microsoft's sub-message tells you exactly which cause you're dealing with.
- Look for what's in the blocked cells: a value, a formula, a table boundary, or a merge.
- Fix the cause, not the symptom. Clearing a blocking cell manually doesn't prevent the next person from pasting there again.
ModelMonkey scans formula errors across all open sheets in real time - #SPILL!, #REF!, #VALUE! - and identifies the specific blocking cell even in cross-tab models where Excel's blue dashed border doesn't reach. If you work across Excel and Google Sheets (where the same underlying tension surfaces differently, as covered above), it flags both environments simultaneously. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.