How To Find Duplicates In Google Sheets
Quick Navigation
If you work with a lot of data in Google Sheets, you’ve probably worked on spreadsheets with accidental duplicate entries.
Having incorrect data of any kind in your spreadsheet can cause problems, so it’s important to find any duplicates in your spreadsheet so you can decide how to fix the issue.
The quickest way to find duplicates is to add a conditional formatting rule which highlights all duplicates in the sheet. Duplicates can then be seen easily as you scroll through your data.
Using Conditional Formatting to Highlight Duplicates
Follow these steps to add a conditional formatting rule that highlights duplicates:
Step 1
Select the range in which you want to find duplicates.
Usually this will be a single column of data such as a list of users, products, locations, etc. To select the entire column, click on the column letter at the top of the column.
For this example, we will selected the range A1:A1000 to look for duplicates in column A
Step 2
Next, click on the Format menu and select Conditional Formatting to open the Conditional Formatting sidebar
Step 3
A new default conditional formatting rule will be added to the selected range
Step 4
Under Format rules, you’ll see a dropdown for Format cells if. Click on this dropdown and choose the bottom option: “Custom formula is”.
An empty field will appear below the dropdown. This is where we’ll enter the formula to check for duplicates.
Step 5
The general form of this formula is “=COUNTIF( RangeToSearch, FirstCellOfRange )>1” where the RangeToSearch should be the entire range we’re checking for duplicates and FirstCellOfRange is the first cell in that range.
In this example where we’re searching for duplicates in column A, the formula is “=COUNTIF($A$1:$A$1000, $A1)>1”
Note the dollar signs ($) inserted in the range references.
These are important for the formula’s functionality because they create what is called an absolute reference which is needed for this formula.
Be sure to replicate the placement of the dollar signs exactly when you enter this formula.
Step 6
Once you’ve entered the formula, you’ll see the duplicates highlight in the spreadsheet
You can optionally change the highlight color by clicking the background color formatting icon and selecting a different highlight color
Click Done and the conditional formatting rule will be saved. All duplicates in the range will now be highlighted and easy to spot when you scroll through your document
Summary
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to group rows in Google Sheets. Want more? Check out all the Google Sheets Tutorials.