Exceljet

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:

=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.

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.