How To Find Duplicates In Google Sheets : Best Guide 2022

Written by Amy Brink Technically reviewed by Channa S Bandara.
Publish Date : April 13, 2021 | Review Date : April 13, 2021

Google Sheet, Spread Sheet

When you deal with large data sets or unformatting data, most of the time it will accidentally add duplicate data in google sheets. It is hard to control when working with a team or user submitting data. In a google sheet, introduce several ways to find, compare, highlight, and remove duplicates data in google sheet. It will help to save a lot of your time and increase productivity.

About example

Let's take a student exam mark spreadsheet. In this spreadsheet contain 3 data row ( Student Name, Subject, Mark).every student was sitting one time for one subject. We seem that our data entry operator same record multiple time. So we need to,

Highlight which student mark record is duplicate
How to remove duplicate record

Download Example Sheet

# Easy Tip

How review duplicates record exit or not?

Before finding and removing duplicate values, you may need to find out how duplicate values exist and what kind of formulas need to implement to highlight and remove duplicates in your spreadsheet. In google sheet offer several ways to review a spreadsheet alphabetical sorting option that helps to identify duplicate data in spread rows easier to review.

Why this important ?

When you work with a large data set you couldn’t get an idea of how data arrange and which cell contains the correct record necessary for your data analysis.

Open a spreadsheet in Google Sheets.
To Highlight cells you’d like to sort areas, Click column data areas that need to sort and press Ctrl + A in windows or CMD + A in mac OS. ( Base on our example we select all cell because we need to review duplicate record base on all spreadsheet data )
Then move your mouse pointer to the top of navigation and click Data ( middle of Format and Tool navigation link ) Then Select Data > Sort Range.
Selected column head of row in sorting dialog box.
Then we need to sort our spreadsheet,
1. Select student name in the dropdown and select press sort A — Z.
2. Then add another sort of column, select subject in the dropdown and select press sort A — Z.
3. Then add another sort of column, select mark in the dropdown and select press sort Z — A.
Now your spreadsheet will sort alphabetical order and all student mark will order will be ordered group by alphabetical student name order.

This is one of the easiest ways to sort duplicate value in the spreadsheet.

Complete Alphabetical Sorting Guide

we focus 4 different way to implement Sort data in alphabetical your data in Google Sheets latest update.

Read the Tutorial !

How to highlight duplicates in google sheets (using simple custom formula) ?

This is a bit advanced method to identify duplicate in your spreadsheet. If you deal with a large set of data sheet, this method will be valuable.in Spreadsheet, in this example sheet, we added a new column to show which record duplicate or not. it names (row header name) as is duplicate.

# Step 01

Need to identify how many time the same student name present in the data set?

Open a spreadsheet in Google Sheets.
Select the first row of the ‘is duplicate’ column.
Type =(COUNTIF($A$2:$A,$A2)>1) conditional formula and press enter. You can see that the formula result appears as true. The reason is that the student name `Flona` already exit in the A8 row.
To check all student name you need to click this blue square icon & drag till the end of the student name row which data exit ( base on the above example A12 ).
In the result, true mean duplicate value and false is a pure row in the name cell.
To clearly identify, I will change the row colour to green if duplicate.
1. Select Format > Conditional Formatting
2. Enter A2:A Column range
3. A select custom formula is an option from the format rule dropdown
Enter =(COUNTIF($A$2:$A,$A2)>1) in custom condition field.

How countif conditional validate work logic work?

countif is the conditional function that uses get how many items in a range met given criteria. In the above example, we use the countif function to pull out which name already duplicate in this spreadsheet.

Add analysis range keyword `$` it will emit with the dynamic row.it will use for lock cell and colum to avoid change value when copy in to another cell.
Column name.
Row id which needs to starts formula.
Row range (if keep as letter only, that mean it will work with all row in the column)
Criterion row also needs to define as an analysis range keyword.
This range condition if value exit more than one, emit answer as true .

Always remember that column index is letter and row index is number.

Then we need to expand our formula with other columns. Because the above duplicate row identifies base on name duplicate conditional. student can sit on multiple subjects. So above result, we need to expand this single condition with multiple columns

# Step 02

Highlight duplicates using multi-column fields combination.

To mark all row which exit duplicate we use same range formula,

Select Format > Conditional Formatting
Open existing condition format which we created.
Change column range you need to validate duplicate row. ( i change as A2:A to A2:C ).
After clicking the done button in the conditions format rule dialog box, all duplicate row will highlight base on the name row condition.

But these duplicate row highlight base only on student name condition. But this parameter needs to be expanded with the subject and mark.

# Step 03

Highlight duplicates using condition parameter.

To identify duplicate row base on multiple conditions, google sheet offer AND logic for all column parameter . that describe analysis data set using all of these parameters,s not one parameter.

Student name column condition (Column index A) : COUNTIF($A$2:$A,$A2)>1)
Subject column condition (Column index B) : COUNTIF($B$2:$B,$B2)>1)
Markcolumn condition (Column index C) : COUNTIF($C$2:$C,$C2)>1)

This 3 parameter need to be combine as one parameter with AND logic operator. First wrap all parameters with parentheses and connect all formuler using asterisk symbol(*) .asterisk will operate as AND logic in this case.

				
					=(COUNTIF($A$2:$A,$A2)>1) * (COUNTIF($B$2:$B,$B2)>1)* (COUNTIF($C$2:$C,$C2)>1)

Select Format > Conditional Formatting
Open existing condition format which we created.
A select custom formula is an option from the format rule dropdown.
Type =(COUNTIF($A$2:$A,$A2)>1) * (COUNTIF($B$2:$B,$B2)>1)* (COUNTIF($C$2:$C,$C2)>1) conditional formula and press enter.
Then you can get what actual duplicate row in the student data sheet.

How to delete duplicates in google sheet?

This is a really easy step, you don’t need to use any conditions formula.

Open a spreadsheet in Google Sheets.
To Highlight cells you need to combine remove the duplicate parameter.
Select Data > Remove Duplicates.
Select data has row in dialog box if you already select with row header, if not, please ignore it .
Checked column index which you need to combine duplicate parameter.
Then click the remove duplicate button and will show the process summary.
You can get the final datasheet after removing duplicate record in the datasheet.

I know this step is complicated. But this complete guideline that how to find / highlight / remove the duplicate record in google sheet.

Conclusion

Google sheet now a very popular and fast-growing open-source spreadsheet software among the professional community. When you deal with large data set duplicate data record insert is a very common mistake. Google offer may way to sort, highlight and remove from your data set.