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.

How to remove duplicates in Google Sheets—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

 

  1. Click on the “Remove Duplicates” option.
Click on the Remove duplicates option.

 

  1. In the “Remove duplicates” dialog box, make sure ‘Data has header row‘ is selected (only if your data has the header row).
    Check the Data has header row option
  2. Ensure ‘Select all‘ is selected (in the ‘Columns to analyze‘ section).
    Click on Select all columns option
  3. Click on the ‘Remove duplicates‘ button.
    Check the Data has header row option
  4. You will get the following prompt, and you can click “OK.”
Prompt to confirm how many rows will be removed

 

  • 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.
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?

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

Spreadsheet fundamentals screenshot

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.

How to remove duplicates in Google Sheets—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 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.
Type the equals sign (=) and the QUERY function in a blank cell.

 

  1. Select the data range.
Select the data range.

 

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

 

  1. 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 data in the selected range without any duplicate values.

 

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.”
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.”
Conditional formatting window with drop down menu to select custom formula.

 

  1. To highlight the duplicates, input the following 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 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.
Select the data for a pivot table

 

  1. Go to “Insert” > “Pivot tables.”
Go to Insert > Pivot table.

 

  1. Choose “New sheet” or “Existing sheet,” then click “Create.”
Choose either a New or existing Sheet

 

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

 

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

 

  1. Next, go to “Value” and click “Add.”
Next go to Value and Click Add.

 

  1. Choose the same columns or rows as before.
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.

Results for Finding duplicates using a pivot table

 

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.
    Click on Add-ons option in the menu
  3. Click on “Get add-ons.”
    Click on Get Add-ons
  1. 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
  2. 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
  1. In the “Remove Duplicates” add-on screen, click on the blue “Install” button.
    Click on the BLUE install button
  2. 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.
    Allow to add the add-on to the Google Sheets

 

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
    Click the Add-on option
  1. Hover the cursor over the ‘Remove Duplicates‘ option.
  2. Click on “Find duplicate or unique rows.” This will open the “Find duplicate or unique rows” dialog box (it may take a few seconds).
    Click on Find Duplicate or Unique rows
  3. 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
  4. In Step 2, make sure “Duplicates” is selected.
    Select Duplicates option in Step 2
  5. 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
  6. In Step 4, select the ‘Delete rows within selection‘ option. This will remove the duplicate rows.
    Select Delete rows within selection
  7. 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:

Most Popular Posts

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!