Excel practice › Excel errors › #SPILL! error
#SPILL! error in Excel: what it means and how to fix it
#SPILL! means a formula returns several values and can't lay them out, because a cell in the range it needs isn't empty. Formulas like =UNIQUE(B2:B8) or =FILTER(…) write their results into the cells below and to the right of the one you typed in. That's called spilling, and a spill never overwrites what is already there.
The first fix to try is to empty the cell that's in the way. The formula itself is fine, and it fills the range the moment the range is free.
The causes, in the order worth checking
- Spill range isn't blank: a value, a note or a space sits in a cell the result needs.
- The result grew. The same cause, on a formula that fit yesterday.
- A formula meant for one row was given a whole range.
- Spill range is too big: the result would run off the edge of the sheet.
- The formula is inside an Excel table.
- Merged cells in the range.
- A size Excel can't settle, or a result too large for memory.
In Excel, select the cell that shows #SPILL!: a dashed border outlines the range the result needs, and the warning icon next to the cell names the cause.
1. Spill range isn't blank
The formula in F2 lists each region once. It needs F2, F3 and F4, and F4 holds a note someone left there. In the sheet below, click the marked cell to clear it:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Rep | Amount | Regions | |
| 2 | 7001 | West | Casey | 1200 | #SPILL! | |
| 3 | 7002 | East | Blake | 640 | ||
| 4 | 7003 | West | Avery | 980 | old note | |
| 5 | 7004 | South | Drew | 410 | ||
| 6 | 7005 | East | Blake | 1530 | ||
| 7 | 7006 | West | Avery | 760 | ||
| 8 | 7007 | South | Casey | 890 |
A blocking cell can look empty and still hold a space. Select Obstructing Cells takes you to it.
2. The result grew
A spilled list is as long as the data makes it. A FILTER that returned three orders last week returns six this week, and now reaches a cell that was never in its way before. Here the list of orders over $500 runs into a note in F6. Try both fixes: click the note away, or change 500 to 900 so the list is shorter.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Rep | Amount | Over $500 | |
| 2 | 7001 | West | Casey | 1200 | #SPILL! | |
| 3 | 7002 | East | Blake | 640 | ||
| 4 | 7003 | West | Avery | 980 | ||
| 5 | 7004 | South | Drew | 410 | ||
| 6 | 7005 | East | Blake | 1530 | Checked: Sam | |
| 7 | 7006 | West | Avery | 760 | ||
| 8 | 7007 | South | Casey | 890 |
3. A formula meant for one row was given a whole range
Each customer row should get its own price. The formula in F2 was written with the whole Plan column, E2:E4, as the value to look up. Current versions of Excel answer with one result per cell of that range, three results, and F3 and F4 already hold the prices for their own rows:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Plan | Price | Customer | Plan | Price | |
| 2 | Basic | 19 | Acme Co | Team | #SPILL! | |
| 3 | Team | 49 | Birch LLC | Basic | 19 | |
| 4 | Business | 99 | Cedar Inc | Business | 99 | |
| 5 | Enterprise | 249 |
=VLOOKUP(E2,A2:B5,2,FALSE), which returns 49, and copy it down.4. Spill range is too big
The extreme version of the last cause is a whole column as the lookup value: =VLOOKUP(A:A,A:C,2,FALSE). Excel looks up all 1,048,576 cells of column A and tries to spill as many results. Typed anywhere below row 1, they would run past the bottom of the sheet. Excel's tooltip for this reads "Spill range is too big", and Microsoft's help page calls it "Extends beyond the worksheet's edge". Microsoft lists three fixes:
- Look up only the cells you need:
=VLOOKUP(A2:A7,A:C,2,FALSE). One formula, six results. - Look up one cell and copy the formula down:
=VLOOKUP(A2,A:C,2,FALSE). - Put
@in front of the column, which tells Excel to use the cell on the formula's own row:=VLOOKUP(@A:A,A:C,2,FALSE).
5, 6 and 7. Tables, merged cells, sizes that can't be settled
- Inside an Excel table. Formulas that spill aren't supported in tables. Move the formula to a cell outside the table, or turn the table back into a plain range (Table Design, Convert to Range).
- Merged cells. A result can't spill into a merged cell. Unmerge the cells in the range, or move the formula.
- A size Excel can't settle. A formula whose result changes size each time it's calculated, like
=SEQUENCE(RANDBETWEEN(1,1000)), can't be given a range. - Out of memory. The result is too large. Point the formula at a smaller range.
The sheets on this page have no tables, merged cells or whole-column references, so causes 4 to 7 are described here and not reproduced.
Practice: 3 to fix
Type the formula in the box and press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data.
=SORT(UNIQUE(C2:C8)) lists each rep once, from A to Z. Typed in F2 it shows #SPILL!, because F4 holds an old note. Enter the formula in F2, then clear the cell that's in its way by clicking it.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Rep | Amount | Reps | |
| 2 | 7001 | West | Casey | 1200 | ||
| 3 | 7002 | East | Blake | 640 | ||
| 4 | 7003 | West | Avery | 980 | old note | |
| 5 | 7004 | South | Drew | 410 | ||
| 6 | 7005 | East | Blake | 1530 | ||
| 7 | 7006 | West | Avery | 760 | ||
| 8 | 7007 | South | Casey | 890 |
Show the answer
=SORT(UNIQUE(C2:C8)) gives Avery (4 rows, starting with Avery).
The formula was right before and after: #SPILL! was about the sheet, not the formula. Once F4 is empty, the four names fill F2:F5.
=VLOOKUP(E2:E4,A2:B5,2,FALSE) in F2 shows #SPILL!: it returns three prices, and F3 and F4 hold the formulas for their own rows. In F2, write the formula that returns the price for this row's plan only.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Plan | Price | Customer | Plan | Price | |
| 2 | Basic | 19 | Acme Co | Team | ||
| 3 | Team | 49 | Birch LLC | Basic | 19 | |
| 4 | Business | 99 | Cedar Inc | Business | 99 | |
| 5 | Enterprise | 249 |
Show the answer
=VLOOKUP(E2,A2:B5,2,FALSE) gives 49.
One lookup value gives one result, so nothing spills. Copied down, each row looks up its own plan.
G2 should show the total amount for the region in F2. =FILTER(D2:D8,B2:B8=F2) returns the three West amounts as a list, which runs into the note in G4: #SPILL!. In G2, write a formula that returns the total as one number.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Rep | Amount | Region | Total | |
| 2 | 7001 | West | Casey | 1200 | West | ||
| 3 | 7002 | East | Blake | 640 | |||
| 4 | 7003 | West | Avery | 980 | Updated Friday | ||
| 5 | 7004 | South | Drew | 410 | |||
| 6 | 7005 | East | Blake | 1530 | |||
| 7 | 7006 | West | Avery | 760 | |||
| 8 | 7007 | South | Casey | 890 |
Show the answer
=SUM(FILTER(D2:D8,B2:B8=F2)) gives 2940.
SUM turns the filtered list into one number, and a result of one cell has nothing to spill. =SUMIF(B2:B8,F2,D2:D8) gives the same total.
Questions people ask
What does #SPILL! mean in Excel?
A formula produced more than one value and couldn't place them all. Results that take several cells "spill" into the cells below and to the right, and Excel shows #SPILL! when one of those cells already holds something, or when the range can't exist at all.
How do I fix a #SPILL! error?
Select the cell with the error. The dashed border shows the range the result needs. Empty every cell inside it, or use Select Obstructing Cells from the warning icon to find what is there. If the formula was only meant to return one value, change it: point it at one cell, or wrap the list in SUM, MAX or another function that returns a single number.
What does "Spill range isn't blank" mean?
At least one cell where the results would go has something in it: a value, a formula, a space. Clear it or move it and the formula spills by itself. You don't need to retype the formula.
What does "Spill range is too big" mean?
The result would run past the last row or column of the sheet. It usually comes from a whole column used as a lookup value, as in =VLOOKUP(A:A,A:C,2,FALSE). Look up one cell (A2) or a short range (A2:A7).
Why do I get #SPILL! inside a table?
Excel tables don't support formulas that spill. Put the formula in a cell outside the table, or convert the table to a normal range. A formula that returns one value per row, copied down the column, works in a table.