How to Remove Duplicates in Excel Without Losing Data (7 Steps)

Excel’s Remove Duplicates button is fast — and that’s the problem. One click can delete rows you meant to keep, and Excel misses “duplicates” that only differ by a stray space or capital letter. Here is the routine I use so nothing important disappears.

1. Work on a copy

Before anything else, duplicate the sheet (right-click the tab → Move or Copy → Create a copy) or save a new version of the file. If something goes wrong later, you can compare against the original instead of hoping Undo goes back far enough.

2. Decide what a “duplicate” actually means

This is the step most people skip. Is a duplicate the same email address? The same name and phone number? The same invoice number? Write the rule down. A customer list deduped on name alone will merge two different people named “John Smith”; deduped on email, it keeps them apart.

3. Clean the key columns first

To Excel, john smith, John Smith (trailing space) and JOHN SMITH are three different values. Normalize the columns in your rule with a helper column:

=PROPER(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))

TRIM removes extra spaces, but not the non-breaking spaces (CHAR(160)) that often sneak in from websites and PDFs — that’s what the SUBSTITUTE handles. For emails, use =LOWER(TRIM(B2)) instead, since emails aren’t case-sensitive in practice.

4. Flag duplicates before deleting them

Add a column that counts how many times each key appears so far:

=COUNTIFS($E$2:E2,E2)

The first occurrence shows 1, and every repeat shows 2, 3, and so on. Now you can filter for anything greater than 1 and actually look at what you’re about to remove. If you need to match on two columns, build a combined key first, such as =E2&"|"&F2.

5. Choose which copy to keep

Remove Duplicates always keeps the first row it finds. If the newest record is the one you want, sort by date (newest first) before removing duplicates. If different copies have different missing fields, merge the details into one row before you delete the others.

6. Move removed rows to a review tab

Instead of deleting flagged rows outright, filter them, copy them to a sheet named something like Removed duplicates, and then delete them from the main list. Anyone checking your work can see exactly what was taken out and why.

7. Check your counts

Finish with a quick sanity check: original row count = cleaned rows + removed rows. If you have Excel 365 or 2021, =ROWS(UNIQUE(E2:E5000)) tells you how many unique keys exist, which should match your cleaned list. If the numbers don’t add up, something slipped through — better to catch it now than after the list goes to a mail merge or CRM import.

That’s it: copy, define, clean, flag, choose, archive, verify. It takes a few extra minutes, and it is the difference between a clean list and a quietly broken one.