"Spilling" in Excel refers to the automatic expansion of formulas and functions to adjacent cells, without the need for manually copying or dragging the formula to those cells. Spilling is a new feature introduced in Excel 365 and is available for dynamic array formulas, which are formulas that can return multiple results in a single cell.
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)
When you enter a dynamic array formula in a cell and press Enter, Excel automatically "spills" the formula into the adjacent cells, where it calculates the results for each cell based on the input data. The number of spilled cells depends on the number of input data and the logic of the formula. The spilled cells are indicated by a blue border, which can be adjusted to include more or fewer cells by dragging the border.
What version of Excel do I need for the Spill function?
The 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 or 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 outputs 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.
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)
Source: Microsoft
The Tech Platform
Comments