How to Remove Duplicates in Google Sheets (3 Easy Methods)

With large amounts of data, manually searching for duplicate instances to extract can strain your eyes. Google Sheets’ “remove duplicates” option allows you to easily eliminate duplicates.

In this article, we will show you how to remove duplicates in Google Sheets using multiple methods.

Method 1: Removing Duplicates in Google Sheets

1. Select the dataset that contains the duplicate data.
2. Go to the “Data” tab and click on “Remove Duplicates”.
3. Make sure “Select All” is chosen (under the “Columns to Analyze” section).
4. Click on the “Remove Duplicates” button.
5. Confirm by clicking “OK” in the pop-up window.

Method 2: Removing Duplicates Using the “Remove Duplicates” Tool

Removing duplicates in Google Sheets is a common operation, and there is now a dedicated option to quickly remove duplicates.

Assume you have the dataset illustrated below and want to remove all duplicate data from it.

Follow these steps to eliminate duplicates in Google Sheets:

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

2. Go to the “Data” tab in the menu and click on “Remove Duplicates”.

4. In the “Remove Duplicates” dialog box, ensure that “Data has header row” is selected (only if your data contains a header row).

5. Make sure “Select All” is chosen (under the “Columns to analyze” section).

7. You will see the following pop-up window, where you can click on “OK”.

If you only check a single column in the remove duplicates menu, it will only remove duplicates that appear in that column and the entire row in which the duplicate appears. This is unlike selecting “Select All”.

In this case, 6 rows were removed instead of the previous 5.

What to Do If You Want Google Sheets to Remove Duplicates Based on a Column?

To remove a column, only select the specific column and perform the same process. This way, the other columns will not be affected when you remove duplicates.

However, when you use Google Sheets’ “Remove Duplicates” option to eliminate duplicate records, it will not impact the surrounding data.

It does not remove rows or delete any cells. It simply removes duplicate records from the cells (without disturbing the cells around the dataset). You can then easily remove rows containing empty cells by holding down the CTRL key, selecting the row headers, and deleting the rows.

Related: Comment comparer deux colonnes dans Google Sheets

Method 3: Removing Duplicates Using the “UNIQUE” Function

Google Sheets also has a function that you can use to remove duplicates by deleting duplicate values and keeping only unique values. This is the “UNIQUE” function.

Assume you have the dataset illustrated below and want to remove all duplicate records from it.

The following formula would remove all duplicate rows, giving you all the unique rows:

=UNIQUE(A2:B17)

The above formula will give you the result from the cell where you entered the formula.

The result is an array of unique records, and you cannot delete or modify any part of this array result. Nothing happens if you try to delete a cell from the result. And if you overwrite a cell, the entire result will disappear, and you will see a “#REF!” error.

One limitation of using the “UNIQUE” function is that it will only consider records as duplicates when the entire content of the row repeats. If you only want to keep a single occurrence of a country name and remove all others, UNIQUE will only do so if the rest of the column values for that record are also the same.

If your data contains unnecessary spaces, leading or trailing spaces, the “UNIQUE” function will treat the records differently. In such cases, you can use the following formula:

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

Also read: Comment conserver les zéros en début de nombre dans Google Sheets

Method 4: Removing Duplicates Using the “QUERY” Function

While the “QUERY” function in Google Sheets is not specifically designed to remove duplicates from a dataset, it can still work. The “QUERY” function is commonly used to filter and manipulate data based on specific criteria, but you can also remove duplicates in Google Sheets.

In our example, here’s how to remove duplicates in Google Sheets using the “QUERY” function:

1. Enter the “=” symbol and the “QUERY” function in an empty cell.

2. Select the data range.

3. Enter the formula “=Query(A2:C17,”Select A,B,MIN(C) Group By A,B label MIN(C) ‘2022 GDP’ “)” within quotes.

4. Close the parentheses and click “Enter”.

The complete formula we 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 unique values within the selected range without any duplicates. What this function does is group the data in the range, which eliminates duplicates. This formula requires another arithmetic function, and in our example, we used “MIN”. However, you can also use “SUM” or “MAX”.

Although it is possible to remove duplicates in Google Sheets using the “QUERY” function, the formula is complex and hard to understand.

How to Find and Remove Duplicates in Google Sheets Selectively

Sometimes, you may want to remove duplicates in a spreadsheet selectively instead of just removing all duplicates. In such cases, you need to first find duplicates in your data.

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

1. Select the data range.
2. Go to “Format” and then “Conditional Formatting”.

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

4. To highlight duplicates, enter the following formula:

=(COUNTIF($A$1:$A,$A1)>1)*(COUNTIF($B$1:$B,$B1)>1)

You will see duplicates in your data highlighted. You can change the highlight color in the conditional formatting menu.

You can then manually delete duplicate rows in Google Sheets using the “Backspace” key or by 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 duplicates in Google Sheets using pivot tables:

1. Select the data.

2. Go to “Insert” and click on “Pivot table”.

3. Choose either “New sheet” or “Existing sheet” and click “Create”.

4. In the pivot table editor, go to “Rows” and click on “Add”.

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

6. Then, go to “Values” and click on “Add”.

7. Choose the same columns or rows as before.

Once you have added the values, you will notice a column titled “Count of Count”. This column indicates how many times the value was repeated in the data. This will help you identify duplicates.

You will find that your data is sorted alphabetically and there are no duplicates.

Removing Duplicates Using an Extension

Google Sheets supports a vast library of different extensions to solve any imaginable problems.

Duplicate removal extensions can all be used for this purpose. The “Remove Duplicates” extension for Google Sheets developed by Ablebits is one of the best extensions for removing duplicates from your dataset.

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

Here are the steps to add an extension to your Google Sheets document:

1. Open the Google Sheets document in which you want to remove duplicates.
2. Click on the “Extensions” tab.
3. Click on “Get add-ons”.

4. In the “Add-ons” dialog box that opens, search for “Remove duplicate” in the top-right field.

5. Click on the “Remove Duplicate” extension in the displayed list of extensions.

6. In the “Remove Duplicates” extension screen, click on the blue “Install” button.

7. In the pop-up dialog, it may ask you to confirm your account by signing in to your Gmail account. Enter the relevant information and click the blue “Allow” button.

The above steps will add the “Remove Duplicate” extension to your Google Sheets document, and you can now use it.

Here are the steps to use this extension to remove duplicates in Google Sheets:

1. Select the dataset that has the duplicates you want to remove.
2. Click on the “Extensions” option in the menu.

3. Hover 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 might 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 if you want to ignore empty cells or not.
8. In step 4, select the option “Delete rows in selection”. This will remove duplicate rows.
9. Click on “Finish”.

The above steps will instantly remove duplicates in Google Sheets, leaving you with only unique records.

Since Google Sheets can quickly remove duplicates using a built-in function, it is advisable to use it if possible. The only reason I would recommend using the extension is when you want to do much more than just removing duplicates.

This extension can also perform the following operations:

• Color duplicate records
• Add a column and specify duplicate records
• Compare columns or sheets

Which Method of Removing Duplicates in Google Sheets is Most Effective?

The most suitable method depends on specific requirements and the complexity of your data. However, the most effective method to remove duplicate entries in Google Sheets is by using the built-in “Remove Duplicates” option.

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

Conclusion

In this article, we have shown you how to remove duplicates in Google Sheets using three methods: using the “Remove Duplicates” menu, using the “UNIQUE” function, and by manually highlighting and removing duplicates.

There are other ways to do it, but these are the easiest and simplest ones. Now that we have shown you how to remove duplicates in Google Sheets, you should be able to do it on your own.

If you found this guide on removing duplicates in Google Sheets helpful, you might also enjoy the following tutorials:

• How to hide blank 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
• Comment comparer deux colonnes dans Google Sheets

Happy sheeting!