Excel UNIQUE: Distinct Values and Spill Lists
By Szabó Gergő · Updated
UNIQUE spills the distinct values from a range. It is the fastest way to build a clean list for data validation, summaries, or FILTER criteria.
Syntax and arguments
=UNIQUE(array, [by_col], [exactly_once])- array
- The range that contains duplicates.
- by_col
- FALSE (default) compares rows; TRUE compares columns.
- exactly_once
- TRUE keeps values that appear only once; FALSE keeps first of each duplicate set.
UNIQUE examples
Names repeat in A2:A500.
=SORT(UNIQUE(A2:A500))UNIQUE removes duplicates and SORT orders the spill list.
Ticket IDs are in B2:B200.
=UNIQUE(B2:B200,FALSE,TRUE)exactly_once TRUE drops IDs that were entered more than once.
Region and owner are in A2:B200.
=UNIQUE(A2:B200)A row is unique when the combination of both columns is unique.
Common mistakes
Hidden spaces create fake uniques
TRIM the source or wrap UNIQUE around TRIM on a helper range.
Header included in the array
Start at row 2 or the header will appear as a distinct item.
#SPILL! on a table column
Place UNIQUE outside the table or use a clear spill area.
UNIQUE FAQ
Can UNIQUE ignore blanks?
Wrap with FILTER, such as =UNIQUE(FILTER(A2:A100,A2:A100<>"")).
Does case matter?
UNIQUE is not case-sensitive in current Excel, so East and EAST count as one value.
How do I count unique items?
Use =COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>""))) or COUNTA of the spill range.