Thank you for subscribing!

It's great to feel loved.

How to Remove Duplicates in Google Sheets

Jun 09, 2020 3 min read

To solve the problems of duplications in your workflow, documentation, and data analysis, Google introduced a Google sheet platform that offers a built-in feature to remove duplicates.

Imagine you’ve got a list of several email addresses obtained through a form on your website. And of course, you want to determine the number of emails you’ve gathered, but you’re bothered about something.

What’s that? You are unsure if someone has wrongly filled the form on two separate occasions, which would lead to an increase in the number of gathered emails.

Chances of having duplicated cannot be entirely maneuvered whenever it involves a large amount of data in a spreadsheet. Yes, you’re bound to have duplicates, which would render your work inaccurate. This can either be as a result of robots or human error. Regardless of the reasons, these duplicates can influence your documentation, workflow, and data analysis.

To solve the problems of duplications in your workflow, documentation, and data analysis, Google introduced a Google sheet platform that offers a built-in feature to remove duplicates. In this piece, we’ll briefly discuss the best methods to remove duplicates in Google sheets. Ready? Let’s go:

Delete Duplicates with the “Remove Duplicates” 

Removing duplicates isn’t a new thing as many people do that in Google sheets. Suppose you’ve got a set of data and you want to eliminate all the duplicates as shown in the illustration below:

Remove Duplicates    


Now, let’s move to the steps involved in remove duplicates from the data set using Google sheets.

  • Select the specific data set that you want to remove duplicate records.
  • Click the “Data” option, as shown in the menu.
data option    


The next is to simply click on the “Remove Duplicate” icon.

remove duplicates icon    

 

  • In the Remove Duplicates dialog box, you should ensure that you select the “Data has a header row.” This is usually advised if your data has the header row.
data header    
  • Click the “Select All” icon in the section that contains the columns to analyze.
select all    
  • Next is to click on the “Remove Duplicates” icon.

Following the above steps will automatically remove all the duplicate data from the data set, and the result will be displayed as follows:

Using the “Remove Duplicates” icon to remove duplicate data records won’t negatively impact the surrounding data. This implies the other rows or cells will not be deleted. This function only helps to eliminate the duplicate records from the cell without affecting other cells around the data set.

Remove Duplicates with the UNIQUE Function

Apart from the “remove duplicate” function, Google sheet also offers another feature which allows users to remove the duplicate values while also keeping the unique values.

This feature is known as the UNIQUE function

For instance, you have the following data sets, and you want to eliminate the duplicate records:

duplicates table    


Here is a formula that will help remove all the duplicated records while it retains the unique ones.

  • =UNIQUE (A2: B17)

The UNIQUE formula stated above will provide the results beginning from the specific cell in which you’ve entered the formula.

The result that will be brought upon the use of this formula is a wide array of unique records that you’ll be unable to delete any part of this array result. If you try to remove any of the cells in the array result, it won’t be possible. And if you choose to overwrite any of the cells, the entire result will disappear, leaving you with a #REF! Error.

Usually, there is one limitation when it comes to using the UNIQUE function to remove duplicated records. That’s the fact that it would only consider repeated records in the entire row as duplicates. For instance, if you only want to keep a country name and delete all other details, the UNIQUE feature will only perform this action if the remaining column values for that same records are similar.

In cases where there are leading, trailing, or extra spaces in the available data, the UNIQUE feature will consider the records as different. In such situations, you can then use the formula below.

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

There you go! The best ways to remove duplicate records from Google sheet. Apart from the two earlier discussed ways, there is another third way of removing duplicates from your records. This is known as Remove Duplicate Using Add-on. This is also one of the most effective ways to remove duplicates files using Google Sheet Add-ons.

Mike
Written by Mike

Former data analyst and the head of Whatagraph blog team. A loving owner of two huskies, too.

If you’re into digital marketing and CEO, you probably know how much information you have to track and evaluate on a regular basis. Luckily, there’s a free Google sheet analytics add-on that will make it easier for you to access, visualize, understand, share and manipulate data from your Google Analytics in your Google Sheet.
Read more...
Mike
Feb 03, 2020 5 min read
Add-Ons are third party plugins that add more features in your spreadsheets and provide additional functionality to already powerful Google Sheets.
Read more...
Krystyna Krajevskaja
Jan 31, 2020 6 min read
Google Spreadsheets is one of the best tools you can use for analysis, especially when you work with numbers. There’s so much more than creating a sheet. With the right Google sheets formulas, you can extract useful insights for your business as well as manipulate information to make the right decisions.
Read more...
Wendy
Jan 31, 2020 6 min read