Home › Excel functions › UNIQUE
UNIQUE Function in Excel: Remove Duplicates with a Formula
UNIQUE returns a list of the distinct values in a range, without duplicates. Unlike the Remove Duplicates button, it's a formula: the list updates automatically when your data changes.
Excel CSE · 2 min readIn this guide
UNIQUE syntax
=UNIQUE(array, [by_col], [exactly_once])| Argument | What it means |
|---|---|
| array | The range to get unique values from. |
| by_col (optional) | FALSE (default) compares rows; TRUE compares columns. |
| exactly_once (optional) | TRUE returns only values that appear exactly once. Default is FALSE: every distinct value. |
Availability: Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. For older versions, see the alternative below.
Basic example
| A | B | C | |
|---|---|---|---|
| 1 | Region | Unique | |
| 2 | North | North | |
| 3 | South | South | |
| 4 | North | East | |
| 5 | East | ||
| 6 | South |
Entered in C2, this spills North, South, East into C2:C4, in the order they first appear. UNIQUE isn't case-sensitive: "north" and "North" count as the same value.
A sorted unique list
=SORT(UNIQUE(A2:A6))Returns East, North, South. That's a handy, self-updating source for a dropdown list (Data › Data Validation › List, with =$C$2# as the source; the # refers to the whole spilled range).
Count unique values
=COUNTA(UNIQUE(A2:A6))Returns 3: there are three different regions. If the range may contain blanks, filter them out first: =COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>""))).
More methods, including older Excel versions and counting with a condition: count unique values.
Values that appear only once
=UNIQUE(A2:A6, FALSE, TRUE)Returns only East: North and South both appear twice. Useful for spotting one-off entries, like customers who only ordered once.
Unique combinations across columns
Give UNIQUE several columns and it returns unique rows. =UNIQUE(A2:B100) on a region and rep column returns each region/rep pair once.
In older Excel versions
- Remove Duplicates: copy the column, then Data › Remove Duplicates. Not live, but quick.
- Advanced Filter: Data › Advanced, choose "Copy to another location" and tick "Unique records only".
- Pivot table: put the field in Rows; a pivot table always shows each value once.
Frequently asked questions
Why does UNIQUE show #SPILL!?
The cells where the result needs to go aren't empty. Clear them and the list appears.
Why does UNIQUE include a 0 at the end?
Your range contains empty cells, which UNIQUE returns as 0. Filter them out with UNIQUE(FILTER(range, range<>"")).
Is UNIQUE case-sensitive?
No. Values that differ only in upper and lower case are treated as the same.
What's the difference between UNIQUE and Remove Duplicates?
Remove Duplicates permanently deletes rows from your data. UNIQUE leaves the data alone and creates a live list elsewhere that updates when the data changes.