Lesson 3.7: Modern Dynamic Arrays & Spill Ranges
Learning Objective
Leverage Excel 365's Dynamic Array calculation engine to filter, deduplicate, sort, and parse tabular data in memory without VBA, manual copying, or legacy Ctrl+Shift+Enter arrays.
🎥 Video Chapter: Chapter 3 – Excel Formulas & Functions (1:38:56)
Core Dynamic Array Functions
=UNIQUE(array, [by_col], [exactly_once]): Extracts distinct values from a column or range.=FILTER(array, include_boolean_array, [if_empty]): Returns rows matching criteria.=SORT(array, [sort_index], [sort_order]): Sorts range dynamically.=SORTBY(array, by_array1, [order1], ...): Sorts based on an external column.=TEXTSPLIT(text, col_delim, [row_delim]): Dynamically splits text strings across columns/rows.=XMATCH(lookup_value, lookup_array): Dynamic position finder with exact match by default.
Understanding the Spill Range (#)
When a formula returns multiple values, it "spills" into neighboring cells.
To reference the entire dynamic spilled output of cell G2:
EXCEL
=G2#
The #SPILL! Error
Occurs when a non-empty cell blocks the expansion path of a dynamic array. Clearing the blocking cells immediately resolves the error.
Related Knowledge
- Formulas: UNIQUE, FILTER, SORT, SORTBY, TEXTSPLIT, XMATCH
- Concepts: VLOOKUP vs XLOOKUP, Why Not Always Formulas
- Practice: Ex02 Formulas and Lookup Logic