How to Extract Unique Values (and Count Them) in Excel
You want the distinct values of a column — maybe with a tally of how often each shows up. UNIQUE() needs 365, Remove Duplicates destroys your data, and counts mean a wall of COUNTIF. Here's the one-click way that does all three safely.
Last updated: August 2026
| Column | Unique + count (by frequency) | |
|---|---|---|
| Apple, Pear, Apple, | → | Apple · 3 |
| Orange, Apple, Pear | → | Pear · 2 |
| → | Orange · 1 |
Why the built-in ways fall short
=UNIQUE(A2:A100) is the clean answer, but it only exists in Excel 365 and the web. Data ▸ Remove Duplicates works everywhere — but it deletes rows in place, so you lose the full data unless you copied it first. And neither one counts: to know that Apple appears three times you add a COUNTIF for every value, or build a PivotTable. Three tools for one small job.
The 1-click way — Tellsheet's Extract Unique Values
Select the column and run Extract Unique Values. It writes the distinct values to a new sheet — your original data is never touched. Turn on Add a count column to see how many times each appears, and set the order: as first seen, A → Z, Z → A, or most frequent first. Blank cells can be ignored. Pairs with Remove Duplicates and Group & Summarize.
Frequently asked questions
Do I need Excel 365 or UNIQUE?
No — it works on any Excel version and doesn't rely on the UNIQUE formula.
Can it count each value?
Yes — turn on the count column, then sort by "Most frequent first" if you like.
Will it delete my rows?
No. Unlike Remove Duplicates, the list goes to a new sheet and your data stays whole.
What order can I get?
As first seen, A → Z, Z → A, or most frequent first.
Related Excel guides
Extract unique values in one click
Extract Unique Values lists a column's distinct entries — with optional counts, sorted your way — on a new sheet, without deleting a thing.
Get Tellsheet free See pricing