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 (1)
One case this doesn't cover: sometimes you don't want to keep one copy — you want a value gone completely if it appears more than once. Examples: sign-ups who registered twice and should be excluded entirely, or finding the entries that are genuinely one-offs.
In Excel you can do it with a helper column:
=COUNTIF($A:$A, A2)Fill it down, then filter. Rows showing 1 appear exactly once — keep those. Rows showing 2 or more are the repeated ones, and every copy of them is in that filtered view, so you can delete or move them together. Works the same in Google Sheets.
If your list is just one column of text and isn't in a spreadsheet, the same thing exists as a free browser tool: bulktextkit.gt.tc/tools/remove-dup... — paste the list and choose "Remove ALL copies of repeated lines"; it deletes every copy of any repeated line and keeps only the lines that appear once. The default mode on that page is the keep-one behaviour described in the article, so check which mode is selected before copying the result back.