Fix #SPILL! Error in Excel When Dynamic Array Formula Output Is Blocked

The Error

You type a formula like =UNIQUE(A2:A100) or =FILTER(B2:B50, C2:C50="Active") and instead of results, Excel shows:

#SPILL!

The anchor cell gets a dashed border and a warning icon. Hover over it and you'll see "Spill range isn't blank" — or something similar depending on the cause.

Why This Happens

Dynamic array formulas — UNIQUE, FILTER, SORT, SEQUENCE, XLOOKUP with multiple results, and others — automatically spill output into neighboring cells. The formula lives in one cell; the results expand downward or to the right from there.

Something is standing in the way. Here's what usually causes it:

A cell in the spill range has data in it — even a single space character counts
A merged cell sits somewhere in the spill path
The formula is inside an Excel Table (ListObject) — Tables block spilling entirely
The output array is so large it would push past row 1,048,576 or column XFD
The formula references another dynamic array whose size hasn't resolved yet (rare, but it happens)

Step-by-Step Fix

Step 1 — Find the blocker

Click the cell showing #SPILL!. Excel draws a blue dashed border around the intended spill range. Scan that area for anything out of place.

Faster option: click the warning icon and choose Select Obstructing Cells. Excel jumps straight to the problem cells and highlights them for you.

Step 2 — Clear the obstructing data

Found stray data in the spill range? Select those cells and press Delete. The error clears instantly and the formula output fills in.

Watch out for invisible space characters — they're the silent killers here. A cell that looks blank might contain a " " typed by accident. Use Find & Replace (Ctrl+H): put a space in the Find field, leave Replace empty, and run it on the suspect range.

Step 3 — Unmerge cells blocking the spill

Merged cells and dynamic arrays don't get along. If a merged cell is sitting in the spill path:

Select the merged cell(s).
Go to Home → Alignment → Merge & Center → Unmerge Cells.
Reformat or reposition the content, then check whether the formula resolves.

Step 4 — Move the formula outside an Excel Table

Excel Tables (Ctrl+T) simply don't support spilling — the architecture doesn't allow it. If your formula anchor sits inside a Table, you have two ways out:

Cut (Ctrl+X) and paste the formula into any cell outside the Table boundary.
Or convert the whole Table back to a plain range: right-click the Table → Table → Convert to Range.

Either way, once the formula is on normal cells, it spills without complaint.

Step 5 — Fix out-of-bounds spill

A formula like =SEQUENCE(2000) entered in row 47,000 will try to write into row 49,000 — no problem. But enter it in row 47,576 and it hits the sheet limit. Two options:

Move the formula higher up (or further left for column-spilling formulas).
Cap the output with INDEX:

-- Instead of:
=SEQUENCE(2000)

-- Limit to first 500 results:
=INDEX(SEQUENCE(2000), SEQUENCE(500))

Step 6 — Untangle circular dynamic array references

Occasionally two dynamic array formulas reference each other, and neither can resolve first. This creates a size-indeterminate spill that Excel can't handle. Break the dependency with LET:

-- Problematic: FILTER referencing another spilling formula
=FILTER(A2:A100, B2:B100=UNIQUE(C2:C100))

-- Fix: pre-calculate with LET so the inner result is stable
=LET(
  uniqueCategories, UNIQUE(C2:C100),
  FILTER(A2:A100, COUNTIF(uniqueCategories, B2:B100))
)

How to Confirm It's Fixed

The #SPILL! indicator is gone and results populate the cells below or to the right.
Clicking the anchor cell shows a light blue border around the spill range — that's normal and expected.
Clicking any non-anchor cell in the spill range shows the formula greyed out in the formula bar. You can't edit it directly, which confirms spilling is working as designed.
Add a new row to the source data. The spill range expands automatically — no need to re-enter anything.

Prevent #SPILL! Before It Starts

Reserve blank space upfront: If =FILTER() might return 200 rows on a busy day, keep 250 rows below the formula clear. A little buffer saves a lot of interruptions.
Keep Tables for input, plain ranges for output: Tables are great for structured data entry. For dynamic array results, use a plain range nearby.
Watch named ranges with merged cells: A named range that quietly includes a merged cell will block spill even when everything looks fine visually.
Wrap with IFERROR for end-user workbooks: If others use your file, hide edge-case errors gracefully:

=IFERROR(FILTER(A2:A100, B2:B100="Active"), "No results")

Unhide before you debug: Hidden rows and columns still hold data. A hidden cell with a stray value in the spill path causes #SPILL! just as reliably as a visible one.