Excel UNIQUE: Extract Distinct Values Automatically
UNIQUE Function (Excel) is a Excel function that unique scans b2:b100 and returns each distinct value once. Formula Genius generates and validates this formula automatically from a plain-English prompt.
UNIQUE returns a dynamic list of distinct values from a range. No more Remove Duplicates button or helper columns.
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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.