formulastreak

#SPILL! error: what it means and how to fix it

#SPILL!

A formula returns several values, but Excel can't put them in the cells it needs.

Dynamic-array formulas such as FILTER, UNIQUE, SORT and SEQUENCE return several results that spill into the cells below and to the right. #SPILL! means Excel can't put them there.

Common causes, ranked

  1. 1

    The spill range isn't empty

    Something is typed in one of the cells the result needs (even a space).

    Fix: Clear the cells in the dashed outline. In Excel, the error's menu has Select Obstructing Cells.

  2. 2

    The result would run off the edge of the sheet

    Looking up a whole column returns a million results: =VLOOKUP(A:A,A:C,2,FALSE) in E2.

    Fix: Look up only the values you need, or one row and fill it down.

    Shows the error

    =VLOOKUP(A:A,A:C,2,FALSE)

    Fixed

    =VLOOKUP(A2:A7,A:C,2,FALSE)
  3. 3

    The formula is inside an Excel table

    Spilled formulas aren't supported in Excel tables.

    Fix: Move the formula outside the table, or convert the table to a normal range.

  4. 4

    Merged cells in the way

    A result can't spill into merged cells.

    Fix: Unmerge the cells.

  5. 5

    The size keeps changing

    Volatile functions such as RANDARRAY can change the result's size between calculations.

    Fix: Avoid making the size depend on a volatile function.

Should you hide it?

Don't hide #SPILL!; make room for the result. Microsoft's IFERROR page doesn't list #SPILL! among the errors it handles.

Careful: The spilled values are the answer. Clearing the cells in the way is always the fix.

Excel vs Google Sheets

#SPILL! is an Excel error (Excel 2021, Excel 2024 and Microsoft 365). Google's list of Sheets error values (ERROR.TYPE) doesn't include it. If an array result is blocked in Google Sheets, hover over the error to read Google's own message.

Sources

Practise this in 5 minutes

Fixing #SPILL! is taught in Lesson 38: FILTER (Unit 11, Dynamic arrays), on a live sheet with instant feedback. Pro

Open “FILTER”

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.

Need a quick reference? The formula cheat sheet is free to read and print.