FILTER / SORT / UNIQUE — Excel guide
Dynamic arrays (365/2021+): formulas that return whole result sets and 'spill' into neighboring cells — live filtering, sorting, and deduplication with zero copies.
FILTER
Keep only the rows where a condition is true.
- Contains-text: FILTER(data, ISNUMBER(SEARCH("pro", col))) — FILTER has no wildcards.
- if_empty avoids #CALC! when nothing matches.
- The include array must be exactly as tall as the data.
SORT
Return the array in order — original stays untouched.
- SORTBY sorts by ranges you don't return — names ordered by a hidden score, multi-key sorts.
- sort_order -1 = descending.
UNIQUE
The distinct values — a live, self-updating dedupe.
- Unlike Remove Duplicates, it updates as data changes and never touches the source.
- Multi-column input dedupes whole ROWS (distinct pairs).
- #SPILL! means the landing zone is occupied — clear the blocking cells.
- Reference a whole spill with # (=E2#): self-sizing dropdowns and follow-on formulas.