by In this tutorial, you will find everything you need to know about the Spill feature. What is spill in Excel? How to get rid of spilling? How to fix a spill error? And a lot more.
The recent dynamic array update has bought a wealth of inspiring new capabilities. But all is centered around the core concept of "spilling". So, let's take a close look at this feature and how you can leverage it to improve your Excel experience.
What does spill mean in Excel?
The term spill or spilling refers to Excel's behavior when a formula that has resulted in multiple values outputs or "spills" all those values into neighboring cells.
For example, to filter the range A2:C9 according to the criteria in F1, you can use this formula:
=FILTER(A2:C9, B2:B9=F1)
Now observe the beauty of spilling. You enter the formula in just one cell (E4) and press Enter. Excel determines how many cells are needed to accommodate the results and fills all those cells automatically without you having to perform any additional moves.
The spill area, also called spill range, is surrounded by a highlighted border indicating that all the values inside it are calculated with a single formula in the upper left cell.
In dynamic Excel, spill behavior is native for all formulas, including the traditional ones that were not originally designed to handle arrays.
What version of Excel do I need for Spill function?
Spill is an exclusive feature of dynamic Excel. It was released to Office 365 subscribers in January 2020. Currently, it is supported in the following Microsoft 365 subscriptions:
- Excel 365 for Windows
- Excel 365 for Mac
- Excel 365 for Apple
- Excel 365 for Android
- Excel 365 for Windows Mobile
Excel Online does not support spilling nor dynamic arrays.
What is a spilled array formula?
A spilled array formula is another name for a dynamic array formula that returns results in multiple cells.
Please do not confuse it with an old-fashioned CSE array formula that requires pressing Ctrl + Shift + Enter to complete. Unlike legacy array formulas, dynamic ones are entered into a single cell and are completed with a normal Enter key.
Earlier, any Excel formula returned a result of a fixed size. A regular formula always output just one value in a single cell. An array formula could return values in multiple cells, but you needed to enter (or copy) it into all those cells, and then press Ctrl + Shift + Enter to explicitly tell the formula to calculate an array. A dynamic array formula does not need any instructions - if it can potentially return multiple values, it will do that automatically!
To better understand the difference, let's consider this simple example. Suppose you want to calculate 10% of the numbers in A3:A6. This can be done in three different ways:
Regular formula: entered in B3 and copied down through B6. The result is a single value.
=A3*10%
Multi-cell CSE array formula: entered in B3:B6 and completed with the Ctrl + Shift + Enter key combination. The result is multiple values in a predefined number of cells.
{=A3:A6*10%}
Dynamic array formula (aka spilled array formula): entered in B3 and completed with a usual Enter hit. The result is a dynamic spill range.
=A3:A6*10%
The below image shows all three formulas in action:
What is #SPILL in Excel?
#SPILL! is an error indicating that something prevents the formula from spilling. It may be caused by various reasons such as non-empty cells, formula overlap, merged cells, etc.
To find out the root cause of the problem, click a warning icon with an exclamation mark that appears next to the error, and read the text in the first line:
In most cases, a #SPILL! error is caused by some other data or formulas in the intended spill range. Once the blockage is removed, the error will disappear, and the range will get populated with the formula results as expected.
For more information, please see:
How to remove spill in Excel?
In Excel 365, the spilling functionality is deeply integrated at the core level. There is no way to "turn off spill in Excel" globally. However, you can prevent a certain formula from filling multiple cells by using the implicit intersection operator (@).
For example, the following XLOOKUP formula will throw a #SPILL error after failing to spill over a million results:
=XLOOKUP(E:E, A:A, C:C)
And this one reduces the lookup_value array (E:E) to a single value and works nicely:
=XLOOKUP(@E:E, A:A, C:C)
For more information, please see Implicit intersection in Excel.
Excel spilling - key takeaways
Below is a short summary of the key points on the Excel spill feature:
- A spilled array formula is entered in only one cell and fills multiple cells automatically.
- The size of a spill range is controlled by Excel, not by the user.
- Only the upper left cell in the spill range contains the formula and only that cell can be edited. When you change the formula, the values in the spill area will be updated. When you delete the formula, all the results will be gone.
- It is not possible to disable spill in Excel globally. However, you can force a certain formula to return a single value rather than multiple values.
- Spilled array formulas do not work in Excel tables. If you need to calculate a table's data, place your dynamic array formula outside the table.
- Dynamic array formulas referring to another workbook work only if both files are open. If the source book is closed, a linked spilled array formula will return a #REF! error.
That's the essentials of Excel spilling. If there's anything else you'd like to know about it, just post a comment and we'll try to answer :)
2 comments
I'm using a unique function that returns an array of values =unique(d3:d22) through vbscript. As I need to know the last row number. EXCEL is adding the @ implicit intersection operator air automatically. I need the spill. I saw suggestions of substitute, curly braces and ctrl+alt-enter.. not sure how to do the last one through code but the first two are not working. Substitute is removing the @ but not executing the formula.
Hello!
I cannot reproduce your problem as I do not have your function. Check your Excel version (at least 365). Perhaps this is the problem.