📎 This article includes 1 downloadable practice file ↓
A customer list pulled from three months of orders has the same names again and again. Before you send a mailer or build a master, you need each customer once. Excel has three ways to do it. They give different results when the data is messy, so it’s worth knowing which one you’re using.
In this article
Way 1: the Remove Duplicates button
Click inside the data, then Data › Remove Duplicates. Tick the columns that define a duplicate:
- Only Customer ticked: keeps the first row of each customer.
- Customer + City ticked: the same name in two cities counts as two.
- All columns: removes only rows identical in every column.
Excel reports “7 duplicate values found and removed; 20 unique values remain”.
Way 2: Advanced Filter, unique records only
Data › Advanced (in the Sort & Filter group). Choose Copy to another location, pick a destination cell, and tick Unique records only. The original stays untouched and the unique list appears where you chose. Use this in older Excel when you want to keep the source data.
Way 3: formulas (live, updates by itself)
Excel 365 and 2021:
=UNIQUE(A2:A28) ' each name once
=SORT(UNIQUE(TRIM(A2:A28))) ' cleaned and sorted
=UNIQUE(A2:B28) ' unique Customer + City pairs
=UNIQUE(A2:A28, , TRUE) ' names that appear exactly once
=ROWS(UNIQUE(TRIM(A2:A28))) ' how many distinct customersAny version: flag the first time each value appears, then filter on TRUE.
=COUNTIF($A$2:A2, A2) = 1 ' TRUE on first occurrence
=COUNTIF($A$2:$A$28, A2) > 1 ' TRUE on every value that repeats
=SUMPRODUCT(1 / COUNTIF(A2:A28, A2:A28)) ' distinct count in old ExcelWhich one to use
| Situation | Use |
|---|---|
| One-off clean-up of an export | Remove Duplicates button (on a copy) |
| Keep the source, older Excel | Advanced Filter |
| List must update as data grows | UNIQUE formula |
| Need to review duplicates before deleting | COUNTIF flag + filter, or conditional formatting › Highlight Duplicate Values |
| Thousands of rows from many files, every month | Power Query › Remove Duplicates (repeatable on refresh) |
The messy-data trap
“Sharma Traders” and “Sharma Traders ” (trailing space) look identical but are different to all three methods. Case is the opposite: Remove Duplicates, UNIQUE and COUNTIF all treat “sharma traders” and “Sharma Traders” as the same. Clean first:
=PROPER(TRIM(CLEAN(A2)))Put this in a helper column, paste it back as values, then remove duplicates. In the practice file, the raw list shows 10 distinct names but only 8 real customers.
Keep the latest row, not the first
Remove Duplicates always keeps the first occurrence. To keep each customer’s latest order, sort by date newest-first, then remove duplicates. In Excel 365, =XLOOKUP(name, names, values, , 0, -1) searches from the bottom.
Where people go wrong
| Mistake | Result |
|---|---|
| Running it on the only copy of the data | Rows lost for good once the file is saved |
| Ticking all columns when you meant one | Nothing removed (dates or amounts differ) |
| Ignoring spaces and stray characters | Duplicates survive |
| Selecting one column only | Excel asks to expand; choosing “continue with current selection” misaligns rows |
Practice
Download the practice workbook below. The Customers sheet has 27 orders with repeats, two trailing spaces and one lower-case name. Each task has an automatic ✓ check, and the Answers sheet has a working formula if you get stuck.
See also: find duplicates across columns.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Remove duplicates practice workbook27 orders with repeats, stray spaces and a lower-case name; 10 counting and cleaning tasks with checks, plus steps for the other two methods.⬇ XLSX · 14 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.