How to remove duplicates in Excel, and why it leaves the worst ones behind

Excel is doing something simpler than most people assume. It takes the columns you tick, joins the values in each row, and looks for rows where that combination already appeared. Exact matches go, everything else stays.

That produces two behaviours worth knowing, and they pull in opposite directions.

It is stricter than you’d expect about characters. A trailing space makes a value different. So does a non-breaking space pasted in from a web page, which is invisible and extremely common in imported data. Two rows that look identical on screen can both survive because one has a space on the end.

It is looser than you’d expect about case. Remove Duplicates does not distinguish between upper and lower case, so UK and uk count as the same value and one of them is deleted. If case actually matters in your data, and occasionally it does, this feature will quietly destroy that distinction.

And it has no concept of meaning at all. This is the one that causes the damage. United Kingdom and UK are, to Excel, no more related than Belgium and Tuesday. Run the tool on that column and it will report back that it removed a handful of duplicates and left a certain number of unique values, and the eight ways of writing one country will be sitting there among them, counted as eight separate places.

Nothing has gone wrong. The tool did precisely what it says. It just doesn’t do the thing most people think they’re asking it to do.

With that understood, the mechanics are quick.

1. Click any cell inside your data.
2. On the Data tab, select Remove Duplicates.
3. Excel selects the whole range and lists every column with a tick box. Untick any column that shouldn’t count towards whether a row is a duplicate.
4. Confirm that My data has headers is ticked if your top row is a header, or Excel will treat it as a record.
5. Select OK.

Excel deletes the matching rows and reports how many it removed and how many unique values remain. Microsoft’s guide to finding and removing duplicates walks through the same steps.

The column selection is the part that matters. Tick every column and you’ll only lose rows that are identical all the way across, which is usually very few. Tick only an email address column and you’ll remove every repeat of that address regardless of what else differs on those rows, which may well delete data you wanted. Decide deliberately what makes a row a duplicate before you tick anything.

Worth knowing (bordered aside box):

Remove Duplicates deletes rows permanently. It doesn’t hide them, filter them or move them somewhere recoverable, and once the workbook has been saved and closed the only route back is a previous version. Two habits make that survivable. Save a copy of the sheet before running it, which takes seconds. And if the file is in OneDrive or SharePoint, version history gives you a genuine safety net, which is one of the better arguments for not keeping important workbooks on a local drive. If you’re at all unsure what will go, use one of the non-destructive methods below first and look at the result before deleting anything.

Two of the highest-volume searches on this topic aren’t about deleting at all, they’re about seeing. That’s usually the more sensible order.

The quickest way to see what you’re dealing with:

1. Select the column or range.
2. On the Home tab, choose Conditional Formatting, then Highlight Cells Rules, then Duplicate Values.
3. Pick a colour and select OK.

Every value appearing more than once is now shaded, and nothing has been altered. This is also the fastest way to discover the opposite problem: run it on a country column and the eight UK variants will stubbornly refuse to highlight, which tells you immediately that your duplicates aren’t the kind Excel can see.

For a count rather than a colour, add a helper column:

=COUNTIF($B$2:$B$500, B2)

Any row returning more than 1 appears elsewhere in the column. Sorting or filtering on that column gives you the full picture, and it’s a better basis for a decision than a confirmation message after the fact.

Worth mentioning because it’s the fastest way to see variant spellings rather than exact duplicates. Put the column in Rows and the same column in Values as a count, and every distinct spelling appears as its own row with a tally. That’s how the eight UK variants become visible in one glance, and it’s covered in our pivot tables guide.

If deleting rows from your source feels risky, and it often should, there are three better routes.

The UNIQUE function, on Microsoft 365, Excel 2021 or Excel 2024:

=UNIQUE(B2:B500)

This spills a list of the distinct values into a new location and leaves the source completely untouched. It also updates automatically as the source changes, which Remove Duplicates never will. Not available in Excel 2016 or 2019, the same version boundary that applies to XLOOKUP.

Advanced Filter, which works in every version. On the Data tab, choose Advanced, select Copy to another location, and tick Unique records only. You get a clean copy elsewhere and keep the original.

Power Query, which is the right answer when the job recurs. Its own Remove Duplicates step works on a copy of the data, and because every step is saved and replayed, next month’s file gets cleaned identically without anyone repeating the work. Our Power Query guide covers the wider approach. Microsoft’s reference on filtering for unique values or removing duplicate values covers the in-sheet options in more depth.

Back to the eight spellings, because that’s the problem Remove Duplicates leaves behind and it needs a different approach entirely.

Start with whitespace, since it’s the cheapest win and often accounts for a surprising share of apparent variants:

=TRIM(B2)

TRIM removes leading, trailing and repeated internal spaces. Run it, paste the result back as values, then re-run your duplicate check. Some of your variants will collapse immediately.

Then handle genuine variants. There is no clever formula for this, because the decision is editorial rather than technical. Somebody has to decide whether England means England or means the United Kingdom, and only the business can answer that. A pivot table gives you the list of distinct values to work from, and a simple lookup table mapping each variant to an agreed standard value lets you convert them in bulk with XLOOKUP.

Then stop it recurring. Cleaning the same column every month is a symptom, not a fix. Constraining what can be typed in the first place is the actual solution, which is what our guide to Excel drop down lists covers. And where several people update the same records continuously, the honest answer is that the data has outgrown a spreadsheet and belongs in Microsoft Lists, which enforces typed columns properly.

• Treating the confirmation message as proof. It confirms exact matches were removed. It says nothing about the state of your data.
• Running it on the source rather than a copy. Deletion is permanent and the undo window is shorter than people assume.
• Ticking every column without thinking. Rows identical across all columns are rare, so you’ll remove almost nothing and conclude the data was fine.
• Ticking one column without thinking. The opposite failure, and the expensive one, because rows carrying data you wanted are deleted on the strength of a single repeated value.
• Forgetting the header row. Leave My data has headers unticked and your header is treated as a record.
• Not trimming first. Invisible trailing spaces keep rows that should have gone, and they’re invisible precisely because nobody thinks to look.