Back to all posts

How to Remove Duplicates in Excel Without Deleting Rows You Meant to Keep

Shivank Prabhudessai Founder & CEO ·4 min read

To remove duplicates in Excel, select your range including the header row, then use Data, Remove Duplicates, and choose which columns define a duplicate. Excel keeps the first occurrence of each duplicate and deletes the rest. The step almost everyone skips is choosing the columns deliberately: leaving every column ticked means only rows that are identical in every single field are treated as duplicates, which is often not what you meant.

What counts as a duplicate?

A duplicate is whatever you tell the tool it is, and this is where most mistakes happen.

If you have a customer list and you tick every column, then two rows for the same person with different phone numbers are not duplicates, because the phone column differs. Both survive. If you tick only the email column, they are duplicates, and one is deleted along with the phone number it carried.

Neither behaviour is wrong. But you have to decide which one you want before you run it, because the operation is destructive.

Why did Remove Duplicates delete my blank rows?

Because to most deduplication logic, every blank row looks identical to every other blank row.

This catches people constantly. If you have a table with blank separator rows between sections, and you run Remove Duplicates over the whole range, the first blank row is kept and every other blank row is deleted as a duplicate. Your sections silently collapse into each other, and the confirmation dialog reports a large number of duplicates removed, which makes it look as though it worked well.

Two ways to avoid it:

  • Select only the populated range rather than the whole column.
  • Sort or filter blanks out of the way first, then deduplicate what remains.

This is worth checking in whatever tool you use. It is a genuinely easy behaviour to get wrong, and it fails silently rather than with an error. NativeOffice's Data Cleanser leaves blank rows untouched and never counts them as duplicates, specifically because the failure is invisible when it happens.

Is Remove Duplicates case sensitive?

No. Excel treats SMITH, Smith and smith as the same value, so two rows differing only in capitalisation are duplicates and one will be deleted.

This is usually helpful for names and email addresses. It is a problem for anything where case carries meaning, such as product SKUs, licence keys, base64 strings or case-sensitive identifiers from a database. In those cases, deduplicate outside Excel or add a helper column that makes the distinction explicit before you run it.

Trailing spaces are the opposite trap. "Smith" and "Smith " are treated as different values and both survive, which is why deduplication on imported data so often appears to do nothing. Trim whitespace first, then deduplicate. Doing it in the other order gives the wrong answer.

How do I see the duplicates before deleting them?

Always do this on data you care about. Remove Duplicates does not show you what it is about to remove, and undo only helps if you notice immediately.

Use Conditional Formatting, Highlight Cells Rules, Duplicate Values to colour them first. You get to look at what the rule actually matched before anything is destroyed, and you will frequently discover your definition of a duplicate was wrong.

A safer general habit: work on a copy of the sheet. Deduplication is one of the few spreadsheet operations with no meaningful preview and no partial undo once you have saved and closed.

How do I remove duplicates but keep the last entry instead of the first?

Excel always keeps the first occurrence, which is a problem when later rows hold the most recent version of a record.

Sort into reverse order first, so the row you want to keep becomes the first occurrence, then deduplicate. Add an index column before sorting if you need to restore the original order afterwards, because otherwise you cannot get it back.

What about duplicates across two different sheets?

Remove Duplicates only works within a single range, so this needs a different approach: either combine the sheets into one range first, or use a lookup to flag rows that appear in both before deciding what to delete.

If you find yourself doing this often, a query language handles it far more directly than a chain of helper columns. NativeOffice's SQL on Sheets treats each sheet as a table using its header row for column names, so finding rows present in both is a single statement rather than a stack of VLOOKUPs. See the feature list for what else is on the Data tab.

A safe order of operations

Deduplication goes wrong mostly because it is done in the wrong sequence. This order avoids nearly every trap above:

  1. Work on a copy.
  2. Trim whitespace.
  3. Standardise case if case does not carry meaning.
  4. Deal with blank rows deliberately.
  5. Highlight duplicates and look at them.
  6. Choose the columns that define a duplicate.
  7. Sort so the row you want to keep is first.
  8. Only then remove.

NativeOffice is free on Windows, macOS and Linux, opens .xlsx natively, and includes a Data Cleanser for trimming whitespace and removing duplicates. Download it here.