Dynamic Arrays: FILTER, SORT, and UNIQUE
Three newer functions that can return many results from a single formula, spilling automatically into nearby cells.
Compatibility: Microsoft 365, Excel 2021+
What You'll Learn
- What a dynamic array / spilling formula is
- How FILTER, SORT, and UNIQUE each work on their own
- How to nest these functions together for more complex results
These three functions are dynamic array functions — instead of returning one value, they can return many results that automatically "spill" into the cells below/beside the one you typed the formula into.
FILTER
=FILTER(A2:C20,B2:B20="West")
Returns every row from A2:C20 where the corresponding value in B2:B20 equals "West" — a live, formula-driven filtered view of your data, rather than a manual filter you'd have to reapply.
SORT
=SORT(A2:C20,3,-1)
Returns the range sorted by its 3rd column, in descending order (-1). Like FILTER, this is a live formula, not a one-time action.
UNIQUE
=UNIQUE(A2:A20)
Returns each distinct value from a range exactly once, with duplicates removed — useful for quickly generating a clean list of categories from a long, repetitive column.
Combining Them
These three nest together well — for example, =UNIQUE(FILTER(A2:A20,B2:B20="West")) returns a distinct list of only the values that also matched a filter condition.
Common Mistakes
- Typing something into a cell that a spill formula needs to expand into, which blocks the spill with a #SPILL! error
- Expecting FILTER/SORT/UNIQUE to work in an Excel version that doesn't yet support dynamic arrays
Practice Exercise
Using a data set with a Region column, use FILTER to pull only "East" region rows, then wrap that in UNIQUE to get a distinct list of products sold in the East region only.
Key Takeaways
- Dynamic array functions return multiple results that spill into nearby cells automatically
- FILTER, SORT, and UNIQUE are all live formulas, not one-time actions
- These functions can be nested together for more complex, still-live results