Quick, clean, and to the point

Spill range

Example spill range

The term "spill range" refers to the range of values returned by an array formula that spills results onto a worksheet. In the example shown, the formula in D5 is:


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.

Note: The spilling behavior is a beta feature available only through the Office Insiders program. This feature is expected to be released in 2019 to Office 365 subscribers.