Duplicates can cause quite some trouble in spreadsheets, complicating results and giving a huge difference in the final results and analysis. Luckily, there are easy ways to remove duplicates from your Google Sheets. Here’s how:
1) Remove duplicates in Google Sheets with the new tool to remove duplicates
Google sheets has a new feature that makes it very easy to remove duplicates. All you need to do is go to
Data > Remove Duplicates
This feature will then ask you to choose the columns from which you want to check for duplicates. Here you can remove entire rows with duplicates or specific column and specific rows in that column. After the duplicates are removed, a summary report will show you how many duplicates were removed from the data set
All the examples in this blog post can be found in this workbook (Sheets1&2)
2) Remove duplicates in Google Sheets using formulas
This method deletes duplicates by considering and comparing each row of data and removing all identical duplicates across the whole row.
It is easy as it involves a single formula and only one argument. In the example below the data, the range is selected from the original data set and the formula reads:
The table on the left is the result after the duplicates have been removed.
For this create a new column next to the date column in which you want to check for duplicates example in our sheet that is Name column. In the new column in the cell B2 you can now use the COUNTIF formula as follows:
The $ sign is important as it locks the range to the top of the column so even as you drag down and copy the formula the lock keeps it constant.
Now in the new column when the value shows up for the first time it would read TRUE as the count will be 1 but when it reads a duplicate it will appear as FALSE and the count will be 2.
Once you have filled the new column you can highlight all TRUE values which are duplicates. For a large dataset, you can now paste the actual values from the original column and then sort the values and delete the duplicates in highlighted color so you can remove a big chunk of duplicates together.
There are other formulas which can be used to remove duplicates like the IF formula. Duplicates can also be used by using conditional formatting, pivot tables. We will cover removing duplicates with the IF formula and conditional formatting in another blog post on Google Sheets Cheat Series.
Here are other blog posts in our Google Sheets Cheat Series
We hope you found this article useful. Use our guide Free Keyboard Shortcuts: Spreadsheet Secrets and watch yourself transform from a Google Sheets newbie to a ninja in no time! Save your copy and add the shortcuts that you discover to quickly refer back anytime you need. Stay subscribed to Wyzebulb Blog for more blogs and articles on Automation, Marketing, and Productivity!