#SPILL! Error in Excel & Google Sheets: Fix It Fast (2026)

#SPILL! appears when a dynamic array formula (FILTER, SORT, UNIQUE, XLOOKUP, or anything that returns multiple values) can't write its full output because something is in the way. The formula ran correctly; the landing zone is the problem.

Why #SPILL! Shows Up in Multi-Tab Models

Dynamic arrays shipped in Excel 365 in September 2018 and became the default behavior for all Office 365 subscribers in 2022. If you're still on a 2019 perpetual license, you won't see #SPILL! - but you also can't use FILTER, SORT, or UNIQUE, so the tradeoff is clear.

The error is more disruptive in multi-tab models than in single-sheet work because the blocking cell is often 3 tabs away from the formula cell. Your FCFF tab shows #SPILL!, but the cause is a manually entered row label in the Assumptions tab that nobody moved six months ago.

The 5 Causes of #SPILL! (with Fixes)

Cause 1: Something Is Blocking the Output Range

Click the formula cell. Excel draws a ghost blue border around the full intended spill range - blocked cells show as dashed. Click any dashed cell to see its contents. Delete or move whatever is there.

Common culprits in FP&A models: hardcoded figures from a prior model version, a period label someone pasted into the output zone, or a named range anchor that dropped into the middle of where the array wants to write.

Cause 2: The Formula Lives Inside an Excel Table

Microsoft's documentation states plainly: "Spilled array formulas are not supported in Excel Tables." That's a hard constraint, not a bug they're fixing.

The clean pattern: keep raw transaction data in Excel Tables (you want structured references and auto-expand), and do all dynamic array work in normal ranges that reference those Tables:

=FILTER(tbl_Revenue[Amount], tbl_Revenue[Period] >= Assumptions!$B$3)

This formula lives outside tbl_Revenue, filters from it, and spills freely. Enter it in any normal cell and it works.

Cause 3: Merged Cells in the Spill Path

Merged cells and dynamic arrays don't coexist. Excel can't write to a merged cell as part of a spill range. Unmerge everything in the output zone. If you're using merges for visual formatting - spanning headers, centered section labels - switch to Center Across Selection instead. It looks identical and doesn't block spill.

Cause 4: The Array Runs Off the Sheet Edge

Excel caps at 1,048,576 rows. A formula in row 100 that tries to output more than 1,048,476 values will hit the boundary. This is rare with normal financial data but happens with SEQUENCE formulas used as date scaffolding. Constrain the output:

=TAKE(SEQUENCE(1200, 1, DATE(2024, 1, 1), 1), 1048476)

Better yet: size SEQUENCE to exactly the number of periods you need rather than overbuilding.

Cause 5: Volatile Circular Dependencies

Some models build arrays that reference themselves indirectly - an ARRAYFORMULA on the P&L tab that feeds a total used in a condition that filters the same array. This creates an intermittent #SPILL! that appears, clears on recalculate, and returns. The fix is architectural: separate source data from aggregated output.

Diagnosing #SPILL! Quickly in Excel

3 steps:

1. Click the formula cell showing #SPILL!. The tooltip names which of the 5 causes applies.
2. Study the ghost blue border - that's the full intended spill range. Any cell inside it with content is the blocker.
3. Use Ctrl+G → Special → Blanks to find non-obvious content if the blocking data is hard to spot visually.

For multi-tab models, Excel 365 (as of 2026) names the sheet and cell address of the blocking content in the tooltip. Older versions don't, so you'll need to click through the cross-sheet range manually.

Google Sheets: Real Depth on the Equivalent Problem

Google Sheets doesn't use #SPILL!. When an array formula tries to expand into occupied cells, it throws: "Array result was not expanded because it would overwrite data in [cell]."

The error message names the blocking cell directly - which actually makes diagnosis faster than Excel's ghost-range approach. But there are two distinct Sheets errors that look similar, and they have opposite solutions.

#SPILL! vs #REF! in Google Sheets: Don't Confuse Them

"Array result was not expanded" means the formula ran correctly but the output zone is occupied. The formula is fine. Fix the destination.

#REF! means the formula itself references a range that doesn't exist - a deleted sheet, a renamed tab, an out-of-bounds column reference. Fix the formula, not the output zone.

If the error disappears when you delete the formula and retype it from scratch, then reappears immediately, it's #REF!. If it works until the output grows past row X, it's the spill block. (For more on array errors specific to Sheets, see array_literal error in Google Sheets.)

When FILTER Output Collides With Existing Data

The most common scenario in multi-tab FP&A models. Your FCFF tab pulls filtered transaction data from a Revenue tab:

=FILTER('Revenue'!A2:E2000, 'Revenue'!D2:D2000 >= Assumptions!$B$3)

You enter this in FCFF!B8. Revenue is $4.2M across 340 transactions; the formula currently outputs 118 rows, so the spill ends at B125. Model is clean. Next quarter the dataset grows to 209 rows. The spill tries to reach B216 - but you have manually entered segment subtotals in FCFF!B190 through FCFF!B196 from a prior model version. The error fires mid-board-pack: "Array result was not expanded because it would overwrite data in FCFF!B190."

The error message names B190. You have 3 choices:

• If B190:B196 is stale data from an old model version, delete it.
• If those cells contain live data that must stay, move the FILTER formula to a clean column - say FCFF!H8 - and reference the output from there.
• If the output size is permanently unpredictable, route the formula to a Staging tab (more on that below).

Protecting Spill Zones in a Multi-Tab Model

The cleanest pattern for an 8-tab linked model: route all dynamic array formulas to a dedicated Staging tab that has no manually entered data below row 1. All other tabs - P&L, Balance Sheet, Cash Flow, FCFF, Returns - reference Staging cells via INDEX/MATCH rather than hosting arrays directly:

=INDEX(Staging!$C$2:$C$500, MATCH(Assumptions!$B$3 & Assumptions!$C$3, Staging!$A$2:$A$500 & Staging!$B$2:$B$500, 0))

Your structured model tabs stay clean. The spill zone in Staging expands freely without touching hardcoded labels, section headers, or manually entered assumptions.

If you need an array inline - say a FILTER feeding a contribution margin table directly on the P&L tab - reserve at least 200 empty rows below the formula cell before your next hardcoded section. Monitor the current output size with a helper cell:

=ROWS(FILTER('Revenue'!A2:E2000, 'Revenue'!D2:D2000 >= Assumptions!$B$3))

Watch this number. If it's within 30 rows of your buffer, relocate the blocking data before the next close cycle. For very large datasets where recalculating the array twice is costly, count the output column instead:

=COUNTA(FCFF!B8:B400)

For formulas where multiple arrays might each grow unpredictably, IFERROR converts the hard block into a readable warning that survives a board-pack PDF export:

=IFERROR(
  FILTER('Revenue'!A2:E2000, 'Revenue'!D2:D2000 >= Assumptions!$B$3),
  "Spill zone blocked - clear output range"
)

IFERROR won't prevent the block, but it stops model recalculation from halting entirely while you track down the cause.

Fixing It at Scale with AI

When you're maintaining a 10-tab model and tracking down which formula broke which spill zone across tabs, it gets slow. ModelMonkey handles this kind of diagnostic work - describe the error, it reads your sheet structure, identifies blocking cells across tabs, and proposes the fix. It works in both Google Sheets and Excel.