#SPILL! in Excel: what it means and how to fix it
#SPILL! means a formula wants to fill more cells than are free. You'll see it with UNIQUE, SORT and FILTER.
Try the first exercises for freeIn the lesson The UNIQUE function
What does #SPILL! mean?
#SPILL! means that a formula wants to fill more cells than are free. Formulas such as UNIQUE, SORT, FILTER and SEQUENCE put their result in the cell itself and in the cells below or next to it. If there's already something there, Excel stops.
It can be something small: a space in a cell that looks empty, or merged cells. Click the cell with the error: Excel shows with a dotted line where the result should go.
When do you get #SPILL!?
- You type =UNIQUE(A2:A50) in a cell with data already below it.
- There's a space or an old value in a cell where the result should go.
- There are merged cells in the way.
- You put a spilling formula in an Excel table: spilling doesn't work there.
How to fix #SPILL!, step by step
This is how to fix the error:
- Click the cell with #SPILL!: a dotted line shows where the result should go.
- See which cells inside that line aren't empty. Click the yellow exclamation mark and choose Select Obstructing Cells: Excel selects them for you.
- Empty those cells, or move your formula to a place with enough free cells.
- As soon as the cells are free, the result appears straight away.
Examples
A cell in the way
C2 contains =UNIQUE(A2:A5). UNIQUE wants to fill three cells, but C3 still says old.
Cleared
C2 contains =UNIQUE(A2:A5). With the cells below it empty, the list simply spills over: Leeds, Bristol and Leicester.
Where it often goes wrong
- Merged cells in the way give the same error.
- A spilling formula doesn't work inside an Excel table: put it next to the table.
Practise this in the lesson The UNIQUE function. The first exercises are free.
Frequently asked questions
Can I change a spilled result?
Not cell by cell. If you want to change the values themselves, copy the result and paste it as values.