How to Remove Duplicates in Google Sheets
Although it remains the case that Microsoft Excel is the king of spreadsheets, with an almost infinite variety of powerful features, Google Sheets continues to gain ground among even power users. Sheets is faster, more intuitive and much less complicated than Excel, and is a superior solution for sharing collaborating seamlessly without having to use Microsoft’s clunky (and not-free) SharePoint.
One task that people frequently ask us about is how to remove duplicate rows in Google Sheets. In this article I will provide a brief tutorial on how to do just that.
The more complicated a spreadsheet gets, the more likely it is that you will duplicate cells, rows or columns. With a smaller spreadsheet, finding and removing them is simple. But what happens if you’re counting rows by the thousand?
Using the Unique function to remove duplicates in Google Sheets
There are two main ways to remove duplicates in Google Sheets: use the Unique function or use an add-on.
- Open the spreadsheet you want to sort and click on an empty cell a few rows over from the data you want to de-dupe.
- Type ‘=UNIQUE(’ into the formula box above the data.
- Select all the cells you want to sort with the mouse and they should appear at the end of the formula.
- Close the brackets to complete the formula. In the example images, it looks something like ‘=UNIQUE(A1:A12)’ where I selected column A rows 1 to 12.
5. Click anywhere on the Sheet and the unique entries will appear in the row you selected.
6. Now just delete the original data and you’ll have removed the duplicates.
This is a simple way to sort unique entries from a spreadsheet but isn’t quite as seamless as the Remove Duplicates function in Excel. Nevertheless, it gets the job done.
Use an Add-on to remove duplicates in Google Sheets
Google Sheets supports a huge library of add-ons of one kind or another and there are some pretty good ones around. A couple of them have been designed specifically to remove duplicates. For this example, I’m using “GSmart Duplicates Remover” but most of these add-ons do pretty much the same thing.
- Go to Add-Ons->GSmart Duplicates Remover->Open Duplicates Remover.
- Select the column(s) you want to de-dupe.
- Click “Remove Duplicates”.
Voila! The duplicates are removed in place.
Know any other ways to remove duplicates in Google Sheets? Let us know in the comments below if you do!