Excel errors / #SPILL!

#SPILL! error in Excel

A formula that returns several values has no room to put them.

In Excel 2021 and Microsoft 365, a formula can return a whole list of results. They "spill" from the cell with the formula into the cells below and beside it. #SPILL! means something is in the way: one of the cells the results need is not empty.

Click the cell with the error and Excel draws a dashed border around the area it wants to fill. Anything inside that border, other than the formula itself, is the obstacle.

Have a formula that shows #SPILL!? Paste it into the formula checker with a few rows of your data. It points at the part that fails and says why.

4 causes of #SPILL!, and how to fix each

  1. A cell in the way

    UNIQUE below needs two cells, C2 and C3, and C3 already holds the word "Notes".

    ABC
    1NameList
    2Ana#SPILL!
    3BenNotes
    4Ana

    In C2: =UNIQUE(A2:A4) → #SPILL!

    How to fix it. Clear or move whatever is in the way. Cells that look empty may hold a space: select the whole spill area and press Delete.

    ABC
    1NameList
    2AnaAna
    3BenBen
    4Ana

    In C2: =UNIQUE(A2:A4) → Ana; Ben

  2. Merged cells in the spill area

    Results cannot spill into merged cells, even empty ones.

    How to fix it. Unmerge the cells (Home > Merge & Center), or move the formula somewhere without merged cells below and beside it.

  3. The formula is inside a table

    Tables made with Ctrl+T do not allow spilling formulas. A dynamic array formula typed into a table column shows #SPILL! whatever is around it.

    How to fix it. Put the formula outside the table, or turn the table back into a normal range (Table Design > Convert to Range).

  4. The result runs off the sheet

    A formula such as =A:A*2, entered anywhere but row 1, tries to return over a million rows and runs past the bottom of the sheet.

    How to fix it. Point the formula at the rows that hold data, such as =A2:A500*2, in place of the whole column.

Returning one value on purpose

To make a formula return a single value and not spill, put @ in front of the range: =@A2:A10 takes only the value on the formula's own row. To take one item from a spilling result, wrap it in INDEX: =INDEX(SORT(A2:A10), 1).

Common questions

How do I fix #SPILL! in Excel?

Click the cell with the error. A dashed border shows where the results need to go. Clear every cell inside it apart from the formula. If the area looks empty, check for merged cells, and check that the formula is not inside a table.

What does "spill" mean in Excel?

A formula that returns more than one value fills the neighbouring cells with the rest. =SORT(A2:A10) typed in one cell shows nine sorted values in nine cells. Only the first cell holds the formula; the others cannot be edited on their own.

Practise it

Functions involved

Other errors