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.

Skip to the 3 exercises ↓

The causes, in the order worth checking

  1. Spill range isn't blank: a value, a note or a space sits in a cell the result needs.
  2. The result grew. The same cause, on a formula that fit yesterday.
  3. A formula meant for one row was given a whole range.
  4. Spill range is too big: the result would run off the edge of the sheet.
  5. The formula is inside an Excel table.
  6. Merged cells in the range.
  7. 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:

Live example#SPILL!: an old note in F4
F2fx
ABCDEF
1OrderRegionRepAmountRegions
27001WestCasey1200#SPILL!
37002EastBlake640
47003WestAvery980old note
57004SouthDrew410
67005EastBlake1530
77006WestAvery760
87007SouthCasey890

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.

Live example#SPILL!: six results, room for four
F2fx
ABCDEF
1OrderRegionRepAmountOver $500
27001WestCasey1200#SPILL!
37002EastBlake640
47003WestAvery980
57004SouthDrew410
67005EastBlake1530Checked: Sam
77006WestAvery760
87007SouthCasey890

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:

Live example#SPILL!: VLOOKUP given E2:E4
F2fx
ABCDEF
1PlanPriceCustomerPlanPrice
2Basic19Acme CoTeam#SPILL!
3Team49Birch LLCBasic19
4Business99Cedar IncBusiness99
5Enterprise249

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:

5, 6 and 7. Tables, merged cells, sizes that can't be settled

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.

Exercise 1Something in the way

=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.

F2fx
ABCDEF
1OrderRegionRepAmountReps
27001WestCasey1200
37002EastBlake640
47003WestAvery980old note
57004SouthDrew410
67005EastBlake1530
77006WestAvery760
87007SouthCasey890
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.

Exercise 2One row, one result

=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.

F2fx
ABCDEF
1PlanPriceCustomerPlanPrice
2Basic19Acme CoTeam
3Team49Birch LLCBasic19
4Business99Cedar IncBusiness99
5Enterprise249
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.

Exercise 3A list where one number is wanted

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.

G2fx
ABCDEFG
1OrderRegionRepAmountRegionTotal
27001WestCasey1200West
37002EastBlake640
47003WestAvery980Updated Friday
57004SouthDrew410
67005EastBlake1530
77006WestAvery760
87007SouthCasey890
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.

Keep going

Lesson 10 is nine drills on formulas that spill, on a support-ticket log: UNIQUE, SORT, FILTER with one condition or several, #SPILL! and #CALC!, and the # that points at a whole spilled list. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

FILTER function, with live examplesUNIQUE function, with live examplesSORT function, with live examples#NAME? error in Excel: meaning and fixes#VALUE! error in Excel: meaning and fixesExcel FILTER, SORT, UNIQUE practice (Pro)Excel formula errors, fixed liveExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: How to correct a #SPILL! error, Microsoft Support: #SPILL! error, extends beyond the worksheet's edge, Microsoft Support: Dynamic array formulas and spilled array behavior, Microsoft Support: VLOOKUP function. Last checked November 2026.