Spilling
A dynamic array formula returns multiple values from a single cell and spills into neighbouring cells. The spill range is outlined in blue and you edit only the top-left formula.
If something blocks the spill area you get #SPILL! — clear the obstruction rather than trying to work around it.
The core four
=UNIQUE(A2:A100) returns distinct values.
=SORT(A2:C100, 2, -1) sorts by the second column descending.
=FILTER(A2:C100, C2:C100>1000, "None found") returns only matching rows, with a fallback when nothing matches.
=SEQUENCE(12) generates 1–12; useful for building date scaffolds and numbering.
They compose: =SORT(UNIQUE(FILTER(A2:A100, B2:B100="East"))) gives a sorted distinct list of East-region values in one cell.
The # operator
=COUNTA(E2#) refers to the entire spill range starting at E2, however large it currently is.
This is how you build downstream formulas that adapt automatically. Combine with a chart source and the chart resizes with the data.
Related functions
TEXTSPLIT, TEXTBEFORE and TEXTAFTER handle string parsing without nested FIND/MID gymnastics. TOCOL and TOROW reshape ranges. LET names intermediate values inside a formula for readability and speed:
=LET(rate, B1, base, A2:A100, base*(1+rate))
LAMBDA lets you define a reusable custom function in the Name Manager — genuinely powerful, and the closest Excel comes to real programming without VBA.