UNIQUE, SORT & FILTER Array Formula Practice
UNIQUE, SORT, and FILTER are "spill" formulas — type one formula into a single cell and the results automatically flow (spill) into as many cells below and across as needed. UNIQUE lists each distinct value in a range once; SORT reorders a range; FILTER returns only the rows matching a condition. Together they do in one formula what used to take a pivot table or a helper-column trick.
Worked example
=UNIQUE(A2:A20)Spills a list of every distinct value in A2:A20, duplicates removed
Nest them: =SORT(UNIQUE(A2:A20)) returns the distinct values, alphabetized.
FILTER replaces a lot of manual AutoFilter clicking
=FILTER(A2:C20, B2:B20="Open") spills every full row where column B says "Open" — no clicking the filter dropdown, and it updates live as the source data changes.
One requirement: a recent Excel version
UNIQUE, SORT, and FILTER require Microsoft 365 or Excel 2021+ — the same spill-formula engine XLOOKUP needs. On an older version, these formulas return a #NAME? error.
Practice this for real
Try the "UNIQUE, SORT & FILTER" exercises
Type the formula into a real spreadsheet and get instant feedback.