How to fix the #SPILL! error in Excel
What you see: a formula that should return multiple results —
FILTER, UNIQUE, SORT, SEQUENCE — shows #SPILL! in its cell instead of
filling the neighboring cells. Excel is telling you one thing: the formula
worked, but something is standing where its results need to go.
The fast answer (most common cause)
Select the #SPILL! cell — Excel draws a dashed border around the
range it wants to fill. Click the warning icon next to the cell and choose
"Select Obstructing Cells". Excel jumps to whatever is blocking
the range; delete or move that entry, and the formula spills instantly.
Cause 1: The spill range isn't blank (most common)
Any value, formula, or even a space character inside the intended spill range blocks the whole result.
- Select the
#SPILL!cell — the dashed border marks the intended range. - Click the error alert icon and choose Select Obstructing Cells.
- Delete or move the blocking entry. The formula fills the range automatically — no re-entry needed.
Cause 2: Merged cells inside the range
Merged cells always block a spill — even if they look empty.
- Select the merged cells inside the dashed range.
- Go to Home > Merge & Center > Unmerge Cells.
- If you must keep the merge, move the formula to a merge-free area instead.
Cause 3: The spill runs off the edge of the worksheet
Your formula returns more results than remaining rows or columns — classically
=VLOOKUP(A:A,A:C,2,FALSE) placed near the sheet's right edge (this is
Microsoft's own example). Fix by referencing only what you need:
- Use a bounded range:
=VLOOKUP(A2:A7,A:C,2,FALSE)and fill down, or - Use implicit intersection:
=VLOOKUP(@A:A,A:C,2,FALSE)— the@keeps one result per row.
Cause 4: The formula lives inside an Excel table
Dynamic-array formulas don't spill inside a formatted table.
- Move the formula outside the table, or
- Select any cell in the table, go to Table Design > Tools > Convert to Range, then re-enter the formula.
Cause 5: Out of memory or indeterminate size
Very large arrays can exhaust memory ("Please try referencing a smaller array or range"), and formulas built on RAND/RANDARRAY have no fixed size for Excel to reserve. Reference smaller ranges, or replace volatile functions with static values once generated.
You'll know it worked when
The dashed range fills with values, the error icon disappears, and FILTER / UNIQUE results update normally when the source data changes.
Still broken?
Post the exact formula and a screenshot of the dashed range on the Microsoft Answers Excel forum — the spill-range border usually makes the blocker obvious to a second pair of eyes.
Structured Excel courses that cover dynamic arrays
If FILTER/SORT/UNIQUE still feel like magic, a short focused course beats patchy YouTube. The comparison tool below lists current options.
Compare courses →