Docs tower · floor

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

The command for how to remove duplicates in excel is easy to find and impossible to undo once you have saved and closed. So this floor 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 one most people assume.

Three different tools, and only one of them deletes anything

Microsoft lists all three on the same page, which is more than most guides do. The difference between them is the whole subject.

Most people searching for this want the third and should use the first two first. The third has no preview — it reports how many rows it removed after it has removed them.

“A comparison of duplicate values depends on what appears in the cell—not the underlying value stored in the cell. For example, if you have the same date value in different cells, one formatted as ‘3/8/2006’ and the other as ‘Mar 8, 2006’, the values are unique.”

Microsoft’s own documentation, read 22 August 2026, and it surprises nearly everybody. Formatting decides what counts as a duplicate. The same date, the same number, the same amount of money — if two cells display them differently, Excel treats them as different, and Remove Duplicates will leave both.

That single paragraph explains most of the cases where Remove Duplicates “did not work”. A list of dates imported from two systems, a column of amounts where some cells are text and some are numbers, a spreadsheet where one source uses trailing spaces — in each case the values look identical on screen and Excel sees them as different, or the reverse.

It also gives you the fix, which is to 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.

Microsoft’s own warning, which is the most useful line on the page

“Before removing duplicate values, it’s a good idea to first try to filter on—or conditionally format on—unique values to confirm that you achieve the results you expect.”

The reason this matters more than it sounds: a duplicate is defined across the columns you tick in the dialog, not across the row as a whole. Microsoft’s definition is that “all values in at least one row are identical to all of the values in another row” — so if you tick only Name and Email, two rows that differ in every other column are duplicates as far as the tool is concerned, and one of them goes.

Which one goes is not a judgement Excel makes carefully. It keeps the first occurrence. If the second row was the more complete or more recent one, that is the one you have just lost.

The order that does not lose anything. Make a copy of the sheet first — the whole thing, one right-click, ten seconds. 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 only evidence you get. If it removed far more than you expected, you have just learned something about your data — on a copy, which is the point.

When not to delete at all. If the spreadsheet is a record of something — transactions, submissions, applications — duplicate-looking rows are frequently not 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.

Everything in quotation marks here is from Microsoft’s own Excel documentation, read on 22 August 2026. The working order, the point about records, and the advice to copy the sheet first are ours.

Where to start

Four ways in.

“I just want them gone.”
Do the three steps in order — see before you delete
“It says there are no duplicates and there are.”
That is the formatting rule — when it does not work
“I need to keep the original.”
Go to before you delete
“The sheet is a record of something.”
Do not delete — see spreadsheets as records

Before you delete

A copy, a look, and a count. Three habits that make an irreversible command reversible in practice.

Freezing rows in ExcelOne rule from Microsoft explains every case, including the two that send people to forums.Open this floor →
BackupsThe arrangement that makes losing a file impossible, set up once.Being built
How long to keep recordsThe periods two tax authorities publish, before you delete anything.Open this floor →

When it does not work

Almost every failure is the same one: what the cell displays is not what it stores, and Excel compares the display.

PDF to ExcelWhat survives the conversion and what arrives as text pretending to be a number.Open this floor →
Spreadsheets as softwareThe point at which a spreadsheet has quietly become an application.Being built
Combining Word documentsThe same class of problem in a document: formatting done by hand.Open this floor →

Spreadsheets as records

A working list and a record are different documents with different rules, and treating one as the other is where data goes missing.

Customer data and the lawWhat you are holding about your customers, and what that obliges you to do.Being built
How to write an invoiceThe nine things a government requires on one, including a unique identification number.Open this floor →
Expenses and receiptsWhat to keep, in what form, and for how long.Being built

The rest of Office

The same pattern each time: a rule Microsoft states once, in the middle of a page, that answers everything around it.

Page numbersThree different cases with three different answers, and the button that makes them all fail.Open this floor → Double spacing in WordTwo routes from Microsoft, and the paragraph setting that makes a correctly spaced document still fail.Open this floor → MarginsThe default is already one inch, and why a wider left margin is the wrong fix for binding.Open this floor →

What this tower will not do

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: the definitions, the warning and the tool locations are quoted from Microsoft’s own Excel documentation, with the date it was read.

Where this page got its facts

  1. Microsoft Support — Filter for unique values or remove duplicate values, including the critical difference between filtering and removing, the definition of a duplicate value, and the advice to check before removing — support.microsoft.com, read 22 August 2026.

Written by Alberto Gulotta

Founder and editor of AI Tools Primer, writing from Palermo, Italy. Thirty-five years of taking computers apart, starting with a Commodore 64 — the long version is on the about page.

Something wrong on this page? Write to aitoolsprimer@gmail.com and it gets fixed.

Written on 22 August 2026.

Independence and limits

No affiliate links and no paid placements anywhere on this site. Nobody pays to appear here, and no company has seen this page before you did.

This is general information, not professional advice. Where a page touches money, health, safety or the law, it names its source and the date it was read — and your situation may still differ. See the privacy page and the cookie policy.