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

Watch Video – Remove Duplicates in Google Sheets

The team behind Google Sheets is constantly adding new features and functionalities that make getting things done easy and fast. One such functionality is the Google Sheet delete duplicates option that lets your Google spreadsheet remove duplicates easily. With large volumes of data, you don’t need to strain your eyes to manually look for instances of repeating data to extract.

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

How to Delete Delete Repeats in Google Sheets Using the ‘Remove Duplicates’ Tool

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

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

Dataset with duplicate records

Below are the steps to eliminate duplicates in Google Sheets:

  1. Select the dataset from which you want to remove the duplicate records
Select the data range
  1. Go to the Data option in the menu, then click Data cleanup
Go to the Data option in the menu then click Data cleanup to show the remove duplicates option
  1. Click on the Remove Duplicates option
  2. 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
  3. Make sure ‘Select All’ is selected (in the ‘Columns to Analyze’ section)
    Click on Select All columns option
  4. Click on the ‘Remove duplicates’ button.
    Check the Option Data has header row
  5. You will get the following prompt, and you can click OK
You will get the following prompt that shows how many rows will be removed and you can click ok

If you check only one column in the remove duplicates menu, then it will remove duplicates that appear in that column and the entire row that duplicate appears, unlike when you select all.

Select on column instead of all

In this case, 6 rows have been removed when before only 5 had been removed.

The prompt shows that 6 rows have been removed when before only 5 had been removed.

What if you want to have Google Sheets remove duplicate rows based on one column? In order to remove from one column then select the specific column only and repeat the same process. This way, other columns won’t be affected when you remove the duplicates.

When you use the Google Sheets ‘Remove Duplicates’ option to get rid of the duplicate records, it won’t impact the data around it. 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)

Also read: How to Compare Two Columns in Google Sheets

spreadsheet fundamentals

How to Remove Duplicates in Google Sheets Using the UNIQUE function

Google Sheets also has a function that you can use to delete duplicates Google Sheets by removing duplicate values and keeping the unique values only.

It’s the UNIQUE function.

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

Dataset with duplicate records

The below formula would remove all the duplicate rows, 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 will 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.

If 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)))

Also read: How to Keep Leading Zeros in Google Sheets

How to Find and Delete Duplicates in Google Sheets

Sometimes you might want to remove duplicates in a spreadsheet in a more discriminate way rather than just removing all the duplicates. In this case, you would first have to find the duplicates in your data.

You can highlight the duplicates in the data range by using conditional formatting. Here’s how to do it:

  1. Select the range of data
  2. Go to Format, then Conditional formatting
Go to Format then conditional formatting
  1. In the conditional formatting window, go to the Format rules drop-down menu and select Custom formula is.
In the conditional formatting window go to the format rules drop down menu and select custom formula is
  1. Input the formula =(COUNTIF($A$1:$A,$A1)>1)*(COUNTIF($B$1:$B,$B1)>1)
Type in the count if formula

You’ll see that the duplicates in your data are highlighted. You can change the highlight color on the conditional format menu.

The duplicates in your data are highlighted.

You can then manually delete the rows you want by using backspace or using the delete row option.

How to 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 remover 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’ tab
    Click 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-right
    Enter Remove Duplicates in the Add on search bar
  5. In the list of add-ons that are shown, click on the ‘Remove Duplicate’ add-on
    Click 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 menu
    Click 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 selected
    Select 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 rows.
    Select Delete rows within selection
  9. Click on Finish.

The above steps would instantly remove the duplicate in Google Sheets 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

Conclusion

In this article, we have demonstrated how to get rid of duplicates in Google Sheets in three ways: Using the remove duplicate menu, using the unique function and by highlighting and deleting manually.

There are more ways to do this, but these are the easiest and most straightforward ways. Now that we have shown you how to delete duplicates in Google Sheets, you should be able to do it on your own.

If you found this guide on how to remove duplicates in Google Sheets helpful, you may also like the following tutorials:

Most Popular Posts

Sumit

Sumit

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

4 thoughts on “How To Remove Duplicates In Google Sheets (3 Easy Ways)”

  1. Thank you so much for sharing the video.

    I was using different time consuming tools to remove the duplicates and suddenly got this video. Really helpful!

    Reply

Leave a Comment

";