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)

Note: September 24, 2018: Dynamic array functions are currently a beta feature, and only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

FILTER function

RANDARRAY function

SEQUENCE function

SINGLE function

SORT function

SORTBY function

UNIQUE function

#SPILL! errors in Excel

Spilled array behavior

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

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×