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 issue 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. This is usually the easiest way to find 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!

How to Highlight Duplicates in Google Sheets – Cells in a Column

The most common situation that you will need Google Sheets to highlight duplicates 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.

Names data with duplicate

Below are the steps to highlight duplicates in a column:

  1. Select the names dataset (excluding the headers)
  2. Click the Format option in the menuClick the format option
  3. In the options that show up, click on Conditional formatting. This will open the Conditional format rules pane on the right sideClick on conditional formatting
  4. Click on the ‘Add another rule’ optionClick on add another rule
  5. 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’ sectionCheck the range
  6. Click on the ‘Format cells if’ drop-down and then click on the ‘Custom formula is’ optionClick on custom formula is option in the drop down
  7. In the field below, enter the following formula:
    =COUNTIF($A$2:$A$10,A2)>1

    Enter the formula to highlight duplicates

  8. 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 italicsSpecify the formatting in which to highlight duplicates
  9. Click on DoneClick on done

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

Dataset where duplicates are highlighted

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.

How to Find Duplicates In Google Sheets – Highlight 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:

  1. Select the names dataset (excluding the headers)
  2. Click the Format option in the menuClick the format option
  3. In the options that show up, click on Conditional formatting.
  4. Click on the ‘Add another rule’ optionClick on add another rule
  5. 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
  6. Click on the ‘Format cells if’ drop-down and then click on the ‘Custom formula is’ optionClick on custom formula is option in the drop down
  7. In the field below, enter the following formula:
    =COUNTIF($A$2:$C$10,A2)>1

    Formula to highlight duplicates in multiple columns

  8. 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 italicsFormula to highlight duplicates - multiple columns
  9. Click on Done

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

Dataset where duplicates are highlighted in multiple columns

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 Google Sheets to find all the duplicates through highlighting.

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:

  1. Select the dataset (excluding the headers)
  2. Click the Format option in the menu
  3. In the options that show up, click on Conditional formatting.
  4. Click on the ‘Add another rule’ option
  5. Click on the ‘Format cells if’ drop-down and then click on the ‘Custom formula is’ option
  6. 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

    Enter the formula to highlight duplicates in multiple columns

  7. 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 italicsFormatting for duplicate highlight in rows
  8. Click on Done

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

Row Duplicates highlighted

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.

Highlight Duplicates in Google Sheets With Added Criteria

Google sheets can also use most added criteria you could think of to highlight duplicate data. For example, you can set the system to only highlight duplicates for specific values.

The syntax would have to use the “*” (and) operator so that COUNTIF will use both criteria. The syntax would look something like this:

=(countif(Range,Criteria)>1) * (New Condition) )

We can use our employee data from before to show how this would work. Let’s pretend we found out that there actually were two Henrys in marketing so we updated the Id number of one of them.

We still want to highlight the duplicate employees, so we can add a second condition to be met like so:

  1. Navigate the conditional formatting rules like in the previous examples above
  2. Enter the first part of the formula with the range and format that we should be familiar with by now of =(countif(Range,Criteria)>1) so our example would be:
=(Countif($A$2:$C$10,$A2)>1)

This will show if there are duplicates in the A row but won’t exclude the now mismatched id of the second Henry so you’ll need these additional steps

  1. Use the “*” (and) command after the first formula
  2. Add the second condition to the syntax of (countif(Range,Criteria)>1) but make sure you’re addressing the other row – row C in this case. The whole formula should look like this:
=(Countif($A$2:$C$10,$A2)>1)*(countif($A$2:$C$10,$C2)>1)

Of course with this exact example, you could have just used the second half of the formula to get the same result but this is just a taste of how adding a second condition can be used. Depending on your needs you could also:

  • Use a different after the criteria ie >0 or <5 etc
  • Add a third criteria
  • Many other “*” conditions

Potential Problems with Simple Solutions when Highlighting Duplicate Cells in Google Sheets

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.

  1. Absolute references (example – $A$1)
  2. Relative references (example – A1)
  3. 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.

Using the UNIQUE Function Instead of Highlighting Cells in Google Sheets

This formula is much simpler to use when working with smaller data sets and you just need to see how many different unique pieces of date there are in your spreadsheet.

The syntax is simply:

=UNIQUE(Range)

So, in our previous example, all we would have to do to find all the unique values in the is:

  1. Select an empty cell (D2 in our case) and press enter
  2. Type =UNIQUE and click and drag over the cells you want to check for unique data
  3. Press enter and a list of each unique value will show in the column

 

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

Sumit

Google Sheets and Microsoft Excel Expert.

1 thought on “How to Highlight Duplicates in Google Sheets (Step-by-Step)”

Leave a Comment

Best Online Google Sheets Courses

Become a Google Sheets expert today!
View Course