How to fix the #SPILL! error in Excel
#SPILL! cell, click the warning icon, choose Select Obstructing Cells, then delete or move what's there.Formulas like UNIQUE, FILTER, SORT and SEQUENCE return several results. Excel puts them in the cell with the formula and "spills" the rest into the cells below and to the right. If it can't, it shows #SPILL! instead. When you select the error cell, a dashed border shows the range Excel wanted to use.
Click the warning icon next to the cell: the first line of its menu names the cause, for example Spill range isn't blank. The sections below cover each cause in order of how often it happens.
Spilling and #SPILL! exist only in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. Older versions don't spill.
Causes and fixes
- Something is in the way (spill range isn't blank)
- The formula is inside an Excel table
- Merged cells in the spill range
- A whole-column reference makes the result too big
- The size of the result keeps changing (spill range is unknown)
- The result runs off the edge of the sheet
1Something is in the way (spill range isn't blank)
How to recognise it
The warning icon says Spill range isn't blank. The blocking cell might look empty: a single space, an empty text result ="" from another formula, or white text all count as content.
Fix
Click the warning icon and choose Select Obstructing Cells. Press Delete to clear them, or move them somewhere else. The results appear as soon as the range is free.
If the content has to stay, move the formula to a place with enough empty room below and to the right.
Prevent it. Give spilling formulas their own area of the sheet, with nothing typed underneath. The list will grow and shrink as your data changes.
Try it live: UNIQUE blocked by a note
C2 holds =UNIQUE(A2:A9), which needs C2:C5. A note in C4 is in the way, so C2 shows #SPILL!. Select C4 and press Delete to watch it spill, or click an example to move the formula or return a single cell.
2The formula is inside an Excel table
How to recognise it
The formula sits in a column of an Excel table, and the warning icon mentions a table. Excel tables (made with Insert > Table) don't allow spilled results.
Fix
Move the formula to a cell outside the table. If you don't need the table features, right-click inside the table and choose Table > Convert to Range.
If you need one result per table row, use a formula that returns one value, for example =COUNTIF([Region],[@Region]) instead of a spilling formula.
3Merged cells in the spill range
How to recognise it
The dashed spill border covers cells that are merged, and the warning icon mentions merged cells.
Fix
Select the merged cells and click Home > Merge & Center to unmerge them. To keep a heading centered over several columns without merging, use Format Cells > Alignment > Horizontal: Center Across Selection.
4A whole-column reference makes the result too big
This often happens with older formulas that were written for one row at a time, such as:
=VLOOKUP(A:A,Prices!A:B,2,FALSE)In current Excel, A:A as the lookup value makes the formula return over a million results, one for every row. Placed anywhere below row 1, that can't fit on the sheet.
How to recognise it
The warning icon says the spill range is too big, or the error appears as soon as you enter a formula that uses A:A or 1:1 where one value was expected.
Fix
Point at just the rows you need, so the result spills only that far:
=VLOOKUP(A2:A100,Prices!A:B,2,FALSE)Or go back to one result per row: refer to one cell and fill the formula down.
=VLOOKUP(A2,Prices!A:B,2,FALSE)Adding @ also works: =VLOOKUP(@A:A,Prices!A:B,2,FALSE) uses only the value in the formula's own row, like older versions of Excel did.
5The size of the result keeps changing (spill range is unknown)
How to recognise it
The warning icon says the spill range is unknown. This happens when a volatile function decides how big the result is, for example =SEQUENCE(RANDBETWEEN(1,100)). The size changes every time Excel recalculates.
Fix
Use a fixed size, or base the size on data that only changes when you edit it, such as =SEQUENCE(COUNTA(A2:A100)).
6The result runs off the edge of the sheet
How to recognise it
The warning icon says the result extends beyond the edge of the worksheet. For example =TRANSPOSE(A:A) tries to fill more columns than a sheet has (16,384), and a long list started near the bottom of the sheet runs out of rows.
Fix
Use a smaller range, such as =TRANSPOSE(A2:A50), or start the formula higher up or further left. If the icon mentions memory, the result is simply too large for Excel to calculate, so narrow the ranges.
Still stuck?
- Click the error, then the warning icon, and read the first line. It names the cause.
- Look for "empty" cells that contain a space or a formula returning
"". - Check whether the formula is inside a table (a Table Design tab appears when you click it).
- Replace
A:Astyle references with exact ranges likeA2:A100. - Seeing
#NAME?instead? The file is open in a version of Excel without dynamic array functions such as UNIQUE or FILTER.