UNIQUE Function in Excel: Get a List of Distinct Values
Use UNIQUE in Excel 365 and Google Sheets to list distinct values, count them, sort them, and build automatic summary tables that grow with your data.
By Ayoub Farid · · 1 min read
UNIQUE returns a list of distinct values from a range, and it updates automatically when the data changes. It's the easiest way to get a list of categories, customers or products.
Available in Microsoft 365, Excel 2021+, Excel for the web and Google Sheets.
Syntax
=UNIQUE(array, [by_col], [exactly_once])Basic example
With categories in C2:C200:
=UNIQUE(C2:C200)The results spill down from the formula cell. Don't type anything in the cells below, or you'll get #SPILL!.
Sorted list
=SORT(UNIQUE(C2:C200))Count distinct values
=COUNTA(UNIQUE(C2:C200))If the range has blank cells, UNIQUE includes one blank (shown as 0). Filter them out:
=UNIQUE(FILTER(C2:C200,C2:C200<>""))Values that appear only once
=UNIQUE(C2:C200,FALSE,TRUE)The third argument TRUE returns items that occur exactly once.
Automatic summary table
- In F2:
=SORT(UNIQUE(C2:C200)) - In G2:
=SUMIFS(D2:D200,C2:C200,F2#)
The F2# means "the whole spilled range starting in F2", so totals extend as new categories appear. See SUMIFS.
Unique rows across several columns
=UNIQUE(A2:B200)Returns each distinct combination of A and B.
Older Excel
Without UNIQUE, use Data > Remove Duplicates on a copy, or a pivot table. See remove duplicates.
Frequently asked questions
How do I get unique values in Excel?
=UNIQUE(range) in Excel 365/2021, or Data > Remove Duplicates in older versions.
How do I count unique values?
=COUNTA(UNIQUE(range)).
Why does UNIQUE show #SPILL!?
Cells below the formula aren't empty. Clear them.
Does Google Sheets have UNIQUE?
Yes, and it works the same way.