Guide · Spreadsheets
How to remove duplicates in Excel and Google Sheets
Duplicate rows creep in when lists are merged, copied or imported. Here are four ways to find and remove them, from the one-click tools to formulas that let you check before you delete anything.
Short on time? We clean it for you from £15
Before you start: make a copy
Removing duplicates deletes rows for good once you save. Duplicate the sheet tab first (right-click the tab, then Duplicate in Google Sheets or Move or Copy in Excel) so you can always go back.
1. Excel: the Remove Duplicates button
- Click any cell inside your data.
- Go to the Data tab and click Remove Duplicates.
- Tick My data has headers if your first row is headings.
- Tick only the columns that decide whether a row is a duplicate. For a contact list that might just be Email.
- Click OK. Excel tells you how many duplicates it removed and how many unique values remain.
2. Google Sheets: Data cleanup
- Select the range, including the header row.
- Go to Data, then Data cleanup, then Remove duplicates.
- Tick Data has header row and choose the columns to compare.
- Click Remove duplicates. Sheets reports how many rows it removed.
While you are in that menu, Trim whitespace is worth running first. A stray space at the end of a name makes two identical-looking rows count as different.
3. Highlight duplicates first, then decide
If you would rather see the duplicates before deleting them:
- Excel: select the column, then Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values.
- Google Sheets: select the column from row 2 down (say A2:A), then Format, Conditional formatting, choose Custom formula is, and enter
=COUNTIF($A$2:$A,A2)>1.
Then sort or filter by colour and check each one. This is the safest option when two rows look alike but hold different details, such as two orders from the same customer.
4. Formulas that leave the original alone
- UNIQUE: in an empty column type
=UNIQUE(A2:A500)to list each value once. It works in Google Sheets and in Excel for Microsoft 365 and Excel 2021 or later. - COUNTIF check: in a helper column type
=COUNTIF($A$2:A2,A2)>1and fill it down. The first time a value appears shows FALSE and every repeat shows TRUE, so you can filter on TRUE and delete just the repeats.
Mistakes to avoid
- Comparing on every column when only one (like email or order number) should count.
- Missing near-duplicates caused by capital letters, extra spaces or “Ltd” versus “Limited”.
- Selecting one column only in Excel and choosing Continue with the current selection, which can leave the other columns out of line.
- Not keeping a backup copy before you start.
Related guides: how to clean up a messy spreadsheet and how to convert a PDF to Excel.
Frequently asked questions
- Does removing duplicates in Excel delete the whole row?
- Yes. Remove Duplicates deletes the entire row within the selected range for each repeat it finds, keeping the first one. That is why it is worth making a copy of the sheet first.
- Which row does Excel or Google Sheets keep?
- Both keep the first occurrence, from the top down, and remove the later ones. Sort your data first if you want a particular row, such as the newest, to be the one kept.
- Can I undo Remove Duplicates?
- Straight afterwards you can press Ctrl+Z (Cmd+Z on a Mac). Once you have closed and saved the file, only a backup or version history will bring the rows back.
- Why are some duplicates not being removed?
- Usually because the values are not exactly the same: extra spaces, different capital letters or slightly different spellings. Trim spaces and tidy the text first, then run it again.
Want it done for you?
Send us the file and TCH Works returns it cleaned, sorted and de-duplicated in Excel or Google Sheets. Up to 500 rows £15, up to 10,000 rows £60, back by email.