How to remove duplicates in Excel (3 ways)
Select your data, go to Data > Remove Duplicates, tick the columns that define a duplicate, and click OK. Excel deletes the extra rows and tells you how many were removed.
Want to check first? Highlight them with Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In Excel 365, =UNIQUE(A2:C100) creates a de-duplicated copy without touching the original.
Applies to Excel for Microsoft 365, Excel 2016 and later, Excel for Mac and Excel for the web. Remove Duplicates permanently deletes rows - work on a copy of the sheet or be ready to press Ctrl+Z.
Method 1: Remove Duplicates (deletes rows)
- Click any cell inside your data.
- Go to the Data tab and click Remove Duplicates (in the Data Tools group).
- Make sure My data has headers is ticked if row 1 has column names.
- Tick the columns to compare. Leave all ticked to remove only rows that are identical in every column; tick just Email, for example, to keep one row per email address.
- Click OK. Excel shows how many duplicates were removed and how many unique values remain.
Method 2: Highlight duplicates before deleting
- Select the column or range.
- Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Choose a colour and click OK.
You can then filter by colour (Data > Filter, then the column arrow > Filter by Color) to review them.
Method 3: UNIQUE function (Excel 365 and 2021+)
=UNIQUE(A2:A100)returns the list without duplicates and updates automatically.=UNIQUE(A2:C100)works on whole rows.=SORT(UNIQUE(A2:A100))returns them alphabetically.=COUNTA(UNIQUE(A2:A100))counts distinct values.
Why didn't Excel remove some duplicates?
Values that look identical often aren't: Remove Duplicates ignores capitalisation, but it treats extra or trailing spaces as different text, and numbers stored as text as different from real numbers. Clean the column first with =TRIM(A2) (and Paste Special > Values), or convert text to numbers with Data > Text to Columns > Finish.
Frequently asked questions
Which duplicate does Excel keep?
The first occurrence (the top-most row). Sort your data first if you want to keep, for example, the newest record.
Can I undo Remove Duplicates?
Yes, immediately with Ctrl+Z (Cmd+Z on Mac). After saving and closing, you can't.
How do I remove duplicates in Google Sheets?
Select the data and choose Data > Data cleanup > Remove duplicates.