This guide shows you how to find out why a formula shows the Excel #SPILL! error and how to apply the fix for that cause. The error has several causes, and each needs a different fix. Clicking Select Obstructing Cells solves only one of them.
According to Microsoft's #SPILL! support page, Excel returns #SPILL! when a formula returns multiple results and Excel cannot place them on the grid. Spilling means a formula that returns several values puts them in neighbouring cells. For example, =SORT(D2:D11,1,-1) entered in F2 spills down to F11, as Microsoft's dynamic array page explains. The error appears when something stops that spill.
Step 1: Click the error and read the alert
- Select the cell showing #SPILL!.
- Look at the dashed border. It marks the range the formula wants to fill.
- Click the error-checking alert icon next to the cell. The alert tells you which cause Excel has detected.
Then jump to the matching section below.
Cause: the spill range isn't blank
This is the most common cause. Something already sits in a cell the formula needs, such as a value, a stray space or another formula. Microsoft's fix takes three clicks:
- Select the cell showing #SPILL!.
- Open the error-checking alert and choose Select Obstructing Cells. Excel jumps to the cell or cells in the way.
- Delete the blocking entry, or move it somewhere else.
Once the obstruction is cleared, the formula spills. You don't have to re-enter anything.
Watch out for cells that look empty. If the dashed border covers a cell that seems blank but Excel still flags it, Select Obstructing Cells will show you which one it is.
Cause: indeterminate size (RAND, RANDBETWEEN, RANDARRAY)
Here Excel cannot work out how big the array is. The formula is volatile, and its size changes between calculation passes. Microsoft's example is:
=SEQUENCE(RANDBETWEEN(1,1000))
Each pass, RANDBETWEEN picks a new number, so SEQUENCE asks for a different number of rows. Microsoft says that if the size keeps changing and never settles, Excel resolves the formula as #SPILL!. The same page says this is usually linked to RAND, RANDARRAY and RANDBETWEEN. OFFSET, INDIRECT and TODAY are volatile too, but they don't return different values on every calculation pass.
Deleting cells won't help with this one. The size argument needs to come from something that stays put between passes. Two options:
- Type the size as a fixed number.
- Point the size at a cell that holds a plain value rather than a random function.
Cause: the formula runs past the edge of the sheet (whole-column VLOOKUP)
This one catches people who learned VLOOKUP in older Excel. Microsoft's example is this formula, entered in E2:
=VLOOKUP(A:A,A:C,2,FALSE)
Older Excel looked up only the ID in A2. Dynamic-array Excel treats A:A as a request to look up every cell in column A. According to Microsoft, it returns 1,048,576 results. Because the formula starts in row 2, those results run past the bottom of the grid, and you get #SPILL!.
The better fix is to reference only the lookup values you actually have:
=VLOOKUP(A2:A7,A:C,2,FALSE)
This returns a dynamic array, one result per ID in A2:A7. Microsoft notes this approach does not work with Excel tables. If your data is in an Excel table, use the table fix in the next section: move the formula outside the table and refer to the table with structured references. For lookup basics, see How to Use VLOOKUP in Excel.
Cause: the formula is inside an Excel table
Microsoft's dynamic array page says spilled array formulas aren't supported inside Excel tables.
- Move the formula to an ordinary cell outside the table.
- If you still want the table as the data source, refer to it with structured references. For example, write
=SORT(Table1[Sales])rather than a fixed range.
This matters for FILTER, SORT and UNIQUE, which are often pointed at tables. See How to Use FILTER in Excel With Two Conditions and How to Sort in Excel With a Formula for working examples.
Edit the first cell, not the spilled ones
Selecting any cell in a spill range shows a highlighted border. Only the top-left cell can be edited. The other cells show a greyed-out "ghost" copy of the formula. To change a spilled formula, select the first cell, edit it and press Enter, as the dynamic array page describes.
The same page says old Ctrl+Shift+Enter array formulas still work but "should no longer be used." To convert one:
- Copy the formula text from its first cell.
- Delete the whole legacy array range.
- Re-enter the formula in the top-left cell.
Microsoft warns that the two kinds of formula can calculate differently, so check the results first.
Other causes and limits
More causes. Microsoft's #SPILL! page covers causes beyond the ones above. If the alert names something not listed here, check that page.
PivotTables. The page also notes that PivotTables can show #SPILL! when their range isn't blank. That feature is available only to Microsoft 365 Insider participants.
Links to other workbooks. Dynamic arrays that pull from another workbook work only when both files are open. If the source workbook is closed, linked formulas return #REF! when refreshed, according to Microsoft.
