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.

1. 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.
   
   
2. Type ‘=UNIQUE(’ into the formula box above the data.
   
   
3. Select all the cells you want to sort with the mouse and they should appear at the end of the formula.
   
   
4. 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. The formula should be removed to be able to delete the original data with duplicate values. We can do this by copying and pasting the unique entries without the formula.
Select the cells with the unique entries. Right click and choose Copy.


7. Click on different cell or choose location where you want to put the data. Right click and select Paste special, choose Paste values only.


8. Now just delete the original data and you’ll have removed the duplicates.
Note: After deleting the original data, unique entries having formula will be automatically removed.

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.

1. Go to Add-Ons->GSmart Duplicates Remover->Open Duplicates Remover.
2. Select the column(s) you want to de-dupe.
3. 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!