How to find duplicates in Google Sheets

For recruitment and other fields, we often use Google Forms to get candidate or customer data. The results will be saved to Google Sheets like Excel, so data duplication is inevitable. So how to find duplicates in Google Sheets? Excel is easy to find solutions on the internet, but Google Sheets has a different way of filtering. I will share with you my best ways in this post.

Filter duplicate data in Google Sheets
How to find duplicates in Google Sheets

There are many duplicate names in the list above. We can find duplicates and filter them in many ways, depending on your needs.

Highlight duplicates in Google Sheets

To find duplicate data in Google Sheets, we select the column containing the data range. Click Format, select  Conditional Formatting.

Highlight (find) duplicate data
Highlight duplicates in Google Sheets

In the Conditional Formatting Rules table, we fill in the formula as shown below.

Fill in the formula
Fill in the formula

=Countif(A:A;A1)>1

Note: if the semi-colon is not correct for your device, please change it to the comma. Change the letter A to the letter of the column you are formatting in your file.

So now that you can easily see the duplicate data, we will filter them next. There are 2 ways to find duplicates in Google Sheets.

Find duplicates or unique in Google Sheets

I insert a row above for naming. In the first next cell, enter the formula shown below and drag the formula down the entire column.

Filter out data that matches or doesn't match
Filter out data that matches or doesn’t match

=countif(A:A;A2)=1

TRUE is the data that does not overlap, and FALSE is the data that does not match. We click on range number 1 to select the range of names, click  Data, and select  Filter.

Click on range 1 to select a range of names, click Data and select Filter
Click on range 1 to select a range of names, click Data, and select Filter

Next, click the filter button. Now we want to see data that do not match, keep TRUE, and see duplicate data, keep FALSE.

If they do not match, keep TRUE, if they match, keep FALSE
If they do not match, keep TRUE. If they match, keep FALSE

And the result is obtained when we keep TRUE – the data does not match.

Result obtained when we keep TRUE
Result obtained when we keep TRUE.

But this way is not optimal. We can only see if it matches or doesn’t match. We dream of a complete list of data, only filtering out the duplicated data twice or more. I guarantee this is the first article that appeared on Google to help you do this. haha

Find duplicates in Google Sheets appear 2 times

Select the first cell next to the column containing the data. I select column B, corresponding to A2 will be B2. In this cell, you enter the formula as shown below.

Filter out data appearing 2nd time onwards
Find duplicates in Google Sheets appear 2 times.

Thus, the cells that repeat the second time onwards will change to Trangngocminhhieu.com.

=if(countif($A$2:A2;A2)=1;A2;”Tranngocminhhieu.com”)

Explain:

$A$2: is to be fixed without changing

A2: will change when scrolling down

“Tranngocminhhieu.com”: You can write whatever you want

To filter the data that appears for the first time, check the filter button > uncheck Trangngocminhhieu.com > OK.

Unchecking Trangngocminhhieu.com is to remove duplicate data
Unchecking Trangngocminhhieu.com is to remove duplicate data

Isn’t that great, hehe

The results of filtering data are not duplicated
The results of filtering data are not duplicated

As a result, you have a complete list of data without any loss of data and no duplicate data. You can also see which data matches in the highlighted box.

Remove duplicate data on Google Sheets

Google Sheets now has a feature to remove duplicate data. You just need to select the column you want to delete duplicate data > Data > Data cleanup > Remove duplicates.

How to remove duplicate data in Google Sheets
How to remove duplicate data in Google Sheets

Epilogue

Too great, is not it. The final filter will help you do many of those tasks, such as sending emails or mass messaging. I suggest one more article related to this topic: merging multiple Excel and CSV files into 1 Sheet. How do you feel about the above methods and how difficult it is? Would you mind leaving a comment below the article?

Related articles

Trần Ngọc Minh Hiếu

Trần Ngọc Minh Hiếu

I am currently working as a Data Analyst; before that, I worked in Digital Marketing. Blogging is a joy, helping me share my knowledge and experiences from life and work. You can donate to me here.

Leave a Comment