Get a list of unique values
Make a de-duplicated, sorted list from a column that updates automatically.
The formula
Excel and Google Sheets · Excel 365 and Excel 2021
=SORT(UNIQUE(FILTER(A2:A100, A2:A100<>"")))How it works
FILTER(A2:A100, A2:A100<>"")Drops empty cells first so you don't get a 0 or blank in the list.
UNIQUE(…)Keeps one copy of each value.
SORT(…)Sorts A to Z. Use SORT(…, 1, -1) for Z to A.
Example
| A: City | C: Result |
|---|---|
| Paris | Berlin |
| Berlin | Lisbon |
| Paris | Paris |
| Lisbon |
Tips
- In older Excel, use Data > Remove Duplicates on a copy of the column instead.