Fixes / Excel · Checked

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

Short answer

#SPILL! means a formula returns several results (a dynamic array) but Excel can't place them in the cells below or beside it. Usually one of those cells isn't empty. Click the cell with the error, click the yellow warning icon, choose Select Obstructing Cells, then delete or move whatever is there. The results fill in automatically once the range is clear.

What this message means

In Excel for Microsoft 365, Excel 2021, Excel 2024 and Excel for the web, a formula can return more than one value. Excel then "spills" those values into the neighbouring cells, which are called the spill range. #SPILL! appears in the formula cell when Excel can't write the results there. The formula itself is usually fine. The problem is the space it needs. Something is in the way, the range would run off the sheet, or the formula sits somewhere spilling isn't allowed, such as inside an Excel table. The warning icon next to the cell names the exact reason.

Common causes

CauseHow to tell
The spill range isn't blank (data, a space or a formula is in the way)Click the warning icon beside the cell. The first line reads "Spill range isn't blank". When the error cell is selected, a dashed border shows the area Excel wants to fill, and at least one cell inside it has content. The content can be something invisible, such as a space, white text or a formula that returns "".
The formula is inside an Excel tableThe warning says "Table formula". The cell is in a banded range with filter arrows in the header, and the Table Design tab (Table on Mac) appears when you click it. Often every row of the column shows #SPILL!.
The results would go past the edge of the worksheetThe warning says "Extends beyond the worksheet's edge". The formula usually refers to a whole column or row, for example =A:A*2 or =VLOOKUP(A:A,...), and isn't in row 1. Or it produces more than 1,048,576 rows or 16,384 columns.
Merged cells are in the spill rangeThe warning says "Spill into merged cells". A cell below or beside the formula spans several columns or rows, and Merge & Center is highlighted on the Home tab when you select it.
The result size keeps changing (volatile functions)The warning says "Indeterminate size". The formula uses RAND, RANDARRAY or RANDBETWEEN to decide how many results to return, for example =SEQUENCE(RANDBETWEEN(1,100)).
The result is too large for available memoryThe warning says "Out of memory". The formula refers to very large ranges, such as several full columns, and Excel may slow down or hang while calculating.

How to fix it

1. Clear the cells blocking the spill range

  1. Click the cell that shows #SPILL!. A dashed border shows the range the formula needs.
  2. Click the yellow warning icon next to the cell and confirm the first line says "Spill range isn't blank".
  3. Choose Select Obstructing Cells. Excel selects the cell or cells that are in the way.
  4. Press Delete to clear them, or cut and paste them somewhere else if you need the data. Invisible content (spaces, white text, formulas that return "") also blocks the range, so clear it the same way.
  5. Click back on the formula cell. The results should now spill into the range.

2. Replace whole-column references with an exact range

  1. Click the formula cell and check the warning says "Extends beyond the worksheet's edge".
  2. In the formula bar, look for whole-column references like A:A or whole-row references like 1:1.
  3. Replace them with the actual data range, for example change =VLOOKUP(A:A,D:E,2,FALSE) to =VLOOKUP(A2:A500,D:E,2,FALSE).
  4. Alternatively, refer to a single cell (=VLOOKUP(A2,D:E,2,FALSE)) and fill the formula down, or put @ in front of the reference (=VLOOKUP(@A:A,D:E,2,FALSE)) to return one result per row.
  5. Press Enter and check the error is gone.

3. Move the formula out of the Excel table, or convert the table to a range

Table Design tab in Excel for Microsoft 365, 2021 and 2024 on Windows and Excel for the web. In Excel for Mac the tab is named Table.

  1. Click the cell and confirm the warning says "Table formula". Spilled array formulas are not supported inside Excel tables.
  2. Option A: cut the formula (Ctrl+X on Windows, Cmd+X on Mac) and paste it into an empty cell outside the table, with empty space below and to the right.
  3. Option B: click anywhere in the table, open the Table Design tab (called Table in Excel for Mac) and choose Convert to Range, then click Yes.
  4. Option C: if you need one result per table row, rewrite the formula to use the current row only, for example [@Amount] instead of [Amount].

4. Unmerge cells in the spill range

  1. Click the formula cell and confirm the warning says "Spill into merged cells".
  2. Select the merged cells below or beside the formula (Select Obstructing Cells in the warning menu will highlight them).
  3. On the Home tab, click the arrow next to Merge & Center and choose Unmerge Cells.
  4. If the merged layout must stay, move the formula to a part of the sheet with no merged cells in its path. Center Across Selection (Format Cells > Alignment > Horizontal) gives a similar look without merging.

5. Remove random functions from the size calculation

  1. Click the formula cell and confirm the warning says "Indeterminate size".
  2. Find RAND, RANDARRAY or RANDBETWEEN in the part of the formula that sets how many rows or columns to return.
  3. Replace it with a fixed number or a reference to a cell holding the number, for example =SEQUENCE(C1) instead of =SEQUENCE(RANDBETWEEN(1,100)).
  4. If you need a random count, generate it once in a separate cell, then copy and Paste Values so it stops recalculating, and reference that cell.

6. Make an older formula return a single value with @

Excel for Microsoft 365, Excel 2021 and Excel 2024 (Windows and Mac) and Excel for the web. Older versions do not spill and do not show #SPILL!.

  1. Use this when a workbook built in Excel 2019 or earlier now shows #SPILL! in newer Excel, or when you only want one result per cell.
  2. Click the formula cell and find the range reference that makes it return many values, for example =$B$5:$B$10+3.
  3. Add @ in front of the reference: =@$B$5:$B$10+3. This tells Excel to use only the value on the same row (implicit intersection), as older Excel did.
  4. Press Enter, then fill the formula down if needed.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

Click the warning icon again and read the exact reason on its first line. If it says "Unrecognized/Fallback", Excel can't identify the cause, so check that the formula has every required argument and try it in a new blank sheet to rule out the surrounding layout. If the file must also be opened by people on Excel 2019 or earlier, avoid dynamic array formulas or use @ so the file behaves the same everywhere. For a #SPILL! in a PivotTable or a shared company template, send the workbook to whoever maintains it, or ask in the Microsoft Tech Community Excel forum with a screenshot of the formula and the warning text.

Questions people ask

Why do I get #SPILL! when the cells look empty?

The cells probably contain something you can't see: a space, text in white font, an apostrophe or a formula that returns "". Use Select Obstructing Cells from the warning icon to find them, then press Delete.

How do I stop a formula from spilling and get just one result?

Refer to a single cell instead of a range (A2 rather than A2:A100), or put @ before the range (=@A2:A100) to use implicit intersection. You can also wrap the result in INDEX or TAKE to pick one value.

Can I use spilling formulas inside an Excel table?

No. Excel tables don't support spilled array formulas and return #SPILL! with the reason "Table formula". Put the formula outside the table, or convert the table with Table Design > Convert to Range.

Why did an old spreadsheet start showing #SPILL! after an Office update?

Excel for Microsoft 365, 2021 and 2024 use dynamic arrays, so a formula that used to return one value can now return many. Add @ before the range reference, or change a whole-column reference like A:A to a single cell and fill it down.

How do I delete or edit spilled results?

Only the top-left cell holds the formula. The other cells show greyed-out text in the formula bar and can't be edited. Select the first cell and edit or delete the formula there, and the whole spill range updates or clears.

Sources

Related errors