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

Applies to: Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel 2021, Excel for the web, and the Excel apps for iPad, iPhone and Android. These are the versions that use dynamic arrays.

What #SPILL! means

Since dynamic arrays arrived, a single formula can return a whole list. Type =SORT(D2:D11) into F2 and Excel writes ten sorted values into F2:F11. That overflow into neighboring cells is called spilling, and the cells it fills are the spill range. Only the top-left cell holds the real formula; the others show it in faded text and can’t be edited on their own.

When Excel can’t write those results into the grid, it shows #SPILL! in the formula cell instead. The formula itself is usually fine. The problem is the space around it, or the shape of the result it asks for.

Microsoft’s #SPILL! troubleshooting page groups the causes into seven types. Excel tells you which one applies: select the error cell and point to the warning icon beside it, and the first line of the menu names the cause, such as Spill range isn’t blank.

Find your cause

Message	What’s wrong	Quickest fix
Spill range isn’t blank	Data or a hidden entry sits where results need to go	Select Obstructing Cells, then clear or move them
Spill range has merged cells	A merged cell is inside the spill range	Unmerge, or move the formula
Table formula	The formula is in an Excel table	Move it outside the table, or convert the table to a range
Extends beyond the worksheet’s edge	The result needs more rows or columns than the sheet has	Reference only the cells you need
Indeterminate size	The result size changes on every calculation pass	Calculate the random size in a separate cell
Out of memory	The array is too big for Excel to hold	Use a smaller range
Unrecognized/Fallback	Excel can’t tell what went wrong	Check the formula’s arguments

1. The spill range isn’t blank

This is the cause you’ll see most. Say A2:A50 holds customer names and you enter =UNIQUE(A2:A50) in D2. If anyone has typed a note in D6, Excel won’t overwrite it, so D2 shows #SPILL! and the dashed border marks the space the list wants.

1. Select the cell with #SPILL!.
2. Select the warning icon next to it and choose Select Obstructing Cells. Excel jumps to whatever is in the way.
3. Delete that entry, or move it somewhere outside the dashed border. The formula spills as soon as the range is clear.

If you need the blocking data, move the formula instead: cut the formula cell and paste it in an empty column with enough room below and to the right. Our guide on how to move a row in Excel without overwriting data shows how to shift a block of cells safely.

A cell can look empty and still block a spill. A single space typed by mistake, a formula that returns empty text with "", or white text on a white fill all count as content. Select Obstructing Cells finds these too, which is why it’s faster than hunting by eye.

2. Merged cells in the way

Spilled results can’t land in merged cells. If you enter =SEQUENCE(10) in B2 and someone merged B6:C6 for a subheading, the formula returns #SPILL! even though the merged cell is empty.

Select the merged cell, then on the Home tab open the Merge & Center drop-down and choose Unmerge Cells. If the sheet’s layout depends on the merge, move the formula to a column that doesn’t cross any merged cells. Select Obstructing Cells works here as well, which helps on sheets with many merges.

To center a heading without merging, select the cells, open Format Cells, and on the Alignment tab set Horizontal to Center Across Selection. The text looks the same, and formulas can still spill through those cells.

3. The formula is inside an Excel table

Excel tables don’t support spilled array formulas. A table row expects one value per cell, so a formula such as =SORT(Sales[Region]) typed into a table column returns #SPILL! rather than pushing results into the rows below.

You have two options:

• Move the formula outside the table. Put it in a column or sheet next to the table. It can still refer to table columns by name, and structured references like Sales[Region] grow automatically when you add rows.
• Convert the table to a normal range. Select a cell in the table, then go to Table Design > Tools > Convert to Range. You lose table features such as banded rows that extend automatically and calculated columns, so only do this if you don’t need them.

If you want a per-row result inside the table, write the formula so it returns one value, for example by referring to the current row with [@Region] instead of the whole column.

4. The result extends beyond the worksheet’s edge

A worksheet has 1,048,576 rows. A formula that returns that many values only fits if it starts in row 1. Microsoft’s page on this cause gives two typical examples: =VLOOKUP(A:A,A:C,2,FALSE) entered in E2, and =SORT(D:D) entered in F2. Each one returns a value for every row in the column, and starting in row 2 leaves one row too few.

This often catches people using older workbooks. Before dynamic arrays, a VLOOKUP with A:A as its lookup value only looked at the cell in its own row. Now it looks up every cell in column A. Microsoft suggests three fixes for the VLOOKUP case:

1. Reference only the lookup values you need: =VLOOKUP(A2:A7,A:C,2,FALSE). One formula returns all six results as a spilled list. This doesn’t work inside an Excel table.
2. Reference one cell and copy the formula down: =VLOOKUP(A2,A:C,2,FALSE). This is the classic approach. It works in tables but doesn’t spill.
3. Add the @ operator and copy down: =VLOOKUP(@A:A,A:C,2,FALSE). The @ tells Excel to use only the value in the formula’s own row, which is how the old formula behaved. It also works in tables and doesn’t spill.

For =SORT(D:D), give it a fixed range such as =SORT(D2:D500), or point it at a table column, which only includes rows that hold data. Whole-column references also make workbooks slower, so trimming them helps even when no error appears.

5. Indeterminate (volatile) size

Excel calculates some formulas more than once before it settles on a result. If the number of results keeps changing between those passes, Excel can’t reserve a spill range, and you get #SPILL! with the message Indeterminate size.

Microsoft’s example is =SEQUENCE(RANDBETWEEN(1,1000)). RANDBETWEEN picks a new number on every pass, so the list never has a fixed length. RAND, RANDARRAY and RANDBETWEEN cause this when they decide the size of a dynamic array. Other volatile functions such as OFFSET, INDIRECT and TODAY don’t, because they return the same value on every pass of one calculation.

A common workaround is to work out the size in its own cell first. Put =RANDBETWEEN(1,1000) in H1 and use =SEQUENCE(H1) in the spill cell. H1 holds a single number during each recalculation, so the size of the list should stay fixed until the next one.

6. Out of memory

Every value in a spilled result has to be held in memory. If the array is too large, Excel reports Out of memory instead of filling the grid. A formula like =SEQUENCE(1000000,500) asks for half a billion cells, which is far beyond what most computers can manage in one workbook.

The fix is to reference a smaller array or range. Replace whole-column references with the actual data range, filter the data down before sorting it, and split very large outputs across several formulas. If Excel freezes rather than showing the error, see Excel not responding for ways to recover the workbook.

7. Unrecognized or fallback

Sometimes Excel can’t say why the spill failed and shows Unrecognized/Fallback. Microsoft’s advice is to check that the formula has every argument it needs for what you’re trying to do. Open the function’s argument dialog with the fx button beside the formula bar to see which arguments are required, and compare the formula with Microsoft’s examples for that function.

If nothing is obviously wrong, re-enter the formula in an empty part of a new sheet. If it spills there, the problem is in the original location; if it still fails, the formula itself needs changing.

The @ implicit intersection operator

The @ sign is the reason many older formulas didn’t break when dynamic arrays arrived. It turns a multi-cell reference into one value, a process called implicit intersection, which Microsoft explains on its implicit intersection operator page:

• A single value is returned unchanged.
• A range returns the cell in the same row or column as the formula. With =@A1:A10 in row 5, you get the value from A5.
• An array returns its top-left value.

Old versions of Excel did this silently. Current Excel spills by default, so when it opens an older workbook, it adds @ to formulas that could otherwise spill and change their results. Common examples are =@A1:A10, =@INDEX(A1:A10,B1) and =@OFFSET(A1:A2,1,1), along with custom functions written in VBA. Formulas wrapped in functions that accept ranges, such as =SUM(A1:A10), don’t get an @ because nothing could spill.

Should you remove the @? Only if you want the formula to spill. If the expression returns one value, which is the usual case, removing it changes nothing. If it returns a range, removing it lets the result spill, and it may then hit any of the #SPILL! causes above. If you remove an @ that Excel added and later open the file in an older version, the formula appears as an array formula in braces.

You can also add @ yourself to stop a formula from spilling. That’s what fix 3 in the worksheet-edge section does with =VLOOKUP(@A:A,A:C,2,FALSE). Inside tables you’ll already know it from [@Region], which means the Region value in this row.

The opposite tool is the spilled range operator, #. Once =SEQUENCE(10) spills from A2, the formula =SUM(A2#) adds up the whole spill range, and Microsoft’s spilled range operator page explains that it keeps up if the list grows or shrinks. It’s handy for chaining formulas without guessing range sizes.

=SUM(A2#)

Older Excel versions and shared files

Excel 2019 and earlier don’t support dynamic arrays, so they never show #SPILL!. If you send a workbook with spilling formulas to someone on an older version, those formulas generally appear as legacy array formulas surrounded by braces, and they keep returning the values they had when the file was saved, in a fixed-size range.

Mixed formulas are the exception to watch. A formula that combines a spilled range with an @ reference, such as =A1:A10+@A1:A10, has no equivalent in older Excel. Microsoft says older versions show the @ part as _xlfn.SINGLE() and return a #NAME? error. When current Excel offers to rewrite a mixed formula, accepting its suggestion keeps the file compatible.

In the other direction, legacy array formulas entered with Ctrl + Shift + Enter still work, but Microsoft recommends replacing them. Copy the formula from the first cell, delete the old array range, and enter the formula again in the top-left cell with Enter. It then spills to whatever size the result needs.

If a formula shows a stale result rather than #SPILL!, calculation may be set to Manual; see Excel formulas not calculating or updating. For shortcuts that make large sheets easier to work with, see our Excel tips and tricks.

Related guides

• Excel formulas not calculating or updating: how to fix it
• Excel not responding: how to fix freezes and hangs
• Excel tips and tricks: 10 ways to work faster in Excel
• How to move a row in Excel (without overwriting data)

Official sources

• Microsoft Support: How to correct a #SPILL! error
• Microsoft Support: Implicit intersection operator: @
• Microsoft Support: Dynamic array formulas and spilled array behavior