How To Find Duplicates In Google Sheets

Last Updated on November 1, 2023 by Jake Sheridan

In this tutorial, you will learn how to find duplicates in Google Sheets.

How To Find Duplicates In Google Sheets

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


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.