Confident Cells

UNIQUE and FILTER: lists that update themselves

20 November 2026 · 2 min read

=UNIQUE(B2:B9) lists every different value in a column once. =FILTER(A2:A9,C2:C9>=50) lists only the rows that match a condition. Both "spill": you type the formula in one cell and the answers fill the cells below. They need Excel 365, Excel 2021 or later, or Google Sheets.

UNIQUE: each value once

A sign-up list has a team on every row. Which teams are there?

=UNIQUE(B2:B9)
ABCDE
1NameTeamScoreTeams
2AnnRed55Red
3BobBlue40Blue
4CyRed70Green
5DiGreen35
6EdBlue60
7FayRed48
8GusGreen82
9HalBlue51
Only E2 holds a formula. Blue and Green spill into E3 and E4.

Wrap it in COUNTA to count the different values: =COUNTA(UNIQUE(B2:B9)) gives 3. Wrap it in SORT for an A to Z list: =SORT(UNIQUE(B2:B9)).

FILTER: only the rows that match

Who scored 50 or more?

=FILTER(A2:A9,C2:C9>=50)

The first part is what to show, the second is the condition. Ann (55), Cy (70), Ed (60), Gus (82) and Hal (51) spill down. For two conditions that must both be true, multiply them: =FILTER(A2:A9,(B2:B9="Red")*(C2:C9>=50)).

If nothing matches, FILTER shows #CALC!. Give it a third part to show instead: =FILTER(A2:A9,B2:B9="Pink","None").

Why spilling is useful

Add a new row to the list and the answers update, with no copying down. And to point at the whole spilled answer from another formula, put # after the first cell: =COUNTA(E2#).

Common mistakes

#SPILL!

Something is in the way of the cells below. Clear them: see the #SPILL! error.

#NAME?

Your version of Excel is older than 2021 and doesn't have UNIQUE or FILTER. Use Remove Duplicates and AutoFilter on the Data tab instead.

Different-sized ranges

In FILTER, the condition must cover the same rows as what you show: A2:A9 with C2:C9, not C2:C20.

Try it now

Your turn

In E2, work out how many different customers placed orders. Use UNIQUE so the count stays right if the list changes.

ABCDE
1OrderCustomerValueHow many customers?
21001Harbour Catering£120.00
31002Ashby Stationers£80.00
41003Harbour Catering£95.00
51004Northgate Supplies£150.00
61005Ashby Stationers£60.00
71006Harbour Catering£210.00
81007Kestrel Print£45.00
91008Northgate Supplies£130.00