UNIQUE: Extract Unique Values (2025)
Quick Answer: UNIQUE removes duplicates: =UNIQUE(A2:A100) returns distinct list. For unique rows: =UNIQUE(A2:C100) all columns. Exactly once only: =UNIQUE(A2:A100,,TRUE) excludes duplicates. Combine with SORT: =SORT(UNIQUE(data)) for sorted unique list. Updates automatically when data changes.
What is UNIQUE?
UNIQUE is a dynamic array function in Excel 365 that extracts unique values or unique rows from a range. Automatically removes duplicates and updates when source data changes.
⚠️ Excel 365 Only
UNIQUE requires Excel 365 subscription with dynamic arrays. Not available in Excel 2019, 2016, or earlier.
Alternative for older Excel: Use Remove Duplicates (Data tab) or Advanced Filter with "Unique records only".
Before & After:
Original Data (with duplicates):
Apple
Banana
Apple
Cherry
Banana
Apple
Date
7 items (4 unique)
UNIQUE Result:
=UNIQUE(A2:A8)
Apple
Banana
Cherry
Date
4 unique items
UNIQUE vs Distinct vs Exactly Once:
Distinct (Default)
Returns unique list (removes duplicates). Apple appears 3x → shown once.
Exactly Once
Returns values appearing ONLY once. Apple appears 3x → excluded. Only Cherry, Date shown.
Common Use Cases:
✓ Dropdown Lists
Extract unique categories, regions, products for data validation
✓ Data Deduplication
Remove duplicate customer emails, order IDs, names
✓ Summary Lists
Create unique lists for reporting, analysis, dashboards
✓ Unique Rows
Find unique combinations: Name + Email + Phone
UNIQUE Features:
✓ Advantages:
- • Automatic updates with data changes
- • Works on single or multiple columns
- • Formula-based (auditable)
- • Preserves original data order
- • Combine with SORT, FILTER
- • Case-insensitive by default
✗ Limitations:
- • Excel 365 only
- • Needs spill space (empty cells)
- • Can't customize sort order
- • Case-insensitive (Apple = APPLE)
- • No count of duplicates removed
💡 Pro Tips
- • Sort unique list: =SORT(UNIQUE(A2:A100)) for alphabetical order
- • Count unique: =COUNTA(UNIQUE(A2:A100)) for unique count
- • Filter then unique: =UNIQUE(FILTER(data, criteria)) for conditional unique lists
- • Original order preserved: First occurrence position maintained
UNIQUE Syntax
=UNIQUE(array, [by_col], [exactly_once])
Arguments
array (required)
Range or array to extract unique values from
by_col (optional, default FALSE)
Compare columns instead of rows
FALSE or omitted: Compare rows (most common)
TRUE: Compare columns (rare use case)
exactly_once (optional, default FALSE)
Return only values appearing exactly once
FALSE or omitted: Return distinct list (remove duplicates)
TRUE: Exclude any value that appears more than once
Common Patterns
Unique List (Distinct)
=UNIQUE(A2:A100)
Remove duplicates, keep one of each
Unique Rows (Multiple Columns)
=UNIQUE(A2:C100)
Unique combinations across all columns
Exactly Once Only
=UNIQUE(A2:A100,,TRUE)
Exclude any duplicates, singles only
Sorted Unique List
=SORT(UNIQUE(A2:A100))
Unique values in alphabetical order
Distinct vs Exactly Once
Sample Data: Apple, Banana, Apple, Cherry, Banana, Date
Distinct (Default)
Result:
Apple
Banana
Cherry
Date
All unique values (4 items)
Exactly Once
Result:
Cherry
Date
Only singles (2 items) - excludes Apple, Banana
Unique Values vs Unique Rows
Single Column (Unique Values)
Compares each cell individually. Returns unique list of values.
Multiple Columns (Unique Rows)
Compares entire rows. Returns rows with unique combinations of all columns.
Example: John + john@email.com different from John + john2@email.com
⚠️ Important Notes
- • Excel 365 only: Not available in Excel 2019 or earlier
- • Case insensitive: "Apple" = "apple" = "APPLE" (treated as same)
- • Spills automatically: Results fill multiple cells - ensure space is clear
- • Order preserved: First occurrence order maintained in result
- • Blanks included: Empty cells count as unique value (appears once in result)
- • #SPILL! error: Blocked by data - clear cells or move formula
- • Updates automatically: When source data changes, unique list updates
- • Numbers = text: 123 (number) different from "123" (text)
10+ UNIQUE Examples
Basic UNIQUE
1. Unique List
=UNIQUE(A2:A100)
Remove duplicates from single column
2. Unique Categories
=UNIQUE(B2:B100)
Extract unique categories/regions/departments
3. Sorted Unique List
=SORT(UNIQUE(A2:A100))
Alphabetical unique list
Unique Rows (Multiple Columns)
4. Unique Name + Email
=UNIQUE(A2:B100)
Unique person records (both columns)
5. Unique Transactions
=UNIQUE(A2:D100)
Unique combinations across all columns
6. Unique Customer Orders
=SORT(UNIQUE(A2:C100), 1)
Unique rows sorted by first column
Exactly Once (Singles Only)
7. Non-Duplicate Values
=UNIQUE(A2:A100,,TRUE)
Only values appearing once (exclude any duplicates)
8. Single Occurrence Orders
=UNIQUE(A2:A100,,TRUE)
Find items ordered only once
Advanced Applications
9. Unique from Filtered Data
=UNIQUE(FILTER(A2:A100, B2:B100>500))
Unique list from filtered subset
10. Count Unique Values
=COUNTA(UNIQUE(A2:A100))
Number of unique items
11. Unique for Data Validation
=SORT(UNIQUE(Table1[Category]))
Dropdown list source (sorted)
🎯 Pro Tips
- • Use with data validation: =SORT(UNIQUE(...)) for dropdown lists
- • Combine with FILTER: Unique values from filtered subset
- • Count unique: =COUNTA(UNIQUE(...)) for distinct count
- • Exactly once for anomalies: Find items appearing only once
- • Multiple columns: Unique combinations across all columns
- • Named ranges: =UNIQUE(SalesData[Product]) with tables
- • Case insensitive: Remember "Apple" = "APPLE"
Frequently Asked Questions
What's the difference between UNIQUE and Remove Duplicates?
UNIQUE is a formula that creates dynamic list - updates automatically when source changes. Remove Duplicates (Data tab) is manual operation that permanently deletes duplicates from original data. UNIQUE: non-destructive, formula-based, auditable. Remove Duplicates: destructive, manual, can't undo after saving. Use UNIQUE for reports/dashboards, Remove Duplicates for one-time cleanup.
How do I make UNIQUE case-sensitive?
UNIQUE is case-insensitive by default (Apple = APPLE). No built-in case-sensitive option. Workaround: Add helper column with EXACT or concatenate with binary value. Advanced: Use BYROW with EXACT comparisons. For most use cases, case-insensitive behavior is desired - 'John' and 'JOHN' should be treated as same person.
Can I get unique values from multiple non-adjacent ranges?
Yes, use array constants or VSTACK (Excel 365). Method 1: ={A2:A100;C2:C100;E2:E100} then wrap in UNIQUE. Method 2 (better): =UNIQUE(VSTACK(A2:A100, C2:C100, E2:E100)). VSTACK combines ranges vertically, then UNIQUE removes duplicates from combined list.
What does exactly_once parameter do?
exactly_once=TRUE returns only singles (values appearing exactly one time). Default (FALSE) returns distinct list. Example: Data {A,B,B,C,C,D} → UNIQUE (default) = {A,B,C,D}. UNIQUE(...,TRUE) = {A,D} (only singles, excludes B and C because they repeat). Use for finding anomalies or one-time occurrences.
How do I count duplicates removed by UNIQUE?
Compare original count to unique count: =COUNTA(A2:A100) - COUNTA(UNIQUE(A2:A100)). Example: 100 total values, 75 unique = 25 duplicates removed. For duplicate count per item, use COUNTIF: =COUNTIF(A:A, A2) then filter where count>1. UNIQUE doesn't provide duplicate counts directly.
Can UNIQUE work with errors in the data?
UNIQUE treats each error as unique value. #N/A, #DIV/0!, #VALUE! all appear in result. Filter errors first: =UNIQUE(FILTER(A2:A100, NOT(ISERROR(A2:A100)))). Alternative: Use IFERROR in source data to replace errors with blanks or 'Error' text before applying UNIQUE.