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

Spilling is a new feature available in Office 365 only.