#SPILL! Error in Excel — Every Cause, Every Fix
The #SPILL! error is unique to modern Excel: a dynamic-array formula tried to return multiple values, but something is blocking the space it needs. Here's every reason it happens and how to unblock the spill.
What it means: A dynamic-array formula (like FILTER, SORT, UNIQUE, SEQUENCE, or a spilled arithmetic operation) needs to fill multiple cells with results — but one or more of those destination cells is unavailable. Excel returns #SPILL! in the anchor cell instead.
What the #SPILL! error actually means
Dynamic arrays are a feature added in Excel 365 (and Excel 2021+). Instead of one formula returning one value, a single formula can return an entire array of values that "spills" into adjacent cells. When you write =SORT(A2:A100), Excel fills 99 cells with sorted results — you type the formula in one cell and 99 cells fill automatically.
This works beautifully until Excel tries to write into a cell that already contains something. The dynamic-array model is strict: every cell in the spill range must be empty. If even one cell is blocked, the entire formula fails with #SPILL!.
#SPILL! is mostly about finding the car.
The 6 root causes of #SPILL!
A blocking cell contains data
The most common cause. Your formula would spill into rows 2 through 100, but cell B47 has a leftover value in it — even just a space character. Excel shows the little blue dashed border indicating the desired spill range, and you can see the offending cell inside it.
Merged cells inside the spill range
Merged cells and dynamic arrays don't mix. If any cell in the destination range is part of a merged group, the formula returns #SPILL!. This is a hard rule — even unmerging and re-merging elsewhere in the sheet doesn't help. The specific cells in the spill path must not be merged.
Spill range extends past the worksheet edge
Excel worksheets have finite dimensions (1,048,576 rows and 16,384 columns). If your formula would spill into non-existent cells — for example =SEQUENCE(2000000) or a formula anchored too close to the sheet's edge — you get #SPILL! with the reason "extends beyond the worksheet edge."
Volatile or unpredictable output size
Functions whose output size can change between calculations — RAND, RANDBETWEEN, or formulas that depend on live external data — can trigger #SPILL! because Excel can't guarantee the spill range will remain valid across recalculations. Wrapping them in dynamic-array patterns often triggers this.
The formula is inside an Excel Table
Traditional Excel Tables (created with Ctrl+T) don't support spilling formulas. If you drop a FILTER or UNIQUE formula inside a Table column, you'll get #SPILL! — the Table's row-per-cell model conflicts with dynamic arrays. Use the formula outside the Table, or convert the Table back to a range.
Out of memory for very large spills
Rare, but possible on constrained machines: a formula that would produce millions of rows may fail with #SPILL! even when the sheet has room, because Excel can't allocate enough memory. Common with careless whole-column references like =SORT(A:A) processed across large ranges.
5 real-world fix scenarios
You're filtering active customers:
=FILTER(Customers, Customers[Status]="Active")The formula returns #SPILL!. Clicking into the cell shows a dashed blue border around the intended spill area — one of those cells has a value.
Find and clear the blocking cell. Excel highlights the dashed spill boundary — scan inside it for any non-empty cell. Delete that cell's contents. The spill will fill immediately.
Quick trick: select the dashed spill range and press Delete. This clears everything inside — obviously only do this if you're sure there's nothing important there.
You added SORT to a sheet with formatted headers:
Result: #SPILL!. The spill destination overlaps merged cells from a decorative header row.
Unmerge the offending cells:
- Select the entire spill range shown by the dashed border.
- Home → Merge & Center dropdown → Unmerge Cells.
- Re-run the formula. Rebuild any needed visual formatting using cell borders instead of merges.
You want unique values from column A but wrote:
=UNIQUE(A:A)This tries to process 1,048,576 rows. Excel may return #SPILL! from memory pressure or overlap with unrelated content further down the sheet.
Use a proper range or the trim-reference operator:
=UNIQUE(A2:A10000)Or in Excel 365's newer versions, use the trim-reference operator to skip trailing empties: =UNIQUE(A2:.A10000). Or wrap in a Table and reference the column: =UNIQUE(Sales[Region]).
You dropped a dynamic-array formula into a Table column:
=SORT(Sales[Amount])Tables don't support spilling formulas — they enforce one-formula-per-row. Result: #SPILL!.
Move the formula to an empty area outside the Table:
- Click any cell in a column to the right of the Table (or below it).
- Enter the same formula:
=SORT(Sales[Amount]). - The spill will populate normally.
Or, if you don't need Table features anymore, convert to a range: Table Design → Convert to Range.
You entered a formula in A1048570:
The formula needs 100 rows to spill but there are only 7 rows remaining below the anchor. Result: #SPILL!.
Move the formula higher up the sheet:
=SEQUENCE(100)Placed anywhere with 100+ rows below it, the formula spills without error. Dynamic-array formulas should generally live at the top of a section, not the bottom.
Prevention checklist
- Keep spill anchors in clean areas — designate specific zones of your sheet for dynamic-array output.
- Avoid merged cells in any area a spill might reach. Use cell borders and formatting instead for visual grouping.
- Prefer bounded ranges over whole columns —
A2:A10000is safer thanA:Afor spilling formulas. - Understand the blue dashed border — when you see
#SPILL!, click the cell and the border shows exactly where the spill wanted to go. The blocker is always inside that box. - Don't put dynamic arrays inside Tables — they conflict architecturally. Use Tables for input, dynamic arrays for output.
- Use the @ operator when you don't want spilling —
=@SORT(A2:A100)tells Excel to return a single value (the first sorted item) instead of the whole array. - Watch for pasted-over spill ranges — pasting values on top of a spilled formula's output breaks it. The formula stays, the spill fails.
Version compatibility
The #SPILL! error only exists in versions of Excel that support dynamic arrays. On older versions, formulas like FILTER and SORT don't exist at all, so this error can't be produced.
Google Sheets has its own array formula system with different error messages. Excel 2019 and 2016 don't have dynamic arrays at all, so #SPILL! is impossible there.
Download the practice workbook
Every scenario above laid out as broken/fixed pairs, plus a "spill diagnostic" sheet that shows what the dashed blue border looks like and how to hunt down blockers.
Related errors
Frequently asked questions
Why did my spill formula suddenly start returning #SPILL! when it worked yesterday?
Something changed in the destination. Someone typed a value into one of the cells, pasted data, added a merged range, or resized the sheet. Click the formula cell — the blue dashed border shows the intended spill area. The blocker is inside that box.
Can I stop a formula from spilling?
Yes. Prefix the formula with the @ operator: =@FILTER(...) returns only the first result instead of the full array. This is called "implicit intersection" and mimics pre-2019 Excel behavior. Use it when you actually want one value and are copying the formula down manually.
Why doesn't Excel just skip the blocked cell and fill the rest?
Dynamic arrays are all-or-nothing by design. Excel guarantees the entire spill range is a coherent single output, not a patchwork. Allowing partial spills would break formulas that reference the spill range with the # operator (like =SUM(A2#)). Better to fail loudly than produce misleading partial output.
How do I find the exact cell that's blocking the spill?
Three approaches: (1) Click the #SPILL! cell — a small yellow icon appears to the left of the formula bar; click it for a menu that includes "Select Obstructing Cells." (2) Look for the dashed blue rectangle around the intended spill area — visually scan for any non-empty cell. (3) Select the whole spill range and press Ctrl+G → Special → Constants to jump to non-empty cells.
Does #SPILL! affect the surrounding cells or just the anchor?
Only the anchor cell shows #SPILL!. The other cells that would have been part of the spill remain unchanged (whatever they already contained). Once you clear the blocker, the anchor's formula fills the whole range in one shot.
Why can't I use dynamic arrays inside an Excel Table?
Excel Tables enforce a "one formula per row" model — every cell in a formula column has its own copy of the formula. Dynamic arrays require a single formula that fills many cells at once. The two models conflict, so Excel doesn't allow spilling inside Tables. Place dynamic arrays in ordinary cells outside the Table.
Does #SPILL! also apply to array-entered CSE formulas?
No. Legacy array formulas entered with Ctrl+Shift+Enter use a different mechanism and won't return #SPILL!. They'll return the array within the pre-selected range or truncate silently. Dynamic arrays are the modern replacement — cleaner behavior at the cost of the spill restriction.
Get instant spill diagnostics — with the Sheets & Cells AI Add-in
Our Excel Add-in identifies the exact blocking cell in any #SPILL! error, explains why the block occurred, and offers one-click fixes. All inside Excel — no formula bar detective work.