The term “spill” refers to a behavior where formulas that return multiple results “spill” these results into multiple cells automatically. This is part of " Dynamic Array " functionality. In the example shown, the formula in D5 is:
=SORT(B5:B14)
The SORT function returns 10 sorted results. Even though the formula is entered just once in cell D5, all 10 values “spill” into the range D5:D14 automatically.
The range of results returned by a formula that spills is called a spill range . When something on the worksheet blocks a spilled array formulas, it will return a #SPILL! error.
Spilling is a new feature available only in Excel 2021 and later .
The term “spill range” refers to the range of values returned by an array formula that spills results onto a worksheet. This is part of Dynamic Array functionality in the latest version of Excel.
In the example shown, the formula in D5 is:
=UNIQUE(B5:B16)
This formula returns seven unique values, and these results spill into the range D5:D11 automatically. The range D5:D11 is called the “spill range”. When you select any cell in a spill range, entire range will be highlighted with a blue border.
Cell reference syntax
You can refer to a spill range with the first cell in the range and a hash character (#) like this:
=D5# // entire spill range
To count values returned to the spill range, you can write:
=COUNTA(D5#) // returns 7
To retrieve the 3rd value, you could use INDEX like this:
=INDEX(D5#,3) // returns "green"
If something on the worksheet blocks a spilled formula, it will return a #SPILL! error.
