What are dynamic arrays and why do they matter?

A dynamic array formula returns multiple values from one cell and spills into the neighbouring range. FILTER, SORT, UNIQUE and SEQUENCE are the core functions.

They matter because they replace whole patterns that previously needed helper columns or array-entered formulas. =SORT(UNIQUE(FILTER(A2:A100, B2:B100="East"))) produces a sorted distinct list in one cell.

The # operator references a whole spill range, so downstream formulas and charts resize automatically. #SPILL! means something is blocking the target area.