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:
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.
The next is to simply click on the “Remove Duplicate” 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.
- Click the “Select All” icon in the section that contains the columns to analyze.
- 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:
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.