#SPILL! means a formula returns several values (a list or a table) and Excel has no room to write them: at least one cell in the range the result needs is not empty. =UNIQUE(A2:A6) below needs three cells, C2 to C4, and C4 holds an x. Delete C4 and the list appears.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Unique list | |
| 2 | Apple | #SPILL! | |
| 3 | Pear | ||
| 4 | Apple | x | |
| 5 | Plum | ||
| 6 | Pear |
#SPILL! The result needs more cells than are free. Clear the cells in its way.Click C2: the note under the grid says what is wrong. Then click C4 and press Delete. The three products spill into C2:C4, and an outline marks the spill range. Type something into C3 and the error comes back. Excel works the same way: functions that return arrays, such as FILTER, UNIQUE, SORT, SEQUENCE and TEXTSPLIT, only work when their whole spill range is free. They need Excel 2021 or later (TEXTSPLIT: Microsoft 365 or Excel 2024); older versions show #NAME? for them, so #SPILL! never appears there (see UNIQUE for the function itself).
How to fix the #SPILL! error
- Click the cell with
#SPILL!. In Excel, a dashed border shows the range the result wants to fill. - Click the warning icon next to the cell and choose Select Obstructing Cells. Excel selects every cell that is in the way.
- Press Delete, or move those cells somewhere else (cut and paste them).
If the obstructing cells hold data you need, move the formula instead: put it in a column or row where everything below and beside it is empty.
#SPILL! when the cells look empty
The most confusing case: the spill range looks blank, but Excel still says #SPILL!. A cell holding a single space, or a formula that returns an empty string "", is not empty, and it blocks the spill just like a value does.
| A | B | C | |
|---|---|---|---|
| 1 | Numbers | Numbers | |
| 2 | #SPILL! | #SPILL! | |
| 3 | |||
| 4 |
#SPILL! The result needs more cells than are free. Clear the cells in its way.A2 wants A2:A4, and A4 holds a space. C2 wants C2:C3, and C3 holds ="". Click A4 or C3 to see what is there, delete it, and the numbers spill. In Excel, Select Obstructing Cells finds these cells even when nothing is visible in them. White text on a white fill hides the same way.
#SPILL! when one formula spills into another
Two spilling formulas can block each other, or a formula someone typed lower down can sit inside the spill range of the one above.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Dept | IT | Sales | |
| 2 | Ana | IT | #SPILL! | Ben | |
| 3 | Ben | Sales | Dee | ||
| 4 | Cy | IT | 5 | ||
| 5 | Dee | Sales | |||
| 6 | Eve | IT |
#SPILL! The result needs more cells than are free. Clear the cells in its way.The IT list needs three cells, D2:D4, and D4 holds a COUNTA formula. The Sales list in E2 has the two cells it needs, so it works. Move the COUNTA formula to D6 (or any cell below the list) and the IT names spill. Leave room for a list to grow: if a fourth IT employee is added later, the result needs one more cell.
#SPILL! with VLOOKUP and whole columns
A common cause in formulas written for older Excel is a lookup value that is a whole column:
=VLOOKUP(A:A,Prices!A:B,2,FALSE) #SPILL! (one result for every row of the sheet)
=VLOOKUP(A2,Prices!A:B,2,FALSE) one result, fill it down
=VLOOKUP(A2:A100,Prices!A:B,2,FALSE) 100 results that spill
=VLOOKUP(@A:A,Prices!A:B,2,FALSE) one result, the value on the formula's own row
A:A has 1,048,576 cells, so the first formula asks for 1,048,576 results, and from row 2 down there are not enough rows left: the warning menu says the spill range extends beyond the edge of the worksheet. The pattern comes from older Excel, which quietly used only the value on the formula's own row. Excel 365 keeps that behaviour in old workbooks by showing the formula as =VLOOKUP(@A:A,...), but the same formula typed fresh spills the whole column. The same happens with =A:A*2 and any other formula that does arithmetic on a whole column. Use one cell and fill down, a range of the real size, or @. More on the lookup itself is on the VLOOKUP page.
One result per row instead of a spill
A formula that does arithmetic on a range spills too. That is often what you want, and sometimes you want one formula per row instead.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | Spilled +10% | Filled +10% |
| 2 | Jan | 100 | 110 | 110 |
| 3 | Feb | 120 | 132 | 132 |
| 4 | Mar | 90 | 99 | 99 |
| 5 | Apr | 140 | 154 | 154 |
Both columns show 110, 132, 99 and 154. C2 holds one formula, and C3:C5 are its spill: click C3 and the note under the grid says it is spilled from C2. Type into C4 and C2 turns into #SPILL!. D2:D5 are four separate formulas, so each cell can be changed on its own and nothing can block them. Use the second form when people will type over single results.
#SPILL! in a table, with merged cells, or of unknown size
These causes depend on the workbook, not on the formula:
- Inside an Excel table (Insert > Table): tables do not support spilled results, so a FILTER or UNIQUE in a table column shows
#SPILL!. Put the formula outside the table, or select the table and choose Table Design > Convert to Range. - Merged cells in the spill range: select them and choose Home > Merge & Center > Unmerge Cells, or move the formula.
- Spill range is unknown: the size of the result changes on every recalculation, as in
=SEQUENCE(RANDBETWEEN(1,10)). Excel refuses to spill a result whose size is volatile. Give it a fixed size. - Spill range is too big or extends beyond the worksheet's edge: the result would run past the last row or column. Out of memory: the array is too large to calculate. For all three, reduce the ranges, usually from whole columns to the real data.
Return one value so nothing can block it
When you need only one number from a list, such as how many different products there are, wrap the spilling function in one that returns a single value. A single value never spills, so no cell can get in its way.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Different products | |||
| 2 | Apple | ||||
| 3 | Pear | ||||
| 4 | Apple | x | |||
| 5 | Plum | ||||
| 6 | Pear | ||||
| 7 | Apple |
Your turn: E2 should say how many different products are in A2:A7. A plain =UNIQUE(A2:A7) would spill into the x in E4. Write one formula in E2 that returns the count.
COUNTA counts the values UNIQUE returns and gives back one number. The same idea works with =INDEX(SORT(A2:A7),1) for the first value of a sorted list, =INDEX(FILTER(...),1) for the first match, or =SUM(FILTER(...)) for a total.
Frequently Asked Questions
What does #SPILL! mean in Excel?
A formula returned more than one value (a dynamic array) and Excel could not write them into the cells below or beside it, because at least one of those cells is not empty, is merged, or is inside a table. Clear or move what is in the way and the results appear.
Why do I get #SPILL! when the cells look empty?
A cell that looks empty can still hold a space, a formula that returns "", or text formatted in white. Any of them blocks the spill. Click the warning icon next to the error and choose Select Obstructing Cells, then press Delete.
How do I fix #SPILL! with VLOOKUP?
The lookup value is a whole column or range, such as =VLOOKUP(A:A,D:E,2,FALSE), so Excel tries to return one result per row of the sheet. Use one cell and fill down, =VLOOKUP(A2,D:E,2,FALSE), or a range of the actual size, =VLOOKUP(A2:A100,D:E,2,FALSE).
How do I stop a formula from spilling in Excel?
Make it return one value. Put @ in front of a range to take only the value on the formula's own row (=@A2:A10*2), or wrap the result in a function that returns one value, such as =COUNTA(UNIQUE(A2:A10)) or =INDEX(SORT(A2:A10),1).
Can a spilled formula go inside an Excel table?
No. A formula that spills shows #SPILL! inside a table. Put it in a cell outside the table, or convert the table to a normal range with Table Design > Convert to Range.
