Even though I own a legit copy of Microsoft Office, I like using Google Sheets. It seems faster, more intuitive and much less complicated than Excel. Plus you can share and collaborate seamlessly without getting SharePoint involved. One thing I recently had to learn was how to remove duplicates in Google Sheets and I thought I would share that new knowledge with you dear reader.
Also see our article How to make a dropdown list in Google Sheets
Both Excel and Sheets make working with spreadsheets easy. I have been using both of them for years and I have barely touched the surface of what they can do. It has taken me this long to learn how to remove duplicates, let alone create complex formulas!
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.
- 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:A26)’ where I selected column A rows 1 to 26.
- Click anywhere on the Sheet and the unique entries will appear in the row you selected.
- Now just delete each of those entries in 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
Most Google Docs support 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.
- Open the spreadsheet you want to sort and select Add-ons from the menu.
- Click get Add-ons and the Google Store window will open.
- Select and install your duplicate removal add-on of choice.
- Click the Add-ons menu button and select the app you just added and use it as directed.
Most apps will have a couple of functions, find and remove duplicates and compare. The exact wording and placement of these functions will obviously differ between them but they should generate similar results in the end.
Know any other ways to remove duplicates in Google Sheets? Let us know below if you do!