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.

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 menu
3. In the options that show up, click on Conditional formatting. This will open the Conditional format rules pane on the right side
4. Click on the ‘Add another rule’ option
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’ option
7. In the field below, enter the following formula:

   =COUNTIF($A$2:$A$10,A2)>1

   

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 italics
9. Click on Done

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

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 menu
3. In the options that show up, click on Conditional formatting.
4. Click on the ‘Add another rule’ option
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’ option
7. In the field below, enter the following formula:

   =COUNTIF($A$2:$C$10,A2)>1

   

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 italics
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.

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

   

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 italics
8. 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.

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:

• Apply Conditional Formatting based on Another Cell Value in Google Sheets
• How To Remove Duplicates In Google Sheets
• How to Count Cells with Specific Text In Google Sheets
• How to Compare Two Columns in Google Sheets (for Matches & Differences)
• How to Search in Google Sheets
• How to Find Unique Values in Google Sheets
• How to Highlight Highest or Lowest Value in Google Sheets

Related posts:

How to Show Formulas in Google Sheets (instead of the value) How to Use SUMIFS Function in Google Sheets (Multiple Criteria) Google Sheets NOT Function: How to Use IF/NOT Step-by-Step How to Use IRR Function in Google Sheets [with Example]