Excel UNIQUE: Extract Distinct Values Automatically
UNIQUE returns a dynamic list of distinct values from a range. No more Remove Duplicates button or helper columns.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Get a unique list of departments from column B"
=UNIQUE(B2:B100)UNIQUE scans B2:B100 and returns each distinct value once. The result spills into adjacent cells and updates automatically when the source data changes.
Step-by-step breakdown
- UNIQUE(array, [by_col], [exactly_once]) extracts distinct values
- B2:B100 is the source range to deduplicate
- by_col (optional): TRUE = compare columns, FALSE/omitted = compare rows
- exactly_once (optional): TRUE = only values appearing once, FALSE/omitted = all unique values
- Results spill automatically
Edge cases & warnings
- Empty cells in the range are treated as a value — you may get a blank in results
- UNIQUE is case-insensitive: 'Sales' and 'sales' are treated as the same
- Wrap in SORT for alphabetical order: =SORT(UNIQUE(B2:B100))
- Requires Excel 365 or Excel 2021+
Examples
"Sales, Marketing, Sales, HR, Marketing"
Sales, Marketing, HR"=UNIQUE(B2:B100, , TRUE)"
Only values appearing exactly onceFrequently asked questions
How do I count unique values?
Wrap in COUNTA: =COUNTA(UNIQUE(B2:B100)). Or for older Excel: =SUMPRODUCT(1/COUNTIF(B2:B100, B2:B100)).
Can UNIQUE work across multiple columns?
Yes. =UNIQUE(A2:C100) returns rows that are unique across all three columns combined.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.