UNIQUE returns the distinct values from a range and spills them into the cells below. Unlike the Remove Duplicates button, it does not change your data and it updates by itself when the data changes. It is available in Microsoft 365 and Excel 2021 or newer.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
- array: the range you want to check.
- by_col: leave it out for a normal column list. Use TRUE to compare across columns.
- exactly_once: TRUE returns only items that appear one time. Default is FALSE.
Get a distinct list of departments
If departments sit in A2:A500, type this in one cell:
=UNIQUE(A2:A500)
Excel fills the list downwards on its own. Do not type anything in the cells below, or you will get a #SPILL! error.
Count how many unique items you have
Wrap UNIQUE inside COUNTA:
=COUNTA(UNIQUE(A2:A500))
This is the quickest way to answer questions like how many different customers placed an order this month.
Find items that appear only once
=UNIQUE(A2:A500,,TRUE)
Two commas in a row skip the second argument. This is useful to find a customer who bought only a single time.
Sort the unique list
Put SORT around it for an alphabetical list:
=SORT(UNIQUE(A2:A500))
Unique rows across two columns
If you select two columns, for example A2:B500, UNIQUE compares the full row. A repeated pair of Region and Product is removed, but the same Region with a different Product is kept.
Common mistakes
- Blank cells inside the range come back as 0. Use a range without blanks or wrap with FILTER to remove them.
- UNIQUE treats "Amit" and "amit" as the same value, because it is not case sensitive.
- Extra spaces make two items look different, so clean the data with TRIM first.