Merged sign-up sheets, exported contact lists, data pasted together from two files: they all end up with the same person listed twice. Finding the repeats by eye means sorting, scrolling and comparing rows, and it's easy to miss one.
Excel has a built-in command for this.
TL;DR: Click inside the table, go to Data > Remove Duplicates, and choose which columns must match.
Steps
- Click any cell inside the table, then go to the Data tab and choose Remove Duplicates.
- Leave every column checked and confirm. Excel removes rows that are identical in every column and tells you how many it removed. In the example list, two duplicate rows went.
- Rows for the same person with a different date are still there, because they weren't identical in every column. To keep one row per person, open Remove Duplicates again, check only the Name column, and confirm.
Checked in the free Excel for the web.
Choosing the columns
The checked columns define what counts as a duplicate. With every column checked, only exact copies are removed. With fewer columns checked, rows only have to match on those columns, and Excel keeps the first one it finds.
Watch for people with the same name
If you check only Name, two different people who share a name are treated as one person, and one of them is deleted. When you have a column that's actually unique, like an email address or an ID, use that instead.
Copy the original sheet before you start. Remove Duplicates deletes rows, and comparing against a copy is much easier than rebuilding what was removed.
Common mistakes
- Selecting a single column first. If you remove duplicates in one column only, the rest of each row doesn't move with it. Click inside the table rather than selecting a column, and check the range Excel proposes.
- Hidden differences. A trailing space can stop two rows from matching. If duplicates survive, look for stray spaces.
The same thing in pandas or VBA
Choosing columns works the same way in code. In pandas:
df = df.drop_duplicates() # all columns
df = df.drop_duplicates(subset=["Name"]) # Name only
In VBA, Range("A1:C50").RemoveDuplicates Columns:=Array(1), Header:=xlYes removes rows with a repeated value in the first column. The same caution applies: decide which columns really identify a row before deleting anything.
AI-assisted article. Steps were checked against the original Excel for the web walkthrough.
Top comments (0)