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