How To Remove Duplicates In Google Sheets (3 Easy Ways)

Google Sheets is slowly becoming the spreadsheet choice for many people. The ease with which you can collaborate in Google Sheets is way ahead of all the other spreadsheet tools.

Another reason Google Sheets is being used so much is due to the ease of use. The team behind it is constantly adding new features and functionalities that make getting things done easy and fast.

In this tutorial, I will show you a couple of ways to remove duplicates in Google Sheets.

Delete Duplicates Using the ‘Remove Duplicates’ Tool

Removing duplicates is such a common thing people do in Google Sheets, that there is now a dedicated option to quickly remove the duplicate records.

Suppose you have a dataset as shown below and you want to remove all the duplicate records from this dataset.

Dataset with duplicate records

Below are the steps to remove duplicates from a dataset in Google Sheets:

  1. Select the dataset from which you want to remove the duplicate records
  2. Click the Data option in the menuClick the Data tab
  3. Click on the Remove Duplicates optionClcik on Remove duplicates
  4. In the Remove Duplicates dialog box, make sure ‘Data has header row’ is selected (in case your data has the header row).Check the Option Data has header row
  5. Make sure ‘Select All’ is selected (in the ‘Columns to Analyze’ section)Click on Select All columns option
  6. Click on the ‘Remove duplicates’ button.Check the Option Data has header row

The above steps would instantly remove all the duplicate records from the dataset and you would get the result as shown below.

When you use the ‘Remove Duplicates’ option to get rid of the duplicate records, it wouldn’t impact the data around it. This means that using it doesn’t remove the rows or delete any cells. It simply deletes the duplicate records from the cells (without disturbing cells around the dataset)

Remove Duplicates using the UNIQUE function

Google Sheets also has a function that you can use to remove the duplicate values and keep the unique values only.

It’s the UNIQUE function.

Suppose you have the dataset as shown below and you want to remove all the duplicate records from this dataset:

Dataset with duplicate records

The below formula would remove all the duplicate records and you will get all the unique ones:

=UNIQUE(A2:B17)

The above formula will give you the result starting from the cell in which you entered the formula.

The result is an array of unique records and you can not delete or edit part of this array result. Nothing will happen in case you try to delete any one cell from the result. And in case you overwrite any cell, the entire result would go away and you will see a #REF! error.

One of the limitations of using the UNIQUE function is that it would consider only those records as duplicate where the entire row content repeat. In case you only want to keep one instance of a country name and delete all the other, UNIQUE will only do it if the rest of the column values for that record are also the same.

In case your data has leading, trailing or extra spaces, the unique function will consider the records as different. In such a case, you can use the below formula:

=ArrayFormula(UNIQUE(TRIM(A2:B17)))

Remove Duplicate using an Add-on

Google Sheets, as a rule, supports a massive library of various add-ons to take care of every conceivable issue or problem.

The duplicate removal add-ons can all be used for the same purpose. The Remove Duplicates add-on by AbleBits is one of the best add-ons to remove duplicate records from your dataset.

To use an add-in, you first need to add it to your Google Sheets document.

Below are the steps to add an add-on in your Google Sheets document:

  1. Open the Google Sheets document in which you want to remove the duplicates
  2. Click the ‘Add-ons’ tabClick on Add-ons option in the menu
  3. Click on ‘Get add-ons’Click on Get Add-ons
  4. In the Add-ons dialog box that opens, search for ‘Remove Duplicate’ in the field in the top-rightEnter Remove Duplcates in the Add on search bar
  5. In the list of add-ons that are shown, click on the ‘Remove Duplicate’ add-onClick on the Remove Duplicate add-on in the search results
  6. In the Remove Duplicates add-on screen, click on the blue Install button.Click on the BLUE install button
  7. In the dialog box that appears, it may ask you to confirm your account by logging in to your Gmail. Enter the credentials and click on the blue ‘Allow’ button.Allow to add the add-on to the Google Sheets

The above steps would add the Remove Duplicate add-on in your Google Sheets document and now you can start using it.

Below are the steps to use this add-on to delete the duplicate records in Google Sheets:

  1. Select the dataset in which you have the duplicates that you want to remove.
  2. Click the Add-ons option in the menuClick the Add-on option
  3. Hover the cursor over the ‘Remove Duplicates’ option.
  4. Click on ‘Find duplicate or unique rows’. This will open the ‘Find duplicate or unique rows’ dialog box (it may ṭake a few seconds)Click on Find Duplicate or Unique rows
  5. In Step 1, make sure the correct range is selected. You can also check the box to create a backup copy of the Google sheets document.Select the range from which you want to remove duplicates
  6. In Step 2, make sure Duplicates is selectedSelect Duplicates option in Step 2
  7. In Step 3, specify whether your data has a header or not and whether you want to skip empty cells or not.Select my table has headers
  8. In Step 4, select the ‘Delete rows within selection’ option. This will remove the duplicate records.Select Delete rows within selection
  9. Click on Finish.

The above steps would instantly remove the duplicate records and you will be left with the unique records only.

Since Google Sheets already has a way to quickly remove duplicates using an inbuilt feature, it’s best to use it if possible. The only reason I would recommend using the add-on is when you want to do a lot more than just removing duplicates.

This add-on can also perform the following:

  • Color the duplicate records
  • Add a column and specify the records that are duplicates
  • Compare columns or sheets

You may also like the following Google Sheets tutorials:

Leave a Comment