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.

Below are the steps to eliminate duplicates in Google Sheets:

1. Select the dataset from which you want to remove the duplicate records

2. Go to the Data option in the menu, then click Data cleanup

3. Click on the Remove Duplicates option
4. In the Remove Duplicates dialog box, make sure ‘Data has header row’ is selected (in case your data has the header row).
   

   

5. Make sure ‘Select All’ is selected (in the ‘Columns to Analyze’ section)
   

   

6. Click on the ‘Remove duplicates’ button.
   

   

7. You will get the following prompt, 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.

In this case, 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

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.

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

3. In the conditional formatting window, go to the Format rules drop-down menu and select Custom formula is.

4. Input the formula =(COUNTIF($A$1:$A,$A1)>1)*(COUNTIF($B$1:$B,$B1)>1)

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

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
   

   

3. 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
   

   

5. In the list of add-ons that are shown, click on the ‘Remove Duplicate’ add-on
   

   

6. In the Remove Duplicates add-on screen, 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.
   

   

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
   

   

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)
   

   

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.
   

   

6. In Step 2, make sure Duplicates is selected
   

   

7. In Step 3, specify whether your data has a header or not and whether you want to skip empty cells or not.
   

   

8. In Step 4, select the ‘Delete rows within selection’ option. This will remove the duplicate rows.
   

   

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:

• • How to Hide Zero Values in Google Sheets
  • How to Filter By Color in Google Sheets
  • How to Delete Empty Rows in Google Sheets
  • How to Count Cells with Specific Text in Google Sheets
  • How to Search in Google Sheets
  • Conditional Formatting Based on Another Cell