The Spilled Range Operator
The average spreadsheet user spends a lot of time managing formulas in spreadsheets, particularly when new data is added to a list. The Table feature, which I discuss in Chapter 7, Automating Tasks with the Table Feature, offers one approach for eliminating this manual task. A second approach involves using the Spilled Range Operator (#) to create formulas that expand into more rows or contract into fewer rows based on changes in your data.
Nuance
You can only use the Spilled Range Operator in conjunction with formulas that reference results generated by a dynamic array function. This means the Spilled Range Operator is not available in Excel 2019 and earlier. If you use the Spilled Range Operator to reference data that is not in a dynamic array, the formula might return zero or a #VALUE! error.
The =SORT(UNIQUE(FILTER(C3:C12,D3:D12>H2))) formula in G6 of Figure 10.22 combines several concepts from this chapter to generate an alphabetical list...
