Docs · guide

How to remove duplicates in Excel, and what counts as one

By Alberto Gulotta · Updated 30 September 2026 · 12 min read

The command for how to remove duplicates in excel is easy to find, and Microsoft advises a copy of the data before you run it. So this guide gives the three tools in the order a careful person uses them, and then the definition of “duplicate” that Excel is actually working to — which is not the everyday one.

1. Copy the data to another worksheet first. Microsoft gives this as advice rather than a step: “before you delete the duplicates, it’s a good idea to move or copy the original data to another worksheet so you don’t accidentally lose any information.” It also names Undo as the way back, on Windows: “Undo the change by select Undo (or pressing Ctrl+Z on the keyboard).”
2. Look at them before you touch them. Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values colours the repeats and changes nothing else.
3. Select the whole range. Selecting one column of a range and deleting its repeats is how rows come apart: Microsoft says “Other values outside the range of cells or table will not change or move.”
4. Data › Data Tools › Remove Duplicates, then choose the columns that define a duplicate. A row counts as a repeat only when every ticked column matches.
5. Read the count it reports, and compare it with what you expected. If the two disagree, the definition of duplicate is the thing to check next, not the command.

That single paragraph explains one case where Remove Duplicates seems not to work: a list of dates imported from two systems, shown in two formats, is not a list of duplicates to Excel.

And the same file gives a different answer in Google Sheets. Google’s rule on formatting is the reverse of Excel’s: “cells with identical values but different letter cases, formatting, or formulas are considered to be duplicates.” So a column of dates that Excel leaves alone, because one row says 3/8/2006 and another says Mar 8, 2006, is a column Sheets will thin out. Neither is wrong; they are answering different questions about what a value is.

The fix that follows from Microsoft’s rule is ours: make the display consistent before you compare — apply the same number or date format to the whole column, and clean up stray spaces, then look for duplicates. On the counts, Microsoft adds: “The counts of duplicate and unique values given after removal might include empty cells, spaces, etc.”

In short. Make a copy of the sheet first — the whole thing. Highlight duplicates with conditional formatting and actually look at them. Decide deliberately which columns define a duplicate for your purpose. Then run Remove Duplicates on the copy, and compare the row counts before and after.

That last step is worth doing every time, because Remove Duplicates tells you how many it removed, and that number is the evidence to check. If it removed far more than you expected, you have just learned something about your data — on a copy, which is the point. And if Excel shut before that copy was ever saved, the recovery routes start with AutoRecover.

When not to delete at all. If the spreadsheet is a record of something — transactions, submissions, applications — duplicate-looking rows are not always errors. Two payments of the same amount on the same day from the same customer can both be real. Deleting one because it looks like a copy is a data-loss event that nothing will flag, and it is the argument for filtering rather than removing whenever the sheet is a record rather than a working list.

The version that never destroys anything. Newer versions of Excel have a UNIQUE function, which produces a de-duplicated list in a new place while leaving the original untouched. Where it is available, it is the better habit for anything you will need to re-check later — the original stays, the clean list updates itself, and there is nothing to undo.

Every quotation from a document here is from Microsoft’s Excel documentation or Google’s Sheets documentation, read on the dates in the Sources. The working order and the point about records are ours.

Not covered here. It will not send you straight to Remove Duplicates. Microsoft itself says to check with filtering or conditional formatting first, and that sentence is quoted above.

It will not pretend the tool understands your data. It compares what the cells display, across the columns you tick, and keeps whichever row came first.

And it will not recommend an add-in for something that is three built-in commands on the Data and Home tabs. What holds instead is simple: what Excel counts as a duplicate, and the warning Microsoft attaches to removing one, are quoted from its own documentation.