#SPILL! in Excel: what it means and how to fix it
#SPILL! means a formula has more than one answer and can't fit them in. Formulas like UNIQUE, FILTER and SORT fill the cells below (and sometimes to the right), and if any of those cells has something in it, Excel stops and shows #SPILL! instead.
A worked example
E2 holds =UNIQUE(B2:B6), which should list Red, Blue and Green in E2 to E4. But someone typed a note in E3:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Team | Teams | ||
| 2 | Ann | Red | #SPILL! | ||
| 3 | Bob | Blue | note | ||
| 4 | Cy | Red | |||
| 5 | Di | Green | |||
| 6 | Ed | Blue |
Click the #SPILL! cell: Excel draws a dashed border round the cells it needs, and the warning sign offers "Select obstructing cells".
The usual causes
Something in the way
Text, a number, or even a space or an empty-looking formula in the cells below. Clear them.
Merged cells
A spill can't go into merged cells. Unmerge them.
Inside a table
Spilling formulas don't work inside an Excel table. Put the formula next to the table instead.
A whole column
=B:B*2 tries to spill a million rows and runs off the bottom of the sheet. Use the rows you need: =B2:B100*2.
When you wanted one answer
Sometimes the spill is a sign the formula isn't finished. FILTER gives a list, but if you wanted the total, wrap it in SUM:
=SUM(FILTER(C2:C9,B2:B9=F1))
Now there's one answer, so nothing to spill. (SUMIF does the same job in older Excel.)
Try it now
=FILTER(C2:C9,B2:B9=G1) lists the scores for the team in G1, but G2 only has room for one answer. In G2, change it to give the total of those scores in one cell.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Team | Score | Team | Red | ||
| 2 | Ann | Red | 55 | Total | |||
| 3 | Bob | Blue | 40 | ||||
| 4 | Cy | Red | 70 | ||||
| 5 | Di | Green | 35 | ||||
| 6 | Ed | Blue | 60 | ||||
| 7 | Fay | Red | 48 | ||||
| 8 | Gus | Green | 82 | ||||
| 9 | Hal | Blue | 51 |