by Svetlana Cheusheva, updated on
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.
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:
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.
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 Online does not support spilling nor dynamic arrays.
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.
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.
Dynamic array formula (aka spilled array formula): entered in B3 and completed with a usual Enter hit. The result is a dynamic spill range.
The below image shows all three formulas in action:
#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:
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.
Below is a short summary of the key points on the Excel spill feature:
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 :)
Table of contents