What is the #SPILL! Error?
The #SPILL! error is a product of the new Dynamic Array calculation engine in use by Excel for Office 365 subscribers.
Historically, a formula would return a single result to a cell, such as calculating the SUM of a range of cells.
=SUM(A1:A10)
If we were to reference a range of cells (ex: =A1:A10), the results would be limited to the first encountered cell reference (A1).
This is because the formula is trying to display the contents of 10 cells within the confines of a single cell. Excel says to itself, “I can’t show ten results, so I’ll just show the first one.”
In the “old days” this issue was solved using array notation (CTRL-Shift-Enter) and cell pre-selection. Trust me, it’s not a road you want to go down.
With the new Dynamic Array engine, if a formula returns multiple results, the results are “spilled” into the adjacent cells.
This allows for multiple results to be displayed without the need for array notation.
All the new Dynamic Array functions utilize this spilling feature:
- SEQUENCE
- FILTER
- TRANSPOSE
- SORT
- SORTBY
- RANDARRAY
- UNIQUE
- XLOOKUP
- XMATCH
The spilling feature has also carried over to the traditional function library for use by functions such as SUM, MATCH, and FREQUENCY.
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
What Happens If I Can’t Spill?
One of the requirements for the spill behavior to operate properly is that there must be an area of unoccupied cells for which to spill into.
If you are trying to display 10 results, but there is data in any of the 10 needed cells, Excel will respond with a #SPILL! error message.
The dotted-blue border indicates the area that is needed to display the results.
To solve this problem, either move or delete the data located in the spill range.
If you are uncertain where the obstruction lies, you can select the Error “floatie” (exclamation button) and click “Select Obstructing Cells” to have attention drawn to the offending cell(s).
This is especially useful for situations where you may need to spill into hundreds or thousands of cells. Selecting each cell in the spill range to look for anomalous data would be impractical.
Not-so-Obvious Errors
Sometimes, the reason for a #SPILL! error is not as obvious as the example above.
An easy example is text that has been formatted with the same font color as the cell’s fill color.
Custom Number Formatting can also play a part in hiding text. Take for example the following number format code set.
; ; ;
This is a sneaky way to hide anything placed in a cell, regardless of font color or cell color.
The three semi-colons are saying, “Show no positive numbers, show no negative numbers, show no zeroes, and show no text.”
If you wish to learn more about Custom Number Formatting, check out these posts:
Learning Excel Custom Number Formatting
Why you SHOULD be USING Custom Number Formatting
Complete Course: Excel Functions in 365
Confused about the NEW Excel Functions? Spill Errors and What Dynamic Arrays mean?
In my EXCEL DYNAMIC ARRAYS COURSE we cover it all!
You’ll master these functions through multiple exercises and challenges so you can use the FILTER Function instead of a complex combination of INDEX & MATCH.
Or use XLOOKUP instead of VLOOKUP.
Not only will these save you from headaches but also from wasting time doing things the long way.
Get access to the Course HERE.
Other Reasons for #SPILL!
There are some more exotic reasons for why the #SPILL! error can occur. The previously mentioned reasons are the most common, but here are other reasons you may wish to investigate if the obvious fails.
Indeterminate Size
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!.
Results Extend Beyond the Worksheet’s Boundary
For example, if we placed in cell B2 the following formula…
=VLOOKUP(A:A, A:C, 2, FALSE)The results would cause a #SPILL! error because Excel will lookup the entire column, return 1,048,576 results, and hit the end of the Excel grid. So close; we only needed one more cell.
It’s generally considered poor practice to select entire columns when selecting ranges.
Table Formulas
Spilled array formulas are not supported in Excel Tables. Spilled formulas should only exist in a single cell. Excel Tables will repeat the formula to every cell in the table’s column. This creates catastrophic interference between every cell in the column.
If you need to use the formula as a spilled array formula, you will need to revert the Excel table to a “plain table” using the Table Design (tab) -> Tools (group) -> Convert to Range option.
Out of Memory
The spilled array formula you’re attempting to enter has caused Excel to run out of memory. In these cases, try referencing a smaller array or range.
Spilling into Merged Cells
If the results of the spilled array encounter a cell that has been merged with other cells, a #SPILL! error will occur.
Unrecognized / Fallback
If Excel doesn’t recognize or can’t reconcile the cause of this error, make sure your formula contains all the required arguments for your scenario.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
