Dynamic Arrays: FILTER, SORT & UNIQUE
Modern Excel functions that return a whole list of results from one formula — they “spill” down the sheet automatically.
What you will learn
- Get a list of distinct values with UNIQUE
- Pull out matching rows with FILTER
- Reorder results with SORT
One formula, many answers
Every formula so far returned one answer into one cell. Dynamic array functions are different: one formula can return a whole list, and Excel automatically fills — or spills — the results into the cells below and beside it. You write the formula once in the top cell, and the list appears on its own.
You will see a faint blue border around the spilled results. That border means “these cells came from one formula above.” The three you will use most are UNIQUE, FILTER and SORT.
Watch out: These functions need a modern Excel: Microsoft 365 or Excel 2021 and later, or Google Sheets. Older Excel (2019 and before) does not have them. Everything else in this course works everywhere; this lesson is the modern bonus.
UNIQUE: a list with no repeats
UNIQUE takes a range and returns each distinct value once, dropping the duplicates. Perfect for building a clean list of regions or products from messy raw data.
Suppose column A holds regions with repeats: North, South, North, North, South in A2:A6.
=UNIQUE(A2:A6)Note: Output (spills down 2 cells): North South UNIQUE scanned all five region cells and returned only the distinct ones — North and South — into two cells. The repeats were dropped. Add a new region to the data and the list grows by itself.
FILTER: keep only the rows that match
FILTER returns just the rows that meet a condition — like a live, formula-based version of the Filter button, except the results spill into a new spot you choose. You give it the range to return and a test that is true or false for each row.
Using regions in A2:A6 and sales in B2:B6, return the sales only where the region is North:
=FILTER(B2:B6, A2:A6="North")Note: Output (spills down): 120 90 100 FILTER tested each row of A2:A6 against ="North". The three North rows passed, so their sales (120, 90, 100) spilled into three cells. South rows were left out. Unlike the Filter button, this is a live formula — change the data and the list updates instantly.
SORT: order the results
SORT returns a range reordered. Add a -1 as a second argument to sort largest first (descending); leave it off for smallest-first. You can even wrap one function in another.
To get the North sales, biggest first, wrap the FILTER from above inside SORT:
=SORT(FILTER(B2:B6, A2:A6="North"), 1, -1)Note: Output (spills down):
120
100
90
Reading inside-out: FILTER first produced 120, 90, 100 (the North sales). SORT then reordered that list largest-first into 120, 100, 90. The 1 means sort by the first column and -1 means descending. One formula did the filtering and the sorting together.
| Function | What it returns | Everyday use |
|---|---|---|
UNIQUE | Each value once, no repeats | A clean list of categories |
FILTER | Only rows that pass a test | Just this region / this month |
SORT | A range in a new order | Top sellers first |
Tip: These combine beautifully. =SORT(UNIQUE(A2:A100)) gives an alphabetical, duplicate-free list of every category in your data — a one-formula way to build the choices for a drop-down or a report heading.
Watch out: A spill needs empty room. If a cell where the result wants to land is already filled, Excel shows the error #SPILL!. Clear the cells below and beside your formula and the list will spill in.
Q. What does “spill” mean for a dynamic array function like FILTER?
✍️ Practice
- Make a column of regions with repeats and use UNIQUE to list each region once.
- Add a sales column and use FILTER to spill out only one region’s sales, then wrap it in SORT to order them largest first.
🏠 Homework
- From a 12-row table of Product and Sales, use UNIQUE to list the products, FILTER to show the sales of one product, and SORT to display all sales from highest to lowest.