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

Watch Video – Remove Duplicates in Google Sheets

With large volumes of data, you no longer need to strain your eyes to manually look for instances of repeating data to extract. The Google Sheets “delete duplicates” option lets you remove duplicates easily.

In this tutorial, we will show you how to remove duplicates in Google Sheets in several ways.

How To Remove Duplicates in Google Sheets

1. Select the dataset with the duplicate data.
2. Go to the “Data” > “Data cleanup.”
3. Click on the “Remove duplicates” option.
4. Ensure “Select All” is selected (in the ‘Columns to analyze‘ section).
5. Click on the “Remove duplicates” button.
6. Click “OK” in the prompt.

How To 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 want to remove all the duplicated data 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 (only if your data has the header row).
   

   

5. Ensure ‘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 only check one column in the remove duplicates menu, it will remove the duplicates that appear in that column and the entire row that the duplicate appears in, 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?

To remove from one column, select the specific column only and complete the same process. This way, other columns won’t be affected when you remove the duplicates.

But, when you use the Google Sheets ‘Remove duplicates‘ option to eliminate 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). You can then easily remove the rows containing empty cells by holding CTRL, selecting the row headers, then deleting the rows.

Related: 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 duplicates where the entire row content repeat. In case you only want to keep one instance of a country name and delete all the others, 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 differently. 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 Remove Duplicates in Google Sheets Using the QUERY Function to Remove Duplicates

Although the QUERY function in Google Sheets is not specifically designed to remove duplicates from a dataset, it can still work. The QUERY function is more commonly used for filtering and manipulating data based on specific criteria, but you can also remove duplicates in Google Sheets.

In our example, here’s how to dedupe in Google Sheets using the QUERY function:

1. Type the equals sign (=) and the QUERY function in a blank cell.

2. Select the data range.

3. Enter the statement “Select A,B,MIN(C) Group By A,B label MIN(C) ‘2022 GDP’” in quotation marks.

4. Close the brackets and click “Enter.”

The full formula we’ve used for this example is:

=QUERY(A2:C17,"Select A,B,MIN(C) Group By A,B label MIN(C) '2022 GDP' ")

The function returns the unique values in the selected range without duplicate values. What this function does is group the data in the range, thereby removing the duplicates. This formula needs another arithmetic function, and in our example, we’ve used MIN. However, you can also use SUM or MAX.

Although it is possible to deduplicate Google Sheets using the QUERY function, the formula is complex and difficult to understand.

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 duplicates in the data range by using conditional formatting rules. 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. To highlight the duplicates, input the following 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 duplicate rows in Google Sheets by using backspace or using the delete row option.

How To Find Duplicates in Google Sheets Using Pivot Tables

Pivot tables in Google Sheets automatically remove duplicates. They are used to summarize data by aggregating and condensing it.

Here’s how to filter out duplicates in Google Sheets using Pivot tables:

1. Select the data.

2. Go to “Insert” > “Pivot tables.”

3. Choose “New sheet” or “Existing sheet,” then click “Create.”

4. In the Pivot table editor, Go to “Rows” and click “Add.”

5. Choose the rows you want to view without duplicates.

6. Next, go to “Value” and click “Add.”

7. Choose the same columns or rows as before.

Once you’ve added the values, you’ll notice a column labeled COUNTA of Count. This column shows how often the value has been repeated in the data. This will help you identify duplicates.

You’ll see that your data is sorted alphabetically, and there is no duplicate data.

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 Google Sheets Remove Duplicates add-on by Ablebits is one of the best add-ons to remove duplicate entries 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 to 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 relevant details and click on the blue ‘Allow’ button.
   

   

The above steps will add the “Remove Duplicate” add-on to your Google Sheets document, and now you can start using it.

Below are the steps to use this add-on to delete 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 take 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 can 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

Which Method to Remove Duplicates in Google Sheets is Most Effective?

The most suitable method depends on the specific requirements and complexity of your data. However, the most effective method to delete duplicate entries in Google Sheets is using the In Google built-in “Remove duplicates” feature.

This method is simple and effective for removing duplicates from a single column or multiple columns at once.

Conclusion

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

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

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
• Compare Two Columns in Google Sheets