How to Remove Duplicates from Google Sheets

If you use Excel or Google Sheets, you might have dealt with situations where you have duplicate records that need to be removed. This is bound to happen when you need to merge two or more files into a single sheet. But whether you’re dealing with an email list that contains duplicated entries or you’re managing a contact list where some people are listed multiple times, you can right this wrong without having to do any manual labor work.

Both Excel and the Google cloud equivalent (Google Sheets) have features that will allow you to delete any unnecessary duplicates from your spreadsheet. In what follows below, we are going to showcase a list of methods capable of automatically removing duplicate rows.

Removing Duplicate Entries from Google Sheets

On Microsoft Excel, removing duplicate rows is extremely easy because the feature is built into the software. You should be able to replicate the steps below regardless of the Excel version that you’re using (desktop or cloud).

Here’s a quick guide on deleting duplicate rows in excel:

1. Open the spreadsheet that you need to clean of duplicates.
2. Using the ribbon bar at the top, go to Data and click on the Remove Duplicates button.

   

3. Next, select the column/s that you want to be inspected. Once you are satisfied with the selection, click Ok to remove any identified duplicate.

   

4. After a short while, you should see a message containing a rundown with the duplicates that have been removed.

   

How to Delete Duplicate rows in Google Sheets

Since Google Sheets doesn’t come with the same built-in functionality, we’ll have to go to additional length in order to curate our spreadsheet of duplicates. But don’t worry, as we’ll feature not one, but two methods of removing duplicate rows from a Google Sheets spreadsheet.

If you want to keep things simple, follow Method 1 where we use a Google Sheet Add-ons called Remove Duplicates that will take care of the issue automatically.

In the event that you are tech-savvy and you like to get your hands dirty, you can follow Method 2 to create a script designed to remove all duplicates from your spreadsheet.

Method 1: Removing Duplicate rows from Google Sheets via Remove Duplicates add-on

There’s a reason why this application is so popular among Google Sheets users. It’s intuitive interface and the fact that is so reliable makes it a perfect choice for those in need of removing duplicate rows.

But there’s a downside to this add-on. It’s only free for 30 days, after which you will have to buy a license if you want to keep using it.

Here’s a quick guide on installing and using the Remove Duplicates add-on on Google Sheets:

1. Visit this link (here) and click on the Free button to download the Remove Duplicates add-on.

   

2. Wait until the extension is loaded, then click the Continue button to give Remove Duplicates permission to run.

   

3. Next, you will be prompted to sign-in with your Google account. Once you do so and click the Allow button at the bottom of the screen, the Remove Duplicates extension is ready to be used inside Google Sheets.

   

4. To use Remove Duplicates, go to the Add-ons tab using the ribbon at the top and click on Remove Duplicates > Find duplicate or unique rows.

   

5. Wait until the wizard is loaded. When the first window appears, select the range manually or hit the Auto select button then hit Next to proceed.

   

   Note: Consider checking the box associated with Create a backup copy of the sheet to avoid data loss.

6. Choose the type of data that you want to target and how you want to treat it and hit Next again. We used Duplicates in order to remove the first duplicate instance.

   

7. Then, select all the columns that you want to be included in the search and press the Next button again.
   
8. In the final window, you will get to select what you want to do with the values that fit the criteria you previously selected. We chose Delete rows within selection on order to remove our duplicates. Finally, hit Finish to get the end result.
   

Method 2: Removing Duplicate rows from Google Sheets with a script

Another way to remove duplicates from Google Sheets is to create a script capable of removing duplicate rows in the spreadsheet data. I know it sounds complicated, but we’ll provide you the script and the rest of steps are pretty straightforward.

Even more, this solution is completely free and does not involve installing additional add-ons or software. Here’s what you need to do:

1. Open the spreadsheet that you want to remove the duplicates from in Google Sheets.
2. Using the Ribbon bar at the top, click on Tools > Script Editor.

   

3. In the Code.gs file, copy and paste to following script:

   function removeDuplicates() {
     var sheet = SpreadsheetApp.getActiveSheet();
     var data = sheet.getDataRange().getValues();
     var newData = [];
     for (i in data) {
       var row = data[i];
       var duplicate = false;
       for (j in newData) {
         if (row.join() == newData[j].join()) {
           duplicate = true;
         }
       }
       if (!duplicate) {
         newData.push(row);
       }
     }
     sheet.clearContents();
     sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
   }

4. Go to File > Save and name this newly created script something recognizable.

   

5. Next, using the ribbon bar, click on Run > Run Function > removeDuplicates.

   

6. If prompted for authorization, click on Review permissions and click Allow to run the script.

   

7. Return to the targeted spreadsheet and take a look at it. You should find that all duplicates have been removed.