How to remove duplicates in Excel
Excel can remove repeated rows from a list. Before you do, decide what counts as a duplicate. A row might be repeated across every column, or just share the same customer ID or email address.
Before removing rows
Make a copy of your worksheet or save a separate copy of the workbook. Removing duplicates deletes rows from the selected data, and you may need the original information later.
Remove duplicate rows
- Click a cell inside your data, including the heading row.
- Choose Data > Remove Duplicates.
- If your data has headings, make sure My data has headers is selected.
- Select the columns Excel should check. Select every column to remove only fully repeated rows. Select just Email to keep one row for each email address.
- Click OK and read Excel's message showing how many duplicates it removed.
Example: remove repeated email addresses
| Name | |
|---|---|
| Ana | ana@example.com |
| Ben | ben@example.com |
| Ana P. | ana@example.com |
If you select only the Email column in the Remove Duplicates window, Excel keeps one row for ana@example.com and removes the other. Excel keeps the first occurrence it finds, so sort the data first if you want a particular row to remain.
Highlight duplicates before deleting
To review repeated values first, select the cells in the column, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This marks repeats without deleting any rows.
Use a formula to mark repeats
If email addresses are in B2:B4, enter this in C2 and fill it down:
=IF(COUNTIF($B$2:B2, B2)>1, "Duplicate", "First")The first time an email appears, the formula says “First.” Later appearances say “Duplicate.” Filter column C to review the repeated rows before removing them.
Remember
- Excel compares the columns you selected. If you select only one column, it may remove rows that have different information in the other columns.
- Extra spaces can make two values look the same while Excel treats them as different.
- Use Undo right away if the wrong rows were removed, or restore your saved copy.