Excel has a Remove Duplicates button. It works, it is fast, and it is the right tool for a specific kind of duplicate. It is also the reason a lot of lists look clean and are not. This post covers the steps, then the three cases where it will let you down.
The steps
One habit worth keeping: copy the sheet to a new tab first and run the removal there. Excel does this in place and the undo history does not survive a save and close.
- 1 Click any cell inside your data, so Excel selects the whole range.
- 2 Open the Data tab and choose Remove Duplicates.
- 3 Tick My data has headers if the first row holds column names, otherwise Excel will treat them as data.
- 4 Choose which columns define a duplicate. Leaving every column ticked means a row must match on every field to count. Ticking only Email means one row per email address.
- 5 Click OK. Excel reports how many duplicate values were removed and how many unique values remain.
Case 1: it only matches exactly
Remove Duplicates compares values character for character. It is not case sensitive, which helps, but everything else has to line up.
That means all of these survive as separate rows:
Acme Corp and Acme Corp.
One trailing full stop.
jose.munoz@acme.com and jose.munoz@acme.com with a trailing space
An invisible difference.
Tom OBrien and Thomas O Brien
The same person, entered by two people.
Ridgeline Partners and Ridgeline Partners LLC
A legal suffix.
A quick check before you run it: use TRIM and LOWER in a helper column and see how many rows collapse. If the number changes a lot, exact matching is not going to be enough. That is the job fuzzy matching exists for.
Case 2: it deletes rows and does not tell you which
The confirmation message says how many values were removed. It does not say which rows they were, which values differed between them, or which of the two copies survived.
When a duplicate row held the only copy of a phone number or a note, that data is gone and there is nothing to compare against. If you ever have to explain why a record changed, the answer cannot be that Excel removed 148 values.
Case 3: it keeps the first row, not the best one
Excel keeps the first occurrence in sheet order. If the more complete record happens to be lower down, the fuller row is the one that goes.
You can work around it by sorting first, for example by putting rows with a filled email address at the top. It is a workaround, and it depends on you remembering to do it every time.
Case 4: it compares what the cell shows, not what it holds
Remove Duplicates decides two values are the same by what the cell displays, not by what is stored underneath it. That sounds like a technicality until it happens to a date column.
The same day, held as the same underlying value, formatted as 08/03/2026 in one row and as 8-Mar-26 in another, is two different values as far as the dialog is concerned. Both rows survive. The same is true of a reference number typed as text in one export and as a number in another: 00123 and 123 look related to you and unrelated to Excel.
The fix in the spreadsheet is to force one display format across the whole column before you run anything, which is a step people forget precisely because the column already looks consistent on screen. It is the same problem from the other side as Excel data cleaning generally: what you see and what is compared are two different things.
What it does with blank cells
Blank counts as a value. If you pick one column and two rows are empty in it, those two rows are duplicates of each other and one of them is removed, even though they may describe two completely different customers.
On a contact export with a half filled phone column, running the removal on that column quietly collapses every row that was simply missing a number. Filter the blanks out of the range first, or pick a column that is always populated.
Two safer tools sitting in the same ribbon
If the only thing you need is the unique set, Excel can give it to you without deleting anything.
Advanced Filter
On the Data tab, choose Advanced, tick Unique records only and Copy to another location. The original range is left exactly as it was and the unique rows are written somewhere else, so you can compare the two.
The UNIQUE function
In a spare column, =UNIQUE(A2:D5000) spills the distinct rows as a live result. Nothing is deleted, and it updates when the source changes.
Both share the limitation this whole post is about: they are exact comparisons, so Acme Corp and Acme Corp. remain two things. What they buy you is that the original file is still there when you find out.
What to do instead when the list matters
For a small file where duplicates are genuinely identical, Remove Duplicates is fine. Keep the copy of the original sheet and move on.
For a list that will be mailed, imported into a CRM, or used in a number someone will question, you want three things Excel does not offer: matching that tolerates spelling differences, a choice about which row survives, and a record of what changed. That is what a data cleaning tool does, and you can run Datauntangler on your own file in the browser without uploading anything. The related read is finding duplicates in Excel without deleting anything, which is the safer first step.