Excel Remove Duplicates Deletes for Good. Copy First

You have a list with repeats in it. Excel has a button called Remove Duplicates, and it looks like exactly the tool for the job. It is. And it will not ask you twice.

Microsoft's own page opens with a warning most people never read. A second one further down explains why the button sometimes seems to miss half of them.

I checked which Excel this machine has before writing. The button has sat on the Data tab for years, and the behavior below is current.

Microsoft's warning, before anything else

Two sentences, and they are the reason to stop for a moment.

⚡ The first line of Microsoft's own duplicates page is the warning: "removing duplicate values means that you are permanently deleting duplicate values."

⭐ And what to do about it, in its own words: "Because you are permanently deleting data, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values."

⚠️ Copy the sheet. Do not just trust undo. Undo survives until you close the file, and people close the file.

⛔ Right-click the sheet tab, choose Move or Copy, tick Create a copy. Ten seconds, and it makes the rest of this reversible.

⭐ Before any bulk delete, I copy the sheet first. It has cost me ten seconds many times and saved an afternoon twice.

Which row survives

Worth knowing, because it is not always the one you want.

⭐ Microsoft says: "When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted."

⚡ The first one wins, by position. Not by quality. Not the most complete record, not the newest, just whichever sits higher up.

⚠️ So sort before you deduplicate, if it matters which version survives. Put the good one on top.

⛔ A newest-record-wins job needs a sort by date first, descending. Excel will not work that out for you.

The column trap that catches everybody

This is the one that makes people think the button is broken.

⚡ Microsoft states what happens to the columns you did not tick: "Data will be removed from all columns, even if you don't select all the columns at this step."

⭐ And it explains the key: "the 'key' used to find duplicates is the value of BOTH Column1 & Column2. If a duplicate is found in those columns, then the entire row will be removed, including other columns."

⚠️ So ticking one column deletes whole rows. It judges on that column alone. Everything else in the row goes with it, unread.

⛔ Tick every column you want compared, not just the one you are looking at. That single misunderstanding is behind most of the accidental data loss here.

Why identical-looking values are not duplicates

The second surprise, and Microsoft is very clear about it.

⭐ Microsoft says: "A comparison of duplicate values depends on what appears in the cell, not the underlying value stored in the cell."

⚡ Its own example is dates: "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."

⚠️ Ours, and it is the same trap in three other shapes: a trailing space, a number stored as text, and different capitalization of the same word.

⛔ Clean before you deduplicate. Trim the spaces, make the formats match, and only then press the button.

Why is the button greyed out?

Short, and easy to miss on the page.

⚡ Microsoft says: "You cannot remove duplicate values from outline data that is outlined or that has subtotals."

⭐ If your sheet has grouping or a subtotal row, remove those first. Put them back afterwards.

⚠️ The button greys out rather than explaining itself, which is why people report it as missing.

See them before you delete them

Ours, and it is the safer order.

⭐ Use conditional formatting to highlight duplicates first. Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. On a sheet I do not know well, I never skip this.

⚡ Now you can look at what is about to go. Half the time the highlighted rows are not duplicates at all, and you have just saved yourself.

⚠️ A helper column gives you the same picture and lets you sort by it. That helps on a long sheet.

⛔ Filtering for unique values is the reversible cousin of this whole job. It shows you the result without touching the original at all.

⚡ Word has its own version of a button that does more than it looks like. The blank page nobody can delete is the classic.

What to do the moment you realize it went wrong

Three steps, and speed matters.

⛔ Press Ctrl and Z before you touch anything else. Undo works, right up until you close the file.

⚠️ Do not save. If the file is still open and unsaved, closing without saving is a complete recovery.

⭐ If it is already saved and closed, look for a previous version. OneDrive and SharePoint keep version history, and that is the fastest route back.

⚡ On a local file with no version history, this becomes a recovery job. What you do in the next few minutes decides the outcome.

Status: checked against Microsoft's duplicates page on 28 August 2026, with the Excel version read here. ⛔ No workbook was opened, edited or deleted from to test any of this.

Does Remove Duplicates in Excel delete data permanently?

Yes. Microsoft states plainly that removing duplicate values means permanently deleting them, and advises copying the original range to another worksheet or workbook first. Undo will bring it back while the file is still open, and it will not help you once the file is closed.

Why does Remove Duplicates not find my duplicates?

Usually because the values only look identical. Microsoft says the comparison depends on what appears in the cell rather than the value stored in it. So two identical dates formatted differently count as unique. Trailing spaces, numbers stored as text and different capitalization do the same thing.

Which duplicate row does Excel keep?

The first one in the list. Microsoft says the first occurrence is kept and the others are deleted. It goes by position, not by which row is most complete. Sort the sheet first if you care which version survives.

Why is Remove Duplicates greyed out?

Most often because the data is outlined or has subtotals. Microsoft states that you cannot remove duplicate values from data in that state. Remove the grouping or the subtotals, run the tool, then put them back.

The Short Version

• Microsoft says removing duplicates permanently deletes data.
• Copy the sheet first. Undo only works until the file is closed.
• The first occurrence survives, by position, not by quality.
• Ticking one column still deletes whole rows, including untouched columns.
• Excel compares what appears in the cell, not the stored value.
• Two identical dates formatted differently count as unique.
• The tool refuses on outlined or subtotalled data and greys itself out.
• Highlight duplicates with conditional formatting before deleting anything.

Where to Next

If it is a purchase you are trying to untangle rather than a spreadsheet, the wording Apple uses is its own maze. A billing problem with a previous purchase has a fixed route out.

Copy the sheet before you press anything. Right-click the tab, Move or Copy, tick Create a copy. It makes everything after this reversible.

Then highlight the duplicates with conditional formatting and look at them. Half the time they are not what you expected.

Trim your trailing spaces and make your date and number formats match before you deduplicate. Excel compares what it can see.

When you do run it, tick every column you want compared, and remember that whole rows go regardless.

And if Remove Duplicates has ever taken something you needed, tell me what the key column was in the comments. It is almost always one ticked column doing more than somebody expected.