How to Remove Duplicates in Google Sheets (3 Easy Ways) - Sheet Point (2023)

Watch the video - Remove duplicates in Google Sheets

The team behind Google Sheets is constantly adding new features and functionality that make things easier and faster. One of those features is the Google Sheet Duplicate Delete option, which allows your Google Sheet to easily remove duplicates. With large amounts of data, you don't have to struggle with manually finding instances of repeating data to extract.

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

How to delete repeats in Google Sheets using the Remove Duplicates tool.

Removing duplicates in Google Sheets is so common that there is now a special option to quickly remove duplicates in Google Sheets.

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

Below are the steps to remove duplicates in Google Sheets:

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

2. I go toDataoption from the menu, and then clickdata cleaning

3. click noremove duplicatesPossibility
4. In the Remove Duplicates dialog box, make sure "Data has a header" is selected (if your data has a header).
   

   

5. Make sure "Select all" is selected (in the "Columns to analyze" section).
   

   

6. Click the Remove Duplicates button.
   

   

7. You will get the following prompt and you can clickOK

Highlighting only one column in the Remove Duplicates menu removes duplicates that appear in that column and displays the entire row containing duplicates, unlike selecting all.

In this case, 6 rows have been removed while previously only 5 were removed.

What if you want Google Sheets to remove duplicate rows based on a column? To remove from a column, simply select the relevant column and repeat the same process. This way other columns will not be affected when you remove duplicates.

When you use Google Sheets' Remove Duplicates option to remove duplicate records, the surrounding data is not affected. Using it does not remove rows or delete cells. It simply deletes the duplicate records from the cells (without disturbing the cells around the record).

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 feature that allows you to delete duplicate Google SheetsRemove duplicate valuesand hold theunique valuesOnly.

It's the ONLY feature.

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

The following formula would remove all duplicate rows and you would get all unique ones:

=ÚNICO(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 edit part of this array result. Nothing happens when you try to delete a cell from the result. And if you overwrite a cell, the whole result disappears and you see a #REF! Mistake.

One of the limitations ofwith the UNIQUE functionis that it would only consider as duplicates those records that contained the entire contents of the rowrepeat. If you want to keep only one instance of a country name and exclude all others, UNIQUE will only do so if the rest of the column values ​​for that record are also the same.

If your dates haveleft, right, or extra spaces, the unique function considers the records distinct. In this case you can use the following formula:

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

read too:How to keep leading zeros in Google Sheets

How to find and delete duplicates in Google Sheets

Sometimes you may want to remove duplicates in a table in more detail than just removing all duplicates. In this case, you would first need to find the duplicates in your data.

You canMark the duplicatesin the data area with conditional formatting. See how to do it:

1. Select data range
2. Go toFormat, Thenconditional formatting

3. In the conditional formatting window, go toformat rulesdrop down menu and selectCustom formula is.

4. Insira und Formel =(CONT.SE($A$1:$A,$A1)>1)*(CONT.SE($B$1:$B,$B1)>1)

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

You can manually delete desired rows using the backspace key or the delete rows option.

How to remove duplicates with an add-on

Google Sheets typically supports a huge library of different add-ons to take care of every conceivable problem or issue.

Duplicate remover add-ons can be used for the same purpose. ORemove Duplicates Add-on von AbleBitsis one of the best add-ons for removing duplicate records from your dataset.

To use an add-in, you must first 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 you want to remove duplicates from
2. Click the Add-ons tab.
   

   

3. Click Download Addons.
   

   

4. In the add-ons dialog that opens, look for "Remove Duplicates" in the top-right box.
   

   

5. From the list of add-ons that appear, click the Remove Duplicates add-on.
   

   

6. On the Add-on Remove Duplicates screen, click the blue Install button.
   

   

7. The dialog box that appears may ask you to verify your account by signing in to Gmail. Enter the credentials and click the blue "Allow" button.
   

   

The above steps would add add-on for removing duplicates in your Google Sheets document, and now you can start using it.

Below are the steps to use this addon to delete the duplicate records in Google Sheets:

1. Select the dataset where you have duplicates that you want to remove.
2. From the menu, click the Add-ons option
   

   

3. Hover over the Remove Duplicates option.
4. Click Find Duplicate or Unique Rows. This will open the Find Duplicates or Unique Rows dialog (may take a few seconds).
   

   

5. In step 1, verify that the correct range is selected. You can also check the box to create a backup copy of your Google Sheets document.
   

   

6. In step 2, make sure Duplicates is selected
   

   

7. In step 3, indicate whether or not your data has a header and whether you want to skip itempty cellsor not.
   

   

8. In step 4, select the Exclude Rows In Selection option. This will remove duplicate rows.
   

   

9. Click Finish.

The above steps would immediately remove the duplicate in Google Sheets and you would only be left with the unique records.

Since Google Sheets already has a way to quickly remove duplicates using a built-in feature, it's best to use that whenever possible. The only reason I would recommend using the addon is if you want to do a lot more than just remove duplicates.

This add-on can also do the following:

• Paint the duplicate records
• Add a column and specify records that are duplicates
• Compare Columnsor leaves

Diploma

In this article, we will demonstrate how to remove duplicates in Google Sheets in three ways: using the remove duplicates menu, using the unique feature, and using manual highlighting and deleting.

There are several ways to do this, but these are the easiest and most straightforward ways. After we showed you how to delete duplicates in Google Sheets, you can do it yourself.

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

• • How to hide null 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