How to remove duplicates in Google Sheets (2 easy ways)


Dealing with data sets and analyzing them seems to be a small part of most job profiles these days. The complexity of the process may vary, and no matter, how big the data sets are, we all come across some generic problems. One of them is when we come across duplicate data. Well, we can filter out duplicate data, but with big data sets, it can be real trouble.

So, today on InkedFreedom, I will talk about how to remove duplicates on Google Sheets. Google Sheets is a popular spreadsheet application these days, and it can also be useful for you if you or your organization use Google Sheets for dealing with data sets.

How to remove duplicates on Google Sheets?

If you have a really big data set, and you are spending sleepless nights thinking, how do I remove duplicates in Google Sheets, it is a really simple process. You don’t necessarily need to mug up a formula for this purpose.

Simply, import your sheet to Google Sheets if it is not already there, and select the cells containing the data, excluding the heading. 

Now, click on ‘Data’ in the menu bar, as shown in the screenshot below. Next, hover your mouse pointer to ‘Data cleanup’, and finally, click on ‘Remove Duplicates’.


Now, as you have already included the heading, uncheck the option that says, ‘Data has header row’.

Now, you have to choose the column that you want to analyze. Let’s have a look at the most common conditions.

Condition 1: In this case, I want to remove the records with duplicate names. As the names are in Column A, I will simply uncheck the other columns to remove duplicates in Google Sheets.

Condition 2: However, If you select both the columns in the following data set, Google Sheets will remove all those records having the same name and salary. So, in my data set, one entry of Murphy having a salary of 25000 will be removed, as there are two entries with Murphy having a salary of 25000.

So, you basically have to choose the column(s) that need to be analyzed. After you are done, click on ‘Remove duplicates’.


Now, all the duplicate entries based on Condition 1 are removed, as I have selected the specific condition.

So, that’s how to remove duplicates on Google Sheets. How easy was that!

How to remove duplicates on Google Sheets using the UNIQUE function?

Well, if you want to keep the original data set intact, and create a new data set excluding the duplicates, you can use the UNIQUE function that is unique to Google Docs. UNIQUE is a formula to remove duplicates in Google Sheets.

Simply find some empty space within your sheet, and type in the following formula.

=UNIQUE(Range)

In our case, the actual data range is A2 to B30. So, the formula will be “=UNIQUE(A2:B30)” without the quotes.

Instantly, you will get the new data set without the duplicates.


However, if you want to create the new data set in a new sheet, simply, use the name of the sheet before the range.

So, if I want to create the new data set without the duplicates in Sheet 2, my formula will simply go as follows.

=UNIQUE(Sheet1!A2:B30)


Here ‘Sheet1’ is for the name of the sheet. If the name of the sheet is something else, use that name instead of Sheet1.

But, if you are using the UNIQUE formula, the point to note here is that you have to select the whole data, and the duplicate records will be analyzed. So, in this case, an entry will be removed, if both the name and salary match, which is Murphy in our data set. 

With the UNIQUE formula, it is not possible to remove those records based only on one column, which can be the name or salary in our case.

So, that was all about the formula to remove duplicates in Google Sheets. It is kind of half-boiled, but can still serve several purposes.

But, you can still use the SORT formula to sort the data based on one column, which is the name in our case. That way, it will become visibly easier to find duplicate names that can be manually deleted later.

So, that was all about how to remove duplicates on Google Sheets. Do you have any questions? Feel free to comment on the same below.


Comments