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
A cell in the way
UNIQUE below needs two cells, C2 and C3, and C3 already holds the word "Notes".
A B C 1 Name List 2 Ana #SPILL! 3 Ben Notes 4 Ana 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.
A B C 1 Name List 2 Ana Ana 3 Ben Ben 4 Ana In C2:
=UNIQUE(A2:A4)→ Ana; BenMerged 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.
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).
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
- UNIQUE: Removes duplicates from a list. The result spills.
- FILTER: Keeps only the rows that meet a condition. The result spills.
- SORT: Sorts a range. The result spills.
- SORTBY: Sorts a range by the values in another range.
- SEQUENCE: Generates a list of sequential numbers that spills into neighbouring cells.
- INDEX: The value at a given row and column of a range.
Other errors
- #N/A: A lookup could not find the value it was given.
- #VALUE!: The formula was given the wrong kind of value, such as text where it needs a number.
- #REF!: The formula refers to a cell that no longer exists.
- #DIV/0!: The formula divides by zero or by an empty cell.
- #NAME?: Excel does not recognise a word in the formula.
- #NUM!: The calculation has no valid number as its answer.
- #CALC!: A formula that returns a list ended up with nothing to return.
- #NULL!: Two ranges that were expected to cross do not.
- #####: The column is too narrow to show the number, or a date is negative.
- Circular reference: A formula depends on its own result.