How to remove duplicates in Excel and Google Sheets
The built-in tools in Excel and Sheets, and a faster way for plain lists.
Duplicates creep into every list: two exports pasted together, a form submitted twice, an old mailing list merged with a new one. Here are the three fastest ways to clean them out, and the traps to watch for.
In Excel
- Select the data, including the header row.
- Go to Data, then Remove Duplicates.
- Tick the columns that decide whether two rows are duplicates. Leave all ticked to remove only rows that match completely; tick just Email to keep one row per email address.
- Click OK. Excel reports how many duplicates were removed and how many unique values remain.
Excel keeps the first occurrence of each duplicate and deletes the rest. Make a copy of the sheet first, because the change is permanent once you save.
In Google Sheets
- Select the range.
- Go to Data, then Data cleanup, then Remove duplicates.
- Tick whether the data has a header row, choose the columns to compare, and confirm.
Alternatively, the UNIQUE function returns a de-duplicated copy without touching the original: =UNIQUE(A2:C500).
For a plain list
If you just have a list, such as email addresses, product codes, names or URLs, opening a spreadsheet is overkill. Paste it into the remove duplicates tool, choose whether to ignore case and extra spaces, and copy the cleaned list back out. The original order is kept, and it tells you how many duplicates it found.
The traps
| Trap | Example | Fix |
|---|---|---|
| Case differences | Ada@Example.com and ada@example.com | Ignore case, or convert to lower case first |
| Invisible spaces | "ada@example.com " with a trailing space | Trim whitespace, or use TRIM in the sheet |
| Near duplicates | Jon Smith and John Smith | Not true duplicates; review by hand |
| Comparing too many columns | Same email, different sign-up date | Compare only the column that identifies the record |
Excel's Remove Duplicates is not case-sensitive, but it does treat trailing spaces as different. The remove duplicates tool handles both with its options.
Deduplicating on one column
To keep one row per customer when other columns differ, compare only the identifying column. In Excel, tick just that column. For a CSV you do not want to open in Excel, pull the column out with extract columns, then deduplicate it.
Email lists
For email addresses, the clean email list tool goes further: it lower-cases, removes invalid addresses and deduplicates in one pass, and it can pull addresses out of messy pasted text.