How to remove duplicate rows
- Open your file. If it's a workbook with several sheets, choose the sheet to clean.
- Pick the columns that decide whether two rows are the same. All columns catches rows that were pasted twice; a single column, such as Email, leaves one row per email address.
- Check the sets of duplicates on the right. Each one shows the row that stays and the rows that go, with the row numbers you'll see in Excel.
- Download the cleaned file. You can also download the removed rows, or keep every row and mark the repeats instead.
Which columns should decide a duplicate?
It depends on what one row stands for. Compare the columns that identify that thing, and leave out the ones that can change between copies, such as a phone number someone updated.
- Customer or mailing lists: Email. If some people have no email, use first name, last name and ZIP code instead.
- Orders, invoices or payments: the order or invoice number.
- An export pasted in twice: all columns.
Rows with nothing in the compared columns are never treated as duplicates of each other. If you compare by Email, ten customers without an email address stay ten customers.
A worked example
A short customer list where two people appear twice, typed slightly differently:
customers.csv
| Row | A | B | C |
|---|---|---|---|
| 1 | Name | City | |
| 2 | Dana Whitfield | dana@example.com | Columbus |
| 3 | Marcus Bell | marcus@example.com | Austin |
| 4, removed | marcus bell | MARCUS@example.com | Austin |
| 5 | Priya Raman | priya@example.com | Tampa |
| 6, removed | Dana Whitfield | dana@example.com | Dayton |
Row 6 is a good reminder to look before you download: Dana's city changed. If the newer address is the right one, choose to keep the last copy instead of the first.
Keep the first copy or the last?
Duplicates aren't always identical. When the compared columns match but others differ, the copy you keep decides which version survives.
- Keep the first when the oldest record is the one that counts, for example the date a customer first signed up.
- Keep the last when rows are added over time and the newest one has the current details, such as a new address or phone number.
If neither copy is fully right, mark the duplicates instead, fix the kept row by hand, then delete the marked ones.
Reviewing duplicates before deleting them
Mark them only keeps every row and adds a Duplicate of row column. The rows that stay have it empty; each repeat shows the row number it matches.
In Excel, turn on Data > Filter and filter that column to hide the blanks: you see only the repeats, next to their row numbers, and can check them one by one. Sorting by the compared column, such as Email, puts each repeat right under the row it matches. When you're happy, delete the filtered rows and the column.
The same review works for a colleague: send the marked file, and nothing is lost until someone decides.
What doesn't count as a duplicate
Matching is exact once capital letters and extra spaces are set aside. That means some rows a person would call duplicates are not caught:
- typos, such as dana@exmaple.com
- nicknames, such as Bob and Robert
- the same phone number written two ways, such as (614) 555-0142 and 614.555.0142
Guessing at these would also remove rows that only look alike, which is worse than leaving a few in. Comparing by a column that is typed consistently, usually email, avoids most of them.
Removing duplicates in Excel or Google Sheets
Excel has a built-in command: select the table, then Data > Remove Duplicates and tick the columns to compare. It deletes the repeats straight away and tells you how many went, but not which ones, so save a copy first. A trailing space makes two values different to Excel, which is a common reason it seems to miss duplicates; the TRIM function removes those spaces.
To see duplicates without deleting anything, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. It highlights repeated values in the cells you select, one column at a time, rather than whole rows.
In Google Sheets, select the data and use Data > Data cleanup > Remove duplicates.
If your duplicates come from combining several files, merge them first, then clean the merged file here.