#SPILL! error in Excel: what each cause means and the fix

#SPILL!

Why it happens

In dynamic array Excel, such as Excel for Microsoft 365, a formula that returns an array places those values in neighbouring cells. Microsoft calls this spilling, and formulas that can return arrays of variable size are dynamic array formulas (Dynamic array formulas and spilled array behavior). You type the formula once in the top-left cell and Excel sizes the output range itself; for example =SORT(D2:D11,1,-1) entered in F2 spills down to F11.

Because Excel decides the output range for you, it also has to refuse when that range is unusable. Microsoft's page How to correct a #SPILL! error lists the reasons it gives:

• Spill range isn't blank – one or more cells in the intended output range already contain something.
• Spill into merged cells – spilled array formulas cannot spill into merged cells.
• Table formula – spilled array formulas are not supported inside Excel tables.
• Extends beyond the worksheet's edge – the result would need more rows or columns than the grid has.
• Indeterminate size – the array is volatile and resizes between calculation passes, so Excel cannot settle on a size.
• Out of memory – the array is too large for Excel to hold.
• Unrecognized/Fallback – Excel cannot identify the cause; Microsoft's only advice is to check that the formula contains all the required arguments.

Microsoft notes that the same error can also come from a PivotTable whose range is not blank, but that PivotTable behaviour is listed as available to Microsoft 365 Insider participants.

Older array formulas entered with Ctrl+Shift+Enter (legacy array formulas) always return a fixed-size result, and the spilling behaviour does not apply to them.

How to fix it

Work out which cause you have first: select the cell with #SPILL!, look at the dashed border and the error checking alert beside the cell, and compare with the list above. Then follow the matching step.

1. Spill range isn't blank. Select the #SPILL! cell to see the dashed border of the intended spill range. Select the error checking alert and choose Select Obstructing Cells; Excel moves straight to the cell or cells in the way. Delete or move their entries. As soon as the obstruction is cleared, the formula spills. Use this command rather than scanning by eye: Microsoft's condition is that the spill range must be blank.

2. Spill into merged cells. Unmerge the cells inside the dashed border, or move the formula to a range that does not intersect merged cells. To unmerge, Microsoft's merge and unmerge guide says to select the Merge & Center down arrow and then Unmerge Cells. Select Obstructing Cells also takes you to the merged cells.

3. Table formula. Move the formula into ordinary cells outside the table, or turn the table into a normal range with Table Design > Tools > Convert to range. Microsoft's recommended pattern is to keep the source data in a table and put the spilling formula next to it in the grid, pointing at the table with structured references so the reference grows as rows are added.

4. Extends beyond the worksheet's edge. This is the classic whole-column lookup. Placed in E2, this formula used to look up only the value in A2:

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

   In dynamic array Excel it looks up every cell in column A, tries to return 1,048,576 results and hits the bottom of the grid. Microsoft gives three replacements:

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

   Reference only the lookup values you need. This returns a dynamic array but does not work inside Excel tables.

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

   Reference the value on the same row and copy the formula down. This works in tables but does not return a dynamic array.

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

   Ask Excel for implicit intersection with the @ operator and copy down. This also works in tables and does not return a dynamic array. If the lookup itself is returning wrong values once it fits, see VLOOKUP not working.

5. Indeterminate size. Microsoft's example is:

   =SEQUENCE(RANDBETWEEN(1,1000))
   

   The size depends on a random number, so every extra calculation pass produces a different-sized array and Excel gives up with #SPILL!. Microsoft ties this to RAND, RANDARRAY and RANDBETWEEN; it states that OFFSET, INDIRECT and TODAY do not return different values on each pass. The fix is to make the size of the array stop depending on a value that changes between passes, for example by driving it from a fixed number or a cell you control.

6. Out of memory. Reference a smaller array or range.

7. Unrecognized/Fallback. Check every argument of the function against its documentation; Microsoft gives no other guidance for this case.

Check that it worked

• The #SPILL! cell now shows the first result, and the cells below or beside it are filled.
• Select any cell inside the result: Excel draws a highlighted border around the whole spill area. Selecting a cell outside removes it.
• Select a cell other than the top-left one: the formula appears greyed ("ghosted") in the formula bar and cannot be edited there. That is expected; only the top-left cell is editable.
• If other formulas need the whole result, point them at the spill with the spilled range operator #, for example =SUM(A2#). The reference follows the spill as it grows or shrinks, so you do not reintroduce a fixed range that later collides with it.

The @ sign and formulas from older workbooks

If you open a workbook built in an older version of Excel, you may see @ added to some formulas, such as =@A1:A10 or =@INDEX(A1:A10,B1). According to Microsoft's page on the implicit intersection operator, the @ marks where the old formula language silently reduced a range to one value (the cell on the same row or column as the formula), and the formula calculates exactly as before.

This matters for #SPILL! in two ways. Removing an @ from in front of something that returns a range or array makes it spill, which can create a new #SPILL! if the neighbouring cells are occupied. And adding @ is the documented way to keep a whole-column formula returning a single value per row, as in the VLOOKUP example above. Microsoft also warns that a mixed formula, one combining array calculation and @, appears as _xlfn.SINGLE() in pre-dynamic array Excel and returns an error there, so avoid mixing the two if the file must open in older versions.

Linked workbooks

Microsoft states that dynamic arrays between workbooks are only supported while both workbooks are open; close the source and linked dynamic array formulas return #REF! on refresh, and the # operator behaves the same way. That is a different error, but it often shows up in the same sheets. Google Sheets has its own version of the cross-file problem, covered in IMPORTRANGE: you need to connect these sheets.

#SPILL! is Excel protecting cells it would otherwise overwrite, so the fix is almost always about the space around the formula rather than the formula itself: clear it, unmerge it, move out of the table, or shrink what the formula returns. If the formula spills correctly but a lookup inside it still returns #N/A for a value you can see, that is a matching problem, not a spill problem; XLOOKUP returns #N/A when the value exists walks through those causes.