To turn a messy column with repeats into a clean, alphabetized list of distinct values — perfect for a drop-down source or a report row header — wrap UNIQUE in SORT. One formula, and it updates itself as data changes.
UNIQUE removes duplicates; SORT orders the result. Both spill automatically into the cells below.
The example
A column of regions with repeats becomes a clean, sorted list.
| A | C | ||
|---|---|---|---|
| 1 | Region | Unique, sorted | |
| 2 | West | East | |
| 3 | East | South | |
| 4 | West | West | |
| 5 | South | ||
| 6 | East |
The formula
Entered once in C2, the clean list spills down C:
How it works
Two dynamic-array functions, nested:
UNIQUE(A2:A6)returns each region once: West, East, South (in first-seen order).SORT(…)alphabetizes that result → East, South, West.- The output spills into the cells below C2 automatically; you enter the formula only once.
- Because it’s a live formula, adding a new region to column A updates the list instantly — ideal as a drop-down’s source range (
=C2#).
Try it: interactive demo
Type values (commas); see the unique, sorted result.
Variations
Sorted descending
Add -1 for Z–A or largest-first:
Unique values that meet a condition
Filter first — distinct products in the West region:
Use as a dynamic drop-down source
Point Data Validation at the spill range with the # operator: =$C$2# — the list grows and shrinks on its own.
Pitfalls & errors
#SPILL! The result needs empty cells below to spill into. Clear anything blocking the column.
Blanks become a "unique" empty. A blank in the source shows up as a 0 or empty item. Filter them out: =SORT(UNIQUE(FILTER(A2:A6, A2:A6<>""))).
Excel 365 / 2021 only. UNIQUE and SORT don’t exist in Excel 2019 or older (#NAME?). Use Remove Duplicates + Sort, or a PivotTable, there.
Practice workbook
Frequently asked questions
How do I create a unique sorted list in Excel?
How do I make a dynamic drop-down from a unique list?
How do I get a unique list in Excel 2019?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class