With the introduction of the dynamic arrays and spill concepts, Microsoft has also released a spill range operator (#). This operator is designed to reference spill ranges dynamically.

For example, the cell D3 has =UNIQUE(B3:B16) formula which spills the return values along to column D, D3:D11. To sort the values of the array, the cell F3 has =SORT(D3#) formula. The reference D3# refers the D3:D11 range.

If we add a new value into the list in B3:B16, we increase the count of unique values in the list. For example, replacing one of the rows with WATER with another BUG in the source will increase the populated rows in the column D, D3:D12 as well as sorted values in the column F. References with the spilled range operator reacts automatically. After the update, D3# refers D3:D12.

Key Points

1. The operator can only be used with spilled ranges. Regular ranges and Excel Tables are not supported.
2. References with the spilled range operator work in bottom and left directions.
3. The spilled range operator does not support references in closed workbooks. Both workbooks should be open.
4. You can use the source range as a table to create fully dynamic references between formulas.
5. At the time of writing this article, Microsoft announced that this formula is currently only available to a number of select insider users. When it’s ready, the feature is planned for release for Office 365 users.