What causes the "spill range is too big" error in Excel?

The error appears when a dynamic array function (FILTER, UNIQUE, SORT, SEQUENCE) contains a full-column reference like A:A or C:C. Excel pre-sizes the spill area before evaluating the formula, so it attempts to reserve output space across all 1,048,576 rows and throws the error rather than proceed. The fix is replacing full-column references with bounded ranges (A2:A50000) or structured Table references like `Revenue[Amount]`.

Does the "spill range is too big" error happen in Google Sheets?

No - Google Sheets uses a lazy evaluation model for dynamic arrays, so FILTER(A:A, B:B="value") evaluates without error even with full-column references. However, bounded ranges (A2:A50000) still perform 3-5x faster on large datasets in Sheets, and if you're building models that will be reviewed in Excel, using bounded ranges from the start prevents the error when someone opens the file there.

What's the difference between #SPILL! "spill range is too big" and #SPILL! "spill range isn't blank"?

"Spill range is too big" means the source reference itself is too large - almost always a full-column reference causing the engine to attempt a 1M-row output area. "Spill range isn't blank" means the output area is a reasonable size but existing cell content is blocking where the results need to land. The fix for "too big" is narrowing the source range; the fix for "isn't blank" is clearing the cells in the intended output area.

Can Excel Table references permanently prevent the spill range is too big error?

Yes. Structured Table references like `Revenue[Amount]` auto-size to the actual row count of the Table and never reference empty rows, so there's no risk of the pre-sizing step attempting to reserve a 1M-row output. As of Excel 365 (July 2026), Table references are the most resilient pattern for dynamic array formulas in models where data volume changes over time.

How do I find all FILTER formulas in a multi-tab model to audit for this error?

Use Ctrl+F, click "Options," set "Look in" to "Formulas," and search for `FILTER(` and `UNIQUE(` separately. For each result, check whether any argument contains a full-column pattern - look for `:A,`, `:B,`, `:C,` etc. without a row number before the comma. On an 8-12 tab model this audit takes under 5 minutes and catches spill range errors before they surface when someone else opens the file.

Excel Spill Range Is Too Big: Causes and Fixes

Why Excel Says the Spill Range Is Too Big

Dynamic array functions introduced in Excel 365 don't evaluate lazily. Before returning a single value, Excel reserves the entire spill range on the grid. When your formula references A:A, it asks Excel to size an output area across all 1,048,576 rows of that sheet. Even if only 400 rows have data, the engine sees a potential 1M-row output and throws the error rather than attempt to place it.

This is a contract change from legacy functions. =SUMIFS('CashFlow'!C:C,'CashFlow'!B:B,">="&Assumptions!$B$3) works fine with full-column references because SUMIFS always returns one cell - no spill area to reserve. =FILTER('CashFlow'!C:C,'CashFlow'!B:B>=Assumptions!$B$3) using the same references triggers the error immediately because FILTER's output size is unknown until evaluation.

The habit transfer is the common trap. Analysts who spent years writing full-column SUMIFS and VLOOKUP formulas simply carried that pattern into FILTER and UNIQUE, and it broke on them.

According to Microsoft's #SPILL! error documentation (updated July 2026): "The spill range is too big" occurs specifically "when the spill range extends to the edge of the worksheet or contains too many cells." Microsoft's separate dynamic array functions documentation notes that spill-capable functions were "designed to work with bounded output ranges" - not entire-column evaluations - precisely because the engine pre-sizes the output before evaluation begins.

Diagnosing Which Formula Triggers the Spill Range Error

Click the cell showing #SPILL!. Excel overlays a blue dotted border showing the ghost cells - where the output would have gone. In a multi-tab model, the formula might live on a Returns tab pulling from a CashFlow tab with 250,000 rows of transaction data, and the ghost overlay makes the scope problem visible immediately.

Three quick checks:

Does the formula contain a full-column reference (any argument ending in :A, :B, :C, etc.)?
Does it use FILTER, UNIQUE, SORT, SORTBY, or SEQUENCE?
Is the formula referencing a sheet with data that extends deep into the column?

If 1 and 2 are both yes, you have the spill range is too big variant. If only 3 is yes (and the source range is bounded), that's a different #SPILL! variant: output blocked by existing cell content, which has a different fix.

How to Fix the Spill Range Is Too Big Error

The right fix depends on why you needed the full-column reference in the first place. Four scenarios, with Excel and Google Sheets equivalents side by side (models get reviewed in both):

Scenario	Excel Fix	Google Sheets Equivalent
FILTER with A:A source range	Replace with `A2:A50000` (or actual data extent)	Same fix; Sheets doesn't throw this error but bounded refs run 3-5x faster on large datasets
Full-column in a criteria argument	`A2:A50000` or use a Table reference: `Revenue[Date]`	Same; use explicit ranges or named ranges
SEQUENCE generating a date spine	`=SEQUENCE(60,,DATE(2025,1,1),1)` with explicit row count	`=SEQUENCE(60,1,DATE(2025,1,1),1)` - same syntax; note Sheets uses the same date serial system as Excel
You only want a single value from the formula	Prefix with `@`: `=@FILTER(Revenue[SKU],Revenue[Margin]>0.38)`	No `@` operator in Sheets; wrap with `INDEX(...,1,1)` instead

The bounded range fix is the cleanest for most models. If your deal model runs 60-120 periods (5-10 years monthly), you'll never touch 50,000 rows. A2:A50000 gives you 25x+ headroom while staying well under any threshold that triggers the error.

For models where you genuinely don't control the data extent at build time, structured Table references solve it permanently. =FILTER(Revenue[Amount],Revenue[Date]>=Assumptions!$B$3) auto-sizes to the table, never references empty rows, and survives inserts and deletions without recalibration.

Why Full-Column References Made Sense Before Dynamic Arrays

Full-column references were the correct professional practice for a decade. =VLOOKUP(A2,'CashFlow'!A:Z,3,FALSE) was more resilient than a bounded version because bounded ranges break when you insert rows above the start row or add columns. The full-column pattern was genuinely the safer choice.

Dynamic arrays changed the contract. Microsoft documented this shift explicitly when releasing Excel 365 dynamic arrays: spill-capable functions require the engine to pre-size the output before evaluation, which makes full-column source ranges structurally incompatible with how they work. The two patterns - full-column safety and spill output - are in direct tension.

The practical split: keep full-column references in SUMIFS, COUNTIFS, XLOOKUP. Switch to bounded ranges (or Table references) whenever FILTER, UNIQUE, SORT, or SEQUENCE touch that column. That's the division that eliminates the error without sacrificing the robustness full-column refs provide elsewhere in the model.

Preventing the Spill Range Is Too Big Error in Multi-Tab Models

The issue compounds in LBO or three-statement models where FILTER formulas on a Returns tab pull from a P&L tab or transaction data with hundreds of thousands of rows. A single poorly-scoped reference breaks an entire output section.

Table references wherever dynamic arrays touch data. When revenue data lives in a named Excel Table, Revenue[Amount] auto-sizes, never overruns, and won't trigger the error regardless of row count. This is the cleanest long-term fix if you control the data structure.

Define a row buffer constant on your Assumptions tab. A named range DataRows = 50000 lets you write INDIRECT("'CashFlow'!C2:C"&DataRows) across the model. One change to the Assumptions tab resizes all formula ranges simultaneously. Slightly more fragile than Table references but useful in models where the data layout is already locked.

Audit FILTER and UNIQUE before model handoff. Before sending to a bank syndicate or handing off to a client, run Ctrl+F with "Look in: Formulas" and search for :A), :B), :C) patterns. This takes under 2 minutes on an 8-tab model and catches 90% of spill range errors before they surface on someone else's machine.

One non-obvious edge case: Google Sheets handles FILTER with full-column references without throwing this error (its array engine is lazy rather than pre-sizing). Formulas a colleague built in Sheets may work fine there, then break the moment you open the ported file in Excel for bank review. Bounded ranges are safer to use in both environments regardless.

ModelMonkey writes refreshed data back to sheets using bounded ranges by default - when it places a dataset on your CashFlow tab, it targets a specific range like 'CashFlow'!B2:G2501 rather than writing to full columns. Downstream FILTER formulas that reference that exact bounded range won't trigger the spill-range-too-big error when the file opens in Excel.

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