How to Highlight/Find Duplicates in Google Sheets?

Find Duplicates in Google Sheets

Here I will show you how to find duplicates in Google Sheets using highlighting and formula methods. Both methods will help you to highlight duplicates so that you can remove them at once.

Let’s check out it step by step…

While constantly working with Google Sheets, sooner or later you will come across the problem of duplicates.

When working with spread sheets, it is quite easy to miss out on something, especially with duplicates.

The primary method to do this is to feature of finding duplicates is with highlighting them.

You can look by segment for copies and consequently feature them, either by filling the cells or changing the content tone.

So, let us now show you step by step process of how to highlight duplicates in Google Sheets.

How to Highlight Duplicates and Delete at Once in Google Sheets?


Before starting the below steps, open Google sheets that you wish to check duplicates in, also make sure that it has data organized by columns and each column has a heading.

Step 1

First open the Google spread sheet that you wish to check duplicates.

Step 2

Next highlight the Column that you wish to search for.

Step 3

Now select Format > Conditional Formatting. (Here you will see the Conditional Formatting menu open)

Step 4

Confirm the cell range that you selected in step 2.

Step 5

Next under “Format Rules,” change the drop-down section tilted ‘Format cells if…’ to ‘Custom formula is.’

Step 6

Now paste the formula =countif(A:A,A1)>1 under the “Custom formula is” option.

Step 7

Next select the ‘Fill colour icon’, under “Formatting style” section to highlight your content with the colour of your choice.

Step 8

Lastly click on ‘Done’ option to save changes.

After doing the above steps, you will be able to see your duplicate cells in the colour you chose.

Once you confirm that the duplicates are not something that you need in your sheet, simply delete them.

So, this was one way to find duplicates in Google sheets, let us now discuss about an another method.

Find Duplicates in Google Sheets with Formulas

This method can be used in both rows and columns, while just applying simple formula to your Google sheets.

Follow the below steps to find duplicates in Column with a formula:

Step 1

First go to Google Docs and open the spreadsheet that you wish to check.

Step 2

Now select an open cell in the same sheet and enter the following formula.


Step 4

Now press the enter key and your formula feature will be activated.

Step 5

Next choose the column that you wish to find duplicates in by clicking the letter at the top of the column.

Step 6

Now press enter to complete the formula.

Here you will see the unique data being displayed in columns from the cell you entered the formula.


So, this was all about how to find duplicates in Google Sheets. With the above steps you will be able to highlight, identify and delete duplicate cells on the spreadsheets.

Related Posts