Spilled range operator

When you're dealing with spilled array functions, such as SEQUENCE, it's possible to reference the entire spill range by placing the spilled range operator (#) after the range. In the following example, we have =SEQUENCE(10) in cell A2, which spills to A2:A11. In cell C2 we have the formula =SUM(A2#), which is the equivalent of =SUM(A2:A11), as A2:A11 is the spill range of the formula in A2. This returns returns 55, the sum of all the values in the spilled array range.

If your spill range grows or contracts, for instance by changing formula in A2 to =SEQUENCE(20), the SUM formula will automatically reflect that. If this was a normal range, we'd need to enter =SUM(A2:A11), and update it manually if the spill range changes. Often times, you may not realize if a range has changed, so this can remove a lot of guess work.

Spilled range operator for referencing entire spilled array ranges with =SUM(A2#).

Here's another example of using the spilled range operator in several formulas, where we create a unique list from a list of names, sort the list, then filter for names over 3 characters in length. We have =SORT(C2#) in cell E2, and =FILTER(C2#,LEN(C2#)>3) in cell G2.

Spilled range operator (#) to reference an entire spilled array range with =FILTER(C2#,LEN(C2#)>3)

Known Limitations

The spilled range operator does not support references to closed workbooks. Instead it will return a #REF! error value. Opening the referenced workbook will resolve the issue.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

FILTER function

RANDARRAY function

SEQUENCE function

SORT function

SORTBY function

UNIQUE function

#SPILL! errors in Excel

Dynamic arrays and spilled array behavior

Implicit intersection operator: @

A subscription to make the most of your time

Need more help?

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

×