How to Remove Duplicates in Google Sheets? [ Four Methods]

Google Sheets is a spreadsheet program included as part of the free, web-based Google Docs Editors suite offered by Google. It is available on Android, iOS, Microsoft Windows, BlackBerry OS, and as a desktop application on Google’s Chrome OS.

And it is also compatible with Microsoft Excel. However, just like Excel, Google Sheets can get messed up sometimes. One of the most frustrating issues is that there are many duplicates in the Google Sheets.

So, how to remove duplicates in Google Sheets? If you choose to find and remove them one by one, it will take a lot of your time. In this post, the MiniTool Partition Wizard provides some useful and high-efficiency methods for you to remove these duplicates.

[Full Guide] How to Make a Table of Contents in Google Docs?

Have you ever wondered how to make a table of contents in Google Docs? This post provides a full guide for you to create an organized table of contents.

Read More

Method 1. Use Remove Duplicates Feature

How to remove duplicates in Google Sheets? In addition to finding and removing duplicates one by one, you can also use the Remove duplicates feature to remove all the duplicate entries in your Google Sheets. Here’s the way:

Step 1. Click on the Data tab on the top toolbar to open the drop-down menu.

Step 2. Then move your mouse cursor to the Data cleanup feature and select the Remove duplicates feature from the menu.

Step 3. Then check the box that you want to analyze under Columns to analyze.

Step 4. After that, click on Remove duplicates.

Step 5. Once done, click OK to make a confirmation, and then you can successfully remove duplicates in your Google Sheets.

Method 2. Using Formulas to Remove Duplicates

Using Formulas is another effective way for removing duplicates. Here are two formulas you may use to do this task.

1. UNIQUE Formula

This formula compares each row of data and removes any duplicate rows. So, you can use it to delete duplicates in the range of data you select. The steps are as follows:

Step 1. Open your Google Sheets.

Step 2. Select a blank cell.

Step 3: Enter the formula =UNIQUE (A2:C23) to choose the cells you want.

Step 4. Press Enter on your keyboard or click the sheet.

Step 5. Once done, you will have a second table with the duplicates removed.

2. COUNTIF Formula

COUNTIF is an Excel function to count cells in a range that meet a single condition. You can set the following rules to highlight duplicates in your dataset:

• When a value shows up for the first time, the count will be 1, so the formula result will be FALSE.
• When the value shows up a second time, the count will be 2, so the formula result will be TRUE.

Here are the full steps below:

Step 1. Open your Google Sheets.

Step 2. Create a new column next to the data column you want to check for duplicates.

Step 3. Enter =COUNTIF (Range, Criteria)>1 in the top cell of the new column you created to highlight duplicates and then copy the formula down column C. In this example, the formula would be =COUNTIF(A$2:A2,A2)>1.

Step 4. After that, you can see many TRUE and many FALSE in the new column.

Step 5. Next, you need to go to the Data tab and select Create a filter.

Step 6. Once done, click the filter icon on the new column and uncheck TRUE. And then you can get the table that has hidden all the duplicates.

How to Share a Google Doc? Get the Tutorial from This Post

Do you know how to share a Google Doc with your friend, colleague, or group? In this post, you can get a step-by-step tutorial. Don’t hesitate to have a try.

Read More

Method 3.  Using Conditional Formatting to Remove Duplicates

How to remove duplicates in Google Sheets? You can use this method to highlight your duplicates and then remove the highlighted duplicates. Here’s the guide:

1. Open Google Sheets and choose your dataset.
2. Go to Format and then Conditional formatting.
3. Select Custom formula from the Format cells if And then enter the formula”=COUNTIF($A$2:$A2,A2)>1“.
4. Next, set your formatting style to highlight your duplicate data and click on the Done button.
5. Then select Create a filter from the Data tab.
6. Select the filter icon next to the header for the column you want to sort by color.
7. Select Sort by color>Fill Color, and choose the fill color you want to appear on top.
8. Select the duplicates block and press Delete.

Method 4.  Using an Add-on

Google Sheets supports a massive library of various add-ons to take care of every conceivable issue or problem. So, you can add duplicate removal add-ons to Google Sheets to remove duplicates. You can do as follows:

Step 1. Open the Google Sheets document in which you want to remove the duplicates.

Step 2. Add the “Remove Duplicates” add-on to your Google Sheets.

1. Click Add-ons tab and select Get add-ons.
2. Search for “Remove Duplicate” in the field in the top-right of the Add-ons dialog box that opens.
3. Next, click on the “Remove Duplicate” add-on in the list.
4. Click on the blue INSTALL button.
5. Log in to your Gmail account and enter the credentials to confirm your account. And then click on the blue Allow button.
6. After that, you can add the Remove Duplicate add-on to your Google Sheets successfully.

Step 3. Select the dataset in which you have the duplicates that you want to remove.

Step 4. Click the Add-ons option in the menu and select the Remove Duplicates option. And then click on Find duplicate or unique rows.

Step 5. Remove the duplicates you don’t want. There are four steps you need to take:

1. Select the range that matches your table.
2. Select Duplicates.
3. Specify whether your data has a header or not and whether you want to skip empty cells or not.
4. Select the Delete rows within selection option.

How to Remove Duplicates in Excel? Three Methods for You

Do you know how to remove many duplicates in Excel at one time? This post provides some helpful methods for you to remove duplicates in Excel.

Read More

Bottom Line

Have you already known how to remove duplicates in Google Sheets? This post provides four helpful methods for you to remove duplicates in Google Sheets. You can select the one you like to do it.

If you are interested in MiniTool Partition Wizard and want to know more about it, you can visit MiniTool Partition Wizard’s official website by clicking the hyperlink. MiniTool Partition Wizard is an all-in-one partition manager and can be used for data recovery and disk diagnosis.