#SPILL Error in Excel: Causes, Fixes & Sheets Overwrites

In an 8-tab three-statement model, one blocked spill range cascades through every tab that references it. Here's what's causing it and how to fix each variant. And if you're primarily in Google Sheets: the same problem wears a different mask. No error, just silent data corruption — which is genuinely worse.

Why the #SPILL Error in Excel Breaks Multi-Tab Models

A spill range is the block of cells a dynamic array formula wants to occupy. =UNIQUE('P&L'!B:B) doesn't output to one cell — it expands downward to cover every unique value it finds. If your Assumptions tab has text, a formula result, or even a stray space in any destination cell, the whole formula returns #SPILL! instead.

The multi-tab problem follows from the spill range operator. =SUMIFS('P&L'!C:C, 'P&L'!B:B, Assumptions!A2#) means "use everything in the spill range starting at A2, whatever size it is." That's powerful when it works. When A2 returns #SPILL!, every downstream formula referencing A2# inherits that error immediately and completely. Your $18.3M NPV line item becomes an error before you've touched the Returns tab.

5 Causes of the #SPILL Error in Excel

Click the cell showing #SPILL! and look at the blue dashed border — that outlines the range Excel is trying to occupy. Any non-empty cell inside that border is blocking it.

1. Non-Empty Cell in the Spill Range

The most common cause, and the trickiest to fix permanently. You clear what you think is the blocking cell, the formula resolves, then it breaks again on the next recalculation. That blocking cell is probably fed by a formula from another tab — ='Headcount'!D47 pulling a value into your spill zone will refill the cell every time the sheet recalculates.

Fix: Find the source formula feeding the blocking cell and reroute it, or move your spill formula to a range that's genuinely unoccupied.

2. Formula Inside an Excel Table

As Microsoft's official #SPILL! documentation states: "Dynamic array formulas are not supported inside Excel tables." (support.microsoft.com, verified April 2026.) Tables use structured references, which are incompatible with the spill model at a fundamental level.

Fix: Move the formula outside the table, or convert the table to a plain range via Table Design → Convert to Range.

3. Volatile Array Too Large for Available Space

Your formula tries to spill 47 rows of Engineering headcount data from the Headcount tab but hits a named range anchor or a static input block 30 rows down. Excel won't partially spill — it errors on the whole formula.

Fix: Free up rows below, or use INDEX to cap the output: =INDEX(UNIQUE('Headcount'!B$2:B$500), SEQUENCE(30)).

4. Spill Range Extends Beyond the Worksheet

Happens with formulas that calculate an output larger than the sheet grid. Excel's ceiling is 1,048,576 rows × 16,384 columns per sheet. Try to spill past row 1,048,576 and you get #SPILL! regardless of what's below.

Fix: Cap output with =TAKE(formula, n) or bound the sequence: =SEQUENCE(MIN(ROWS('P&L'!B:B), 1000)).

5. Spill Range Contains a Merged Cell

Merged cells block spill ranges exactly like data does, and they're easy to miss because they look empty. To confirm: select the blue-bordered spill zone, then Home → Find & Select → Go To Special → Blanks. If it doesn't select every cell in the range, a merged cell is the culprit.

Fix: Unmerge everything in the intended spill area (Home → Merge & Center → Unmerge Cells).

How a #SPILL Error in Excel Cascades Across a Multi-Tab Model

=SUM(Assumptions!A2#) means sum the entire spill range, whatever size it is. That's powerful when it works, and it's why errors propagate immediately and completely when it doesn't.

In a 6+ tab LBO — Assumptions, P&L, Balance Sheet, Cash Flow, FCFF, Returns — a single blocked formula in Assumptions can touch every tab before you've scrolled once. The fix order matters: resolve the root blocking cell first, then force recalculation (Ctrl+Alt+F9 in Windows), then verify each downstream tab sequentially. Fixing tabs out of order just moves the error.

The worst-case scenario is a blocking cell that contains data you actually need. In that case, the spill formula needs to move, not the data.

Excel vs. Google Sheets: How Each Handles Spill Behavior

The Silent Overwrite Problem in Google Sheets

Google Sheets doesn't have a #SPILL error. ARRAYFORMULA just writes over whatever's in its path. If =ARRAYFORMULA(SUMIFS('P&L'!C$2:C$500, 'P&L'!B$2:B$500, Assumptions!A2:A50)) decides it needs more rows than you anticipated, it overwrites whatever's below without a word. No error, no warning, no blue border.

According to Google Workspace documentation on ARRAYFORMULA: the function "allows the display of values returned from an array formula into multiple rows and/or columns." What the documentation doesn't highlight is that it will silently overwrite existing data in the destination range with no conflict prompt. If you handed your boss a board pack where ARRAYFORMULA had quietly eaten your hardcoded terminal value assumptions in row 38, those numbers look fine until someone cross-checks the source — and by then you've distributed the deck.

This is a more dangerous failure mode than #SPILL!, precisely because it doesn't announce itself.

How to Audit Whether ARRAYFORMULA Has Stomped Real Data in Google Sheets

If you're running a multi-tab model in Sheets and something looks off, here's how to verify whether array formulas have overwritten static inputs.

Step 1: Map every ARRAYFORMULA in the workbook.

There's no built-in audit tool. Use Edit → Find (Ctrl+H), search for ARRAYFORMULA, check "Search all sheets." Note every cell that contains one, the direction it spills, and the expected output row count.

Step 2: Check for collisions between formula output and static cells.

For each ARRAYFORMULA, verify the row count matches expectations. If your formula in Assumptions!A2 pulls 47 rows of headcount from the P&L tab, check whether Assumptions!A49, A50, A51 contain what you put there. If A50 has a value that shouldn't exist, the formula expanded and overwrote it in a previous run — or something else wrote there and the formula has since shrunk, leaving orphan data.

A quick count check:

=COUNTA(Assumptions!A2:A60)

Compare that number against the row count you'd expect from the source. A mismatch is a collision flag.

Step 3: Freeze and diff before each model refresh.

The most reliable audit for a quarterly board pack: snapshot the output range before refreshing. Use a dedicated Snapshot tab:

='Assumptions'!A2:A50

After the ARRAYFORMULA refreshes, diff the before/after with:

=ARRAYFORMULA(IF('Assumptions'!A2:A50 <> Snapshot!A2:A50, "CHANGED", ""))

Any CHANGED flag on a cell that should have been a static input is a collision. This is the same logic you'd use to catch circular dependency mutations — you're detecting unexpected writes.

Step 4: Bound every ARRAYFORMULA to an explicit range.

Never use open-ended column references like A:A inside ARRAYFORMULA in a production model. Bound every formula to an explicit row count that matches your maximum expected output:

=ARRAYFORMULA(IFERROR(
  SUMIFS('P&L'!C$2:C$500, 'P&L'!B$2:B$500, Assumptions!A2:A50),
  0
))

This locks the spill to exactly 49 rows and returns 0 for unmatched segments. It won't prevent overwrites below row 50, but it makes the output deterministic and auditable across refreshes.

Step 5: Protect static input cells.

Any hardcoded assumption that an ARRAYFORMULA could reach should be explicitly protected: Data → Protect sheets and ranges → restrict to yourself. If the formula expands into that range, it will throw a permissions error instead of silently overwriting. This is how you engineer the Sheets equivalent of Excel's visible #SPILL! conflict — you force the collision to be noisy.

For models approaching Sheets' practical ceiling of around 10 million cells total, these steps belong in the pre-submission checklist, not the post-mortem.

ModelMonkey can scan your ARRAYFORMULA output zones against your protected and static input ranges, flagging potential collisions before a model refresh. Useful when spill geometry shifts between quarters — which it will any time your headcount rollup or SKU list changes in length.

Try ModelMonkey free for 14 days — it works in both Google Sheets and Excel.