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.
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.
In the Conditional Formatting Rules table, we fill in the formula as shown below.
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.
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.
Next, click the filter button. Now we want to see data that do not match, keep TRUE, and see duplicate data, keep FALSE.
And the result is obtained when we keep TRUE – the data does not match.
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.
Thus, the cells that repeat the second time onwards will change to Trangngocminhhieu.com.
$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.
Isn’t that great, hehe
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.
Find duplicates in Google Sheets with Chrome extension
I see that many of you are not very familiar with formulas, so I have updated my post with a more automated way to find duplicates in Google Sheets.
This way you can only try it for 14 days, after 14 days you have to change to another Gmail or buy a license for this tool. The price is £19.99 or about 640,000 VND to use forever for your Gmail account. If you specialize in jobs that have to filter Google Sheet data often, consider buying the author’s page here .
Step 1: Go to Add-ons > Get add-ons.
Step 2: You find the keyword “remove duplicates” and select the utility of the author EXPAND9. Don’t choose other ones. I’ve tried and found EXPAND9’s to be the most delicious and easy to use.
Step 3: You click the Install button
Step 4: You click the Continue button > select your Gmail > Allow > Done.
Step 5: You go back to the Google Sheet page with duplicate data > Add-ons > Remove Duplicates > Start to start finding duplicates in Google Sheets.
Step 6: Click the Select columns button > Check or uncheck the columns containing the data you want to filter > Save.
Step 7: Highlight or delete duplicate Google Sheet data. If you leave Highlight > click Search, you will see rows of duplicated data for the 2nd time or more highlighted in yellow like this picture.
If you choose Delete > click the Search button, the rows of duplicated data for the second time or more will be deleted.
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?