How to Remove Duplicates in Excel Without Deleting the Wrong Rows

Duplicates creep into every list a small business keeps. The same customer gets entered twice from two sign-up forms, an event attendee list gets merged with an older one, or an export from your shop lands on top of the one you already had. Excel can clear them in a few clicks, but it's a tool that doesn't ask twice.

The steps below come from Microsoft's own page on finding and removing duplicates. I've put the safety steps first, because that's where lists get ruined.

Look before you delete: highlighting duplicate values in Excel

Microsoft suggests spotting duplicates with conditional formatting before you remove anything, so you can judge whether they really are repeats.

1. Select the cells you want to check.
2. Go to the Home tab and click Conditional Formatting.
3. Choose Highlight Cells Rules, then Duplicate Values.
4. Pick a highlight style from the box beside the values with option.
5. Click OK.

One limit from Microsoft: Excel can't highlight duplicates in the Values area of a PivotTable. Do this on the source data instead.

Back up your list before clearing duplicates in Excel

Microsoft's warning is blunt: the duplicate data is deleted permanently. Its advice is to copy or move the original data to another worksheet before you remove anything. Do that now. Right-click the sheet tab, make a copy, or paste the range into a new tab. Ten seconds of caution beats an afternoon of rebuilding a mailing list.

The Remove Duplicates button, step by step

1. Select the range of cells that contains the duplicates. Microsoft's tip is to remove any outlines or subtotals from the data first.
2. Go to the Data tab and click Remove Duplicates.
3. Under Columns, tick or clear the columns Excel should compare.
4. Click OK.

Excel then removes the repeats. Microsoft notes that the counts of duplicate and unique values shown afterwards may include empty cells and cells that only contain spaces, so don't treat the totals as a perfect headcount.

Choosing columns when removing duplicates in Excel

This step decides what counts as a duplicate. Excel only treats a row as a repeat when every ticked column matches.

Columns ticked	What Excel treats as a duplicate
Email address only	Any two rows with the same email, even if the names differ
First name, last name and email	Rows where all three match
Every column	Rows that are identical from start to finish

Microsoft gives a small example: if you want to keep a column of January numbers, clear its tick box so it's left out of the comparison. For a contact list, I'd normally compare on the email address alone, because that's the field that really identifies a person.

Duplicates you can't see in an Excel list

Remove Duplicates only catches values that match exactly. Because Microsoft mentions cells containing spaces, a trailing space after a name can be enough to make two entries look different to the tool even though they look the same to you. If you suspect that, run the highlight step first and see what Excel flags.

A before-and-after check for Excel duplicate removal

Once the button has done its work, don't just trust it. Compare the row count with your backup sheet. If the number of rows dropped by far more than you expected, you probably ticked too few columns, and the tool treated different people as the same record. That's the classic slip: comparing on first name alone, for example, and losing every second Sarah on the list.

When that happens, close the file without saving, reopen your backup and try again with more columns ticked. It's much easier to be strict first and loosen later than to rebuild a list from memory.

Remove Duplicates in Excel: the mistakes I'd watch for

• Selecting only one column of a multi-column list, so the tool never sees the other details in each row.
• Leaving subtotals or outlines in place. Microsoft says to remove them first.
• Ticking every column when you only meant to compare one, which leaves near-duplicates untouched.
• Skipping the backup because the list looks small. Small lists are exactly where nobody checks afterwards.

Hiding duplicates instead of deleting them in Excel

If you only want to look at one of each without losing anything, Microsoft's separate article on filtering for unique values explains that filtering hides duplicates temporarily, unlike Remove Duplicates, which deletes them. Filtering is the safer choice for a quick check on a list you don't fully trust.

Tidying an Excel contact or order list for a small business

A routine I like: back up the sheet, highlight duplicates, scan the highlighted rows, then remove on the one column that identifies the record. Save the result under a new file name so the original export is still there if you need it. If the list feeds an email tool, tidying it first keeps people from getting the same message twice, which is the quickest way to annoy a customer.

If your list lives in Google Sheets rather than Excel, my guide to removing duplicates in Google Sheets has the equivalent steps. For help writing the formulas that check a list, this guide to Claude for Excel is worth a look.

Frequently asked questions

How do I remove duplicates in Excel?

Select your data, go to the Data tab, click Remove Duplicates, choose the columns to compare and click OK.

Does Remove Duplicates in Excel delete data permanently?

Microsoft says the duplicate data is permanently deleted, so copy the original data to another worksheet before you start. The safest undo is the copy you made first.

How do I highlight duplicates in Excel instead?

Select the cells, then use Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values, choose a style and click OK.

Can Excel highlight duplicates in a PivotTable?

Microsoft says it can't highlight duplicates in the Values area of a PivotTable, so check the source data instead.

Is there a way to hide duplicates without deleting them?

Yes. Microsoft's article on filtering for unique values says filtering hides duplicates temporarily, unlike Remove Duplicates.