TL;DR —
UNIQUEreturns the distinct values from a range and spills them into a self-updating list: change the source and the list re-runs. That's the whole difference from the Remove Duplicates button, which edits your data once and forgets. Syntax:=UNIQUE(array, [by_col], [exactly_once]). The one real trap: the third argument is not "remove duplicates" — set to TRUE it returns values that appear exactly once (dropping anything that ever repeated). To count distinct values, wrap it:=COUNTA(UNIQUE(range)). Excel 365 and 2021+.
=UNIQUE(Region) ' each region once, as a live list
=COUNTA(UNIQUE(Region)) ' how many distinct regions
Deduplicating a column is one of the oldest chores in Excel, and for years the
only tool was Data > Remove Duplicates — a one-way, destructive edit. UNIQUE
replaces it with a formula that never touches the original and refreshes itself.
The catch is that one of its arguments does something most people don't expect.
What you'll learn
- Why
UNIQUEis a mirror and Remove Duplicates is surgery - The
exactly_oncetrap — and why it isn't a dedup switch - How to count distinct values correctly
- Deduplicating rows vs single columns
- Why
UNIQUEalmost always lives insideSORTor a dropdown
The mental model: a mirror, not surgery
Remove Duplicates is surgery: it cuts rows out of your actual data, it's
manual, and the moment new data arrives the result is stale — you have to run it
again. UNIQUE is a mirror: it sits in its own cells and reflects the
distinct values of the source. Add a new region to the source and the mirror
updates on the next recalculation. Crucially, it never modifies the original
range, so the raw data stays raw — exactly what you want when the source is a
table you'll keep appending to.
' Source A2:A100 has duplicates; this never changes A2:A100:
=UNIQUE(A2:A100) ' spills the distinct values into a new range
The rule that prevents the worst bug: exactly_once is not dedup
This is the trap that produces wrong answers silently. UNIQUE takes a third
argument, exactly_once. People read the name and assume TRUE means "give me the
deduplicated list." It does not. With exactly_once set to TRUE, Excel
returns only the values that appear exactly one time in the source — and
drops every value that was ever repeated.
' Source: West, West, East, North (West appears twice)
=UNIQUE(Region) ' -> West, East, North (distinct list)
=UNIQUE(Region, , TRUE) ' -> East, North (West DROPPED — it repeated)
If you wanted a clean list of regions and you flipped that argument on, West
vanishes and you never notice until a total comes up short. Rule: for ordinary
deduplication, leave the third argument empty. Only set exactly_once to TRUE
when your actual question is "which values occurred once and only once" — finding
one-off transactions, say. The second argument, by_col, you can almost always
ignore; it only matters when your data runs across columns instead of down rows.
Counting distinct values the right way
There's no COUNTUNIQUE function, and this is the cleanest replacement.
UNIQUE gives you the distinct list; COUNTA counts how many items are in it:
=COUNTA(UNIQUE(Region)) ' number of distinct regions
=COUNTA(UNIQUE(FILTER(Region, Sales > 0))) ' distinct regions that had a sale
This reads better and updates live, unlike the old
SUMPRODUCT(1/COUNTIF(...)) incantation people still copy from forum posts.
Distinct rows, not just one column
Hand UNIQUE more than one column and it returns distinct combinations —
the equivalent of "remove duplicate rows" across those columns:
=UNIQUE(A2:B100) ' each distinct (col A, col B) pair, once
That's how you build a clean lookup table of, say, every Region + Salesperson pairing that actually exists, straight from the transaction log.
The judgment call: UNIQUE rarely stands alone
Here's the honest observation after using it for real: a bare UNIQUE is almost
never the finished answer. Distinct values are usually raw material. Two
destinations cover almost every case. First, sort them so the list is readable —
=SORT(UNIQUE(Region)) — covered in the SORT guide.
Second, feed them to a Data Validation dropdown so the choices grow with the
data: put =UNIQUE(Region) in a spare column, then point the dropdown's source
at the spill with =$E$2#. If you find yourself with a lone UNIQUE and no plan
for it, you probably wanted a dynamic dropdown. And when you only want the
distinct values that also meet a condition, that's
FILTER nested inside:
=UNIQUE(FILTER(Customer, Region = "West")).
How ExcelMaster helps
Most "give me the unique X" tasks are really "build me a clean, live reference
list" — distinct customers, the set of regions that traded this month, a
dedup'd category column for a pivot. ExcelMaster writes the right
combination — SORT(UNIQUE(...)), a COUNTA for the count, or a spill-fed
dropdown — from a plain-English request, and points it at the source you name.
It also knows when you actually mean exactly_once and asks rather than guessing.
Frequently asked questions
What is the difference between UNIQUE and Remove Duplicates?
UNIQUE is a formula that returns a distinct list and updates automatically when
the source changes, without altering the original data. Remove Duplicates is a
one-time, destructive edit on your actual cells that you must re-run whenever data
changes.
How do I count unique values in Excel?
Wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(range)). To count distinct values
that also meet a condition, nest FILTER:
=COUNTA(UNIQUE(FILTER(range, condition))).
What does the third argument in UNIQUE do?
It's exactly_once. Set to TRUE it returns only values that appear exactly one
time and drops anything that repeated — it is not a deduplication switch. For
a normal distinct list, leave it empty.
Does UNIQUE work in Excel 2016 or 2019?
No. UNIQUE needs Excel 365 or Excel 2021+. On older versions use Remove
Duplicates, an Advanced Filter, or a COUNTIF-based formula instead.
Tested in
Tested in: Excel 365 (Windows 11) — last verified 2026-06-16.
Related guides: Excel FILTER · Excel SORT · XLOOKUP in Excel