FILTER, SORT and UNIQUE in Excel: A Practical Guide

FILTER, SORT and UNIQUE are dynamic array functions that let you pull, reorder, and de-duplicate data automatically, without needing pivot tables or manual copy-pasting. They "spill" their results across as many cells as needed, and update on their own whenever your source data changes. If you're on Microsoft 365, they're some of the most genuinely time-saving functions Excel has added in years, and once you get used to them it's hard to go back to the old way.

I want to be upfront that this one took me longer to warm up to than VLOOKUP or INDEX/MATCH did. For years I built little manual processes — sort the data, then Advanced Filter it, then remove duplicates with the ribbon button, then repeat next month — because that's just how I'd always done it and it worked well enough. It wasn't until a manager asked me, on a Friday afternoon with about twenty minutes' notice, to pull "just the unique client names from this list, sorted alphabetically, filtered to only NSW" that I actually sat down and forced myself to learn these three functions properly instead of doing my usual multi-step manual routine. What used to take me ten minutes of clicking through menus became one formula. I've felt a bit sheepish about how long I put it off ever since.

What does FILTER actually do?

FILTER pulls out only the rows that match a condition you set, and the result updates automatically if your source data changes. It replaces a lot of what people used to do with Advanced Filter or a helper column plus manual copying.

The basic structure:

=FILTER(array, include, [if_empty])

A real example — pulling only the sales rows where the region is "NSW":

=FILTER(A2:D200, C2:C200="NSW", "No matches")

Here, A2:D200 is the full data range you want returned, C2:C200="NSW" is the condition (only rows where column C equals "NSW"), and "No matches" is what shows if nothing matches, instead of an ugly error.

You can combine multiple conditions with * (works like AND) or + (works like OR):

=FILTER(A2:D200, (C2:C200="NSW")*(D2:D200>1000), "No matches")

This pulls only rows where the region is NSW and the value is over $1,000.

Why doesn't my FILTER formula spill properly?

This is the most common issue people run into, so worth covering directly. The usual causes:

1. There's data in the way. FILTER needs empty cells below and to the right to "spill" its results into. If there's anything sitting in those cells, you'll get a #SPILL! error.
2. Your condition range and array range are different sizes. If your data is A2:D200 but your condition is only C2:C150, Excel doesn't know how to line them up.
3. You're using an older Excel version. FILTER only works in Microsoft 365 and Excel 2021 onward — it won't appear at all in Excel 2019 or earlier.
4. You've typed it inside a Table object incorrectly. Dynamic arrays can behave a bit differently inside official Excel Tables versus a plain range, and sometimes it's simpler to convert the Table to a normal range first.

How does SORT work, and how is it different from the Sort button?

SORT rearranges data by a column you choose, but unlike the ribbon's Sort button, it doesn't touch your original data at all — it creates a live, separate sorted copy that updates automatically if the source changes.

=SORT(array, [sort_index], [sort_order])

Sorting a client list alphabetically by name (assuming names are in the first column):

=SORT(A2:B50, 1, 1)

The 1 for sort_index means "sort by the first column," and the second 1 means ascending order (use -1 for descending). This is genuinely handy when you want a sorted view of something without permanently reordering the original data underneath it — useful if other formulas or a colleague depend on the original row order staying put.

What's UNIQUE for, exactly?

UNIQUE returns a list of distinct values from a range, removing duplicates without permanently altering anything. It's the dynamic-array replacement for the "Remove Duplicates" button, except it doesn't delete anything from your source data — it just gives you a clean, live list elsewhere.

=UNIQUE(range)

Getting a list of every distinct client name from a column that has repeats:

=UNIQUE(A2:A200)

Add a second argument to find rows that appear exactly once, which is a genuinely underused trick:

=UNIQUE(A2:A200, FALSE, TRUE)

How do you combine FILTER, SORT and UNIQUE together?

This is where it gets properly useful — you can nest them inside each other to do several things in one formula instead of building separate helper columns for each step.

Going back to that Friday afternoon request — unique NSW client names, sorted alphabetically, in one formula:

=SORT(UNIQUE(FILTER(A2:A200, B2:B200="NSW")), 1, 1)

Reading it from the inside out: FILTER grabs only the NSW rows, UNIQUE strips out any repeated names within that filtered list, and SORT arranges what's left alphabetically. One formula, no helper columns, and it updates itself the moment the source data changes.

Building a simple two-way lookup dashboard with dynamic arrays

If you've read my post on <a href="https://www.ourwealth.com.au/2026/08/index-match-formula-explained.html">INDEX/MATCH</a>, you'll remember the two-way lookup example — finding the value where a row and a column meet. Dynamic arrays give you a slightly different, more flexible way to build something similar as a small interactive dashboard.

Say you've got a raw data table of sales by client and by month, and you want a simple dashboard where someone can pick a client from a dropdown and instantly see their sales, sorted by month, with no duplicates in the client list to choose from.

1. In one cell, use =UNIQUE(A2:A200) to build a clean dropdown source of client names, then set up Data Validation pointing to that spilled list.
2. In your dashboard cell where someone selects a client, reference that selection (say, cell F1).
3. Use =SORT(FILTER(A2:C200, A2:A200=F1), 3, 1) to pull every row matching the selected client, sorted by date.

The whole dashboard updates instantly whenever someone changes the dropdown, without a single manual filter or sort click, and without touching a pivot table.

FAQ

Do FILTER, SORT and UNIQUE work in Excel 2019? No. These are dynamic array functions available only in Microsoft 365 and Excel 2021 onward. If you're on Excel 2019 or earlier, you'll need to use Advanced Filter, the Sort ribbon button, and Remove Duplicates as the manual equivalents.

What does #SPILL! mean and how do I fix it? It means the formula's result has nowhere empty to expand into. Clear out any cells below or to the right of the formula, or move the formula somewhere with more open space.

Can I use FILTER with more than one condition? Yes — multiply conditions together with * for an AND relationship, or add them with + for an OR relationship, as shown in the NSW-and-over-$1,000 example above.

Does UNIQUE remove duplicates from my original data? No, and this is the main advantage over the Remove Duplicates button — UNIQUE creates a separate, live list without touching your source data at all.

Should I use dynamic arrays instead of pivot tables now? Not necessarily instead of — they're good for different things. Pivot tables are still better for quick summarising and grouping large datasets, especially with subtotals. Dynamic arrays shine when you want a live, formula-driven result (like a dashboard) that updates automatically without anyone needing to click "Refresh.".  See my beginner's guide to pivot tables for when to reach for those instead.