UNIQUE and FILTER: lists that update themselves
=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)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Team | Score | Teams | |
| 2 | Ann | Red | 55 | Red | |
| 3 | Bob | Blue | 40 | Blue | |
| 4 | Cy | Red | 70 | Green | |
| 5 | Di | Green | 35 | ||
| 6 | Ed | Blue | 60 | ||
| 7 | Fay | Red | 48 | ||
| 8 | Gus | Green | 82 | ||
| 9 | Hal | Blue | 51 |
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
In E2, work out how many different customers placed orders. Use UNIQUE so the count stays right if the list changes.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Customer | Value | How many customers? | |
| 2 | 1001 | Harbour Catering | £120.00 | ||
| 3 | 1002 | Ashby Stationers | £80.00 | ||
| 4 | 1003 | Harbour Catering | £95.00 | ||
| 5 | 1004 | Northgate Supplies | £150.00 | ||
| 6 | 1005 | Ashby Stationers | £60.00 | ||
| 7 | 1006 | Harbour Catering | £210.00 | ||
| 8 | 1007 | Kestrel Print | £45.00 | ||
| 9 | 1008 | Northgate Supplies | £130.00 |