How to Highlight Duplicates in Google Sheets (Step-by-Step)

Watch Video – Highlight Duplicates in Google Sheets

When working with data in Google Sheets, sooner or later, you will come across the issues of duplicate data.

This could be duplicate data in the same column or duplicate rows in a dataset.

With a little bit of conditional formatting, you can easily highlight duplicates in Google Sheets. Once you have these highlighted, you can decide whether to keep them or delete them.

In this tutorial, I will show you some simple ways to highlight duplicates in Google Sheets.

So let’s get started!

Table of Contents

Highlight Duplicate Cells in a Column

The most common situation is when you have a dataset in a column and you want to quickly want to highlight the duplicates.

For example, suppose you have a dataset as shown below where you want to highlight all the names that repeat in column A.

Below are the steps to highlight duplicates in a column:

Select the names dataset (excluding the headers)
Click the Format option in the menu
In the options that show up, click on Conditional formatting. This will open the Conditional format rules pane on the right side
Click on the ‘Add another rule’ option
Make sure the range (where we need to highlight the duplicates) is correct. In case it isn’t, you can change it from the ‘Apply to range’ section
Click on the ‘Format cells if’ drop-down and then click on the ‘Custom formula is’ option
In the field below, enter the following formula:
```
=COUNTIF($A$2:$A$10,A2)>1
```
From the ‘Formatting style’ options, specify the formatting in which you want to highlight the duplicate cells. By default, it will use the green color, but you can specify other colors as well as styles such as bold or italics
Click on Done

The above steps would highlight all the cells with duplicate names in the specified color.

One great thing about conditional formatting is that it is dynamic. This means that in case you change the data in any of the cells, the formatting would update automatically. For example, if you remove one of the names that have a duplicate, the highlight from that name (in the other cell) would go away as it has now become unique.

How does this work?

When you use the custom formula in Conditional formatting, it checks each cell using the specified formula.

If the formula returns TRUE for the cell, it is highlighted in the specified format, and if it returns FALSE, it’s not.

In the above example, each cell is checked and if the name appears more than once in range, it returns TRUE for the COUNTIF formula, and the cell gets highlighted. Else, it remains unchanged.

Also, note that I have used the range $A$2:$A$10 (where there is a dollar sign before the column alphabet and the row number). This is really crucial, as it makes sure that when the formula goes to the next cell (in the row below), the overall range that is being checked for the name count remains the same.

In case you want to remove the highlighted cells, you need to remove the conditional formatting. To do this, select the cells that have the formatting applied, click on the Format option, click on Conditional Formatting, and delete the rule from the pane that opens on the right.

Highlight Duplicate Cells in Multiple Columns

In the above example, we had all the names in a single column.

But what if the names are in multiple columns (as shown below).

You can still use conditional formatting to highlight the duplicate names (which would be a name that occurs more than once in all the three columns combined.

Below are the steps to highlight duplicates in multiple columns:

Select the names dataset (excluding the headers)
Click the Format option in the menu
In the options that show up, click on Conditional formatting.
Click on the ‘Add another rule’ option
Make sure the range (where we need to highlight the duplicates) is correct. In case it isn’t, you can change it from the ‘Apply to range’ section
Click on the ‘Format cells if’ drop-down and then click on the ‘Custom formula is’ option
In the field below, enter the following formula:
```
=COUNTIF($A$2:$C$10,A2)>1
```
From the ‘Formatting style’ options, specify the formatting in which you want to highlight the duplicate cells. By default, it will use the green color, but you can specify other colors as well as styles such as bold or italics
Click on Done

The above steps would highlight the cell if the name appears more than once in all the three selected columns combined.

How does this work?

This one also worked as the last one.

In the COUNTIF formula, we have covered all the cells in the three columns. So each cell in the range is checked using the specified formula and returns either TRUE or FALSE.

If there is a name that repeats in any of the columns, it will be highlighted in the specified format.

Again, note that I have used the range $A$2:$C$10 (where there is a dollar sign before the column alphabet and the row number). This is really crucial, as it makes sure that the range remains the same while conditional formatting is checking for the count of the name in a cell.

Highlight Duplicate Rows/Records

This one is a bit tricky.

Suppose you have a dataset as shown below and you want to highlight all the duplicate records.

In this case, a record will be a duplicate if it has the exact same value in each cell in the row (such as row 2 and 7 in the above example).

The reason this is a bit tricky is that you don’t have to check individual cells now. You have to check the entire row and only highlight those rows where all the cells repeat.

But don’t worry, it’s not too difficult to do.

Below are the steps to highlight duplicate rows using conditional formatting:

Select the dataset (excluding the headers)
Click the Format option in the menu
In the options that show up, click on Conditional formatting.
Click on the ‘Add another rule’ option
Click on the ‘Format cells if’ drop-down and then click on the ‘Custom formula is’ option
In the field below, enter the following formula:
```
=COUNTIF(ARRAYFORMULA($A$2:$A$10&$B$2:$B$10&$C$2:$C$10),$A2&$B2&$C2)>1
```
From the ‘Formatting style’ options, specify the formatting in which you want to highlight the duplicate cells. By default, it will use the green color, but you can specify other colors as well as styles such as bold or italics
Click on Done

The above steps would highlight all the records that are repeated in the dataset (as shown below).

How does this work?

This one works the same way as our first example (where we simply highlighted the cells in a column that had duplicates).

But since there is an entire row that we need to compare with all other rows, we have combined the content of all the rows and created a single string for each row.

The following part of the formula creates an array of strings where all the cell content in a row are combined (done the concatenation using the ampersand sign).

ARRAYFORMULA($A$2:$A$10&$B$2:$B$10&$C$2:$C$10)

This array is used in the Countif formula, and the condition used is again a concatenated string that has all the values in a row. This is done using the following as the criteria:

$A2&$B2&$C2

This now has been converted into a simple column type construct where the COUNTIF function checks how many times this combined string repeats in the array of string we created.

This will end up highlighting all the records that are duplicates.

Google Sheets Not Highlighting Duplicates – Possible Reasons

Sometimes, it may happen that you follow all the above steps and use the same formulas, but Google Sheets still doesn’t highlight the duplicates.

Here are some possible reasons that you can check for:

Extra Spaces in the Cells

Are there any extra spaces (leading or trailing space characters) in the text in one cell and not the other?

Since we are looking for an exact match for two or more cells to be considered duplicates, if there are any extra space characters in the cells, this would lead to mismatch.

So even if you can see that there is a duplicate, it may not get highlighted.

To get rid of this, you can use the TRIM function (and the CLEAN function) to get rid of all the extra space characters.

Incorrect reference

There are three different kinds of references in Google Sheets.

Absolute references (example – $A$1)
Relative references (example – A1)
Mixed references (example – $A1 or A$1)

If a formula calls for one type of reference and you end up using the others, you will likely end up with an issue.

So check the references to make sure Google Sheets highlights the duplicates as expected.

So these are the ways you can highlight duplicates in Google Sheets using conditional formatting.

I hope you found this tutorial useful!

Other Google Sheets tutorials you may like:

Sumit

Google Sheets and Microsoft Excel Expert.

Leave a Comment Cancel reply

Best Online Google Sheets Courses

Become a Google Sheets expert today!