How to correct a #SPILL! error

#SPILL errors are returned when a formula returns multiple results, and Excel cannot return the results to the grid. For more details on these error types, see the following help topics:

This error occurs when the spill range for a spilled array formula isn't blank.

#SPILL! error - Spill range isn't blank

When the formula is selected, a dashed border will indicate the intended spill range.

You can select the Error floatie, and choose the Select Obstructing Cells option to immediately go the obstructing cell(s). You can then clear the error by either deleting, or moving the obstructing cell's entry. As soon as the obstruction is cleared, the array formula will spill as intended.

Excel was unable to determine the size of the spilled array because it's volatile, and resizes between calculation passes. For instance, the following formula will trigger this #SPILL! error:

=SEQUENCE(RANDBETWEEN(1,1000))

Dynamic array resizes may trigger additional calculation passes to ensure the spreadsheet is fully calculated. If the size of the array continues to change during these additional passes and does not stabilize, Excel will resolve the dynamic array as #SPILL!.

This error value is generally associated with the use of RAND, RANDARRAY, and RANDBETWEEN functions. Other volatile functions such as OFFSET, INDIRECT, and TODAY do not return different values on every calculation pass. 

For example, when placed in cell E2 as in the example below, the formula =VLOOKUP(A:A,A:C,2,FALSE) would previously only lookup the ID in cell A2 . However, in dynamic array Excel, the formula will cause a #SPILL! error because Excel will lookup the entire column, return 1,048,576 results, and hit the end of the Excel grid.

#SPILL! error caused with =VLOOKUP(A:A,A:D,2,FALSE) in cell E2, because the results would spill beyond the worksheet's edge. Move the formula to cell E1, and it will function properly.

There are 3 simple ways to resolve this issue:

#

Approach

Formula

1

Reference just the lookup values you are interested in. This style of formula will return a dynamic array, but does not work with Excel tables

Use =VLOOKUP(A2:A7,A:C,2,FALSE) to return a dynamic array that won't result in a #SPILL! error.

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

2

Reference just the value on the same row, and then copy the formula down. This traditional formula style works in tables, but will not return a dynamic array.

Use the traditional VLOOKUP with a single lookup_value reference: =VLOOKUP(A2,A:C,32,FALSE). This formula will not return a dynamic array, but can be used with Excel tables.

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

3

Request that Excel perform implicit intersection using the @ operator, and then copy the formula down. This style of formula works in tables, but will not return a dynamic array.

Use the @ operator, and copy down: =VLOOKUP(@A:A,A:C,2,FALSE). This style of reference will work in tables, but will not return a dynamic array.

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

Spilled array formulas aren't supported in Excel tables. Try moving your formula out of the table, or converting the table to a range (click Table Design > Tools > Convert to range).

#SPILL! error - Table formula

The spilled array formula you're attempting to enter has caused Excel to run out of memory. Please try referencing a smaller array or range.

Spilled array formulas cannot spill into merged cells. Please un-merge the cells in question, or move the formula to another range that doesn't intersect with merged cells.

#SPILL! error - Spill into merged cell

When the formula is selected, a dashed border will indicate the intended spill range.

You can select the Error floatie, and choose the Select Obstructing Cells option to immediately go the obstructing cell(s). As soon as the merged cells are cleared, the array formula will spill as intended.

Excel doesn't recognize, or can't reconcile the cause of this error. Please make sure your formula contains all the required arguments for your scenario.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

FILTER function

RANDARRAY function

SEQUENCE function

SORT function

SORTBY function

UNIQUE function

Dynamic arrays and spilled array behavior

Implicit intersection operator: @

Need more help?

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×