Cellwise Try it free

Excel guide · 3 min read

#SPILL! error in Excel

Quick answer#SPILL! means a formula that returns several values (such as FILTER, UNIQUE, SORT or SEQUENCE) has no empty space to spill into. Click the error cell, then the warning icon, and choose Select Obstructing Cells to see what is in the way. Clear or move those cells and the results appear. Spilling exists in Excel 365 and Excel 2021 and later.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1OrderRegionRepAmount
21041EastKim1200
31042WestRaj860
41043EastLee450
51044NorthAna1310
61045WestRaj990
71046EastKim300

What spilling is

In Excel 365 and 2021, one formula can return a whole list. You type it in a single cell and the results "spill" into the cells below and to the right. A blue border shows the spill range when you click inside it.

This UNIQUE formula returns one row per region. Typed in G2, it fills G2:G4. Only G2 holds the formula; the other cells show greyed-out text in the formula bar.

=UNIQUE(B2:B7)

Result: spills 3 rows × 1 column

East
West
North

UNIQUE spills three rows: East, West and North.

The main cause: the spill range is not blank

If any cell where the results need to go already contains something, Excel refuses to overwrite it. The formula cell shows #SPILL!, and when you select it a dashed border marks the range it wanted to use.

The blocker can be invisible: a space, an empty string "" left by another formula, or white text. Hover over the warning icon and Excel says "Spill range isn't blank".

  1. Click the cell showing #SPILL!.
  2. Click the yellow warning icon next to it and choose Select Obstructing Cells.
  3. Press Delete to clear those cells, or move the formula somewhere with enough empty space.
  4. The results appear as soon as the range is empty.

A working FILTER example

FILTER returns every row that meets a condition, so it needs room for several rows and columns. This one returns all East orders, four columns wide. Leave enough empty rows below it for the list to grow when new orders arrive.

=FILTER(A2:D7,B2:B7="East")

Result: spills 3 rows × 4 columns

1041EastKim1200
1043EastLee450
1046EastKim300

Three East orders spill out: 1041, 1043 and 1046, each with region, rep and amount.

Other causes of #SPILL!

Merged cells: a spill range cannot include merged cells. Select the area and use Home > Merge & Center > Unmerge Cells.

Inside an Excel table: spilling formulas do not work in a table. Put the formula outside the table, or convert the table with Table Design > Convert to Range.

Too big: a reference like =A:A*2 entered below row 1 needs more rows than the sheet has left. Reference just the rows you need, such as A2:A500, or use @A:A to return one value.

Unknown size: volatile functions that keep changing size, such as SEQUENCE(RANDBETWEEN(1,100)), can give #SPILL! because Excel cannot settle the range.

Combine functions and handle empty results

Spilling functions nest neatly. SORT around UNIQUE gives a clean alphabetical list of reps. Give FILTER a third argument for when nothing matches; otherwise an empty result shows #CALC!, which is a different error from #SPILL!.

To refer to a whole spilled list elsewhere, add # after its top cell. For example, if the formula is in G2, =COUNTA(G2#) counts every item, however many there are.

=SORT(UNIQUE(C2:C7))

Result: spills 4 rows × 1 column

Ana
Kim
Lee
Raj

Four reps in alphabetical order: Ana, Kim, Lee, Raj.

Common mistakes

Typing next to a spilled list

Notes or totals typed right under a FILTER block it the moment the list grows. Leave a buffer, or put totals above the formula.

Copying the formula down every row

A spilling formula only needs one cell. Copies in G3, G4 and so on block the original and each other. Delete all but the top one.

Using full-column references

A:A spills a million rows. Use a fixed range or a table column such as Orders[Region].

FAQ

Why did my old formula start showing #SPILL!?

In Excel 365, formulas that used to return a single value now return the whole array. If something sits in the way, you get #SPILL!. Add @ before the range, as in =@A2:A10, to get the old single-value behavior.

Can I delete one cell from a spill range?

No. You can only edit or delete the top cell that holds the formula. Typing into any other cell of the range causes #SPILL!.

Do older versions of Excel spill?

No. Excel 2019 and earlier do not have dynamic arrays. FILTER, UNIQUE, SORT and SEQUENCE show #NAME? there.

Functions used

Related guides

Updated