How to☝️ Remove Duplicates in Google Sheets

Removing duplicates in Google Sheets is a function that allows you to find and remove duplicate values in your data. This can be useful if you have a large dataset and want to clean it up, or if you want to make sure that there are no duplicates in your data before you perform any further analysis.

Trying to remove duplicate data in a dataset in Google Sheets can be a daunting task if you try to do it manually. Large quantities of data can be overwhelming, and visually recognizing the same items may not be simple.

In this article, we will learn how to remove duplicate values in Google Sheets quickly and easily.

Sample Data 

Let’s start with the sample data below, which has subcategories in column A and sales indicators in columns B, C, and D.

Sample Data

5 Ways to Remove Duplicates in Google Sheets 

Google Sheets offers several different ways to remove duplicates in your dataset. Pick the method that works best for you. Let’s get down to business!

How to Remove Duplicates in Google Sheets with the Remove Duplicates Tool 

1. Select the data where you need duplicates removed (A1:D15).

2. Go to the Data tab.

3. Click on the Data cleanup option.

4. Choose Remove duplicates.

How to Remove Duplicates in Google Sheets with the Remove Duplicates Tool

5. Add a checkmark beside each column where you want duplicates removed.

6. Click Remove duplicates.

Remove duplicates

7. Finally, click OK.

The Remove duplicates option

Easy as ABC! The duplicates have been swiftly eliminated.

Data

How to Remove Duplicates in Google Sheets with Formulas

This method is slightly different in that it does not remove the duplicate values. Rather, it creates a new dataset with the duplicates removed, so you end up with two datasets: the original and the one without the duplicates.

Formula 1

1.  Click on the cell where you want the new dataset to start.

2. Type the formula =UNIQUE(A1:D15) in the Formula bar, where (A1:D15) is the range with the data we need.

UNIQUE formula

3. Press the Enter key on your keyboard.

Indicators

Formula 2

The following formula labels each row as TRUE or FALSE depending on the values in a particular column, thus helping you find duplicates.

1. Choose the cell where you need to insert the data.

2. Type the formula =COUNTIF(B$2:B2,B2)>1 in the Formula bar, where B is the column with the values you need to check.

COUNTIF formula

3. Again, on your keyboard, press the Enter key.

True and False indicators

Formula 3

1.  Pick the cell where you want the new dataset to start.

2. Type the formula =ArrayFormula(UNIQUE(TRIM(A1:D15))) in the Formula bar, where (A1:D15) is the range with the data we want to review for duplicates.

Array Formula

3. Hit the Enter key on your keyboard.

Removed Duplicates

You’ve done it!

How to Remove Duplicates in Google Sheets with Conditional Formatting

1. Highlight the cells with the values you want to review for duplicates (A1:D15).

2. Navigate to the Format tab.

3. Click on the Conditional formatting… option.

Conditional formatting

4. Under Format rules, for Format cells if…, select the format rule Custom formula is.

5. Type the formula =COUNTIF($B$2:$B2,B2)>1.

6. Choose a color in the Formatting style section to highlight any duplicates.

7. Hit Done.

Formatting style

8. Select the data (A1:D15).

9. Go to the Filter views button and click on the little arrow to the side to reveal the drop-down menu.

10. Choose Create new filter view.

Create new filter view

11. Click the Filter icon in the header row.

12. Pick the Sort by color option.

13. Select Fill Color.

14. Choose the color you selected to highlight duplicates.

The Fill Color option

15. Select the rows with the duplicates that appear at the top of the list.

Duplicates


16. Right-click the selected rows and select Delete selected rows to remove the rows from your dataset.

Data

There you go! You have removed the duplicates.

For a better understanding of how to create conditional formatting in Google Sheets, we recommend that you check it out here.

How to Remove Duplicates in Google Sheets with Pivot Tables

1. Select your dataset (A1:D15).

2. Navigate to the Insert menu.

3. Choose the Pivot Table option.

Pivot Table

4. Pick the sheet where you want to insert the data. 

5. Click Create.

Create Pivot Table

6. Hit the Add button to add rows to the table.

7. Pick the row or rows you need (here we use all the rows).

Pivot table editor

8. In the Pivot table editor, go to the Values tab.

9. Add each column to the Values tab. Under Summarize by, select COUNT, and under Show as, select Default. Do this for each one.

COUNT options

That’s it! Here you can see the number of duplicates—each item that has a number greater than 1 beside it.

The Removed Duplicates

How to Remove Duplicates in Google Sheets with Add-ons

Before removing duplicates in this way, you need to install the add-on.

1. Go to the Extensions menu.

2. Choose the Add-ons option.

3. Pick Get add-ons.

Extensions menu

4.  In the Search box, type Remove Duplicates.

5. Choose the required add-on.

Search box

6. Click the Install tab.

Remove Duplicates Installation

7. Hit Continue.

Remove Duplicates Permission

8. Choose an account to continue to install the Remove Duplicates add-on.

Choose an account

9. Confirm the installation and click Allow.

Confirm the installation

Now that the application is installed…

10. Select your dataset (A1:D15).

11. Navigate to the Extension menu.

12. Click the Add-ons option.

13. Pick View document add-ons.

The View document add-ons option

14. Click the Use button on the Document add-ons menu.

15. Choose Find duplicate or unique rows.

Find duplicate or unique rows.

16. Type the range of your data (A1:D15).

17. Check the option to create a backup copy of the sheet.

18. Hit the Next button.

Find duplicate

19. Under Find, select Duplicates.

20. Click Next.

Find duplicates or unique row

21. Select the columns to search by adding checkmarks beside each one.

22. Again, click Next.

Find duplicates

23. Pick Delete rows within selection.

24. Hit Finish.

Delete rows within selection

And here is the result!

Data

There are a few different ways to remove duplicates in Google Sheets. The most common way is to use the Remove Duplicates tool, which can be found under the Data tab. Alternatively, you can use the Unique function through the use of formulas, apply Conditional Formatting in combination with the Filter function, set up a Pivot Table, or use a specific add-on to help you find the duplicates in your data.

Whichever method you choose, make sure you understand how it works before you apply it to your data to make sure you don’t accidentally delete important information.

More Google Sheets Tutorials
More Microsoft Excel Tutorials
Share This Post
Daniel Smith
Daniel Smith
Daniel Smith is automation consultant with a passion for technology, data, AI, and machine learning. Daniel loves to learn about new technologies and how they can be applied to solve complex problems. He is also a big fan of productivity hacks and enjoys finding ways to automate tasks to make organizations more efficient.