How to fix the #SPILL! error in Excel
In Excel 365 and 2021, formulas like FILTER, UNIQUE, SORT and SEQUENCE return several values that “spill” into the cells below or beside them. #SPILL! means something is in the way.
#SPILL!
Why it happens
Cells in the spill range aren’t empty
Even a space or an empty-looking formula blocks the spill.
Merged cells
Spilled results can’t land in merged cells.
Inside an Excel Table
Dynamic array formulas can’t spill inside a formatted Table.
The result is too big
A formula like =A:A+1 tries to spill a whole column.
How to fix it
Clear the cells below, then re-enter
=UNIQUE(B2:B9) → East, West, North
Need one value only? Wrap it so it doesn’t spill
=INDEX(SORT(F2:F9,1,-1),1) → 150
Examples use this sample sheet of orders:
A
B
C
D
E
F
1
Order
Region
Rep
Units
Price
Revenue
2
1001
East
Maya
4
12
48
3
1002
West
Omar
2
30
60
4
1003
East
Lena
7
12
84
5
1004
North
Maya
1
45
45
6
1005
West
Lena
5
30
150
7
1006
East
Omar
3
18
54
8
1007
North
Lena
6
12
72
9
1008
West
Maya
8
18
144
Tip: Click the #SPILL! cell: Excel outlines the range it wants with a dashed border, so you can see exactly what’s blocking it.
Other Excel errors
- #N/A“Not available”: a lookup couldn’t find what you asked for.
- #VALUE!A formula got the wrong kind of data, usually text where it needed a number.
- #REF!The formula points at a cell that no longer exists.
- #DIV/0!A formula tried to divide by zero or by an empty cell.
- #NAME?Excel doesn’t recognise a word in the formula.
- #NUM!The math is impossible or a number is out of range.