How to Remove Duplicates in Excel Without Losing Original Data

Duplicates cause wrong totals and wrong counts. Here are three safe ways to deal with them.

Back up first

The Remove Duplicates button deletes rows permanently. Copy your data to a new sheet before you start.

Method 1: Remove Duplicates button

1. Click any cell in your data.
2. Go to Data → Remove Duplicates.
3. Tick the columns that define a duplicate. If you tick all columns, only fully identical rows are removed. If you tick only Email, rows with the same email are treated as duplicates.
4. Click OK. Excel tells you how many rows were removed.

Method 2: the UNIQUE function

=UNIQUE(A2:C100)

This returns a new list without duplicates and leaves the original data untouched. It is available in Microsoft 365 and Excel 2021 or later.

Method 3: flag duplicates first

Add a helper column to see duplicates before deleting anything:

=IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", "")

The first time a value appears it is left blank. Later copies are marked, so you can filter and review them.

Watch out for hidden differences

"Priya" and "Priya " (with a space) look the same but are not duplicates to Excel. Clean the column with TRIM first.