5 Simple Ways to Highlight Duplicates in Google Sheets

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 can completely ruin your spreadsheets. Wouldn’t it be nice if there was a way to find all the duplicates at once? This guide will teach you how to highlight duplicates in Google Sheets easily through a number of simple methods. Read on to master them.

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 does this work?

When you use the custom formula in Conditional formatting, it checks each cell using the specified formula to search for duplicates in Google Sheets.

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.

Find Duplicates in Google Sheets by Highlighting Them 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 to perform conditional formatting for duplicates in Google Sheets(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 did for Google Sheets conditional formatting of duplicates.

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. The COUNTIF formula is kind of like the duplicate formula for Google Sheets.

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.

How to Highlight Duplicate Values In Google Sheets (Find Duplicate Rows)

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.

Show 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

Editing or Deleting Conditional Formatting Rules

When you find all the duplicates through highlighting, you may want to keep some of the duplicates but not want them to be highlighted anymore. The solution is simple, just edit or delete the conditional formatting rule. To do so:

1. Highlight the cells you applied the conditional formatting rule to
2. Navigate to Format > Conditional formatting
3. Click the trash can symbol next to the existing rule

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. If they’re not highlighted, how are you supposed to remove 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.

Some Tips When Highlighting Duplicates in Google Sheets

• Make sure you don’t have other conditional formatting rules for the desired cells that may return false results
• Make sure you don’t have missing spaces from your searches
• Don’t select the headers when using an ARRAYFORMULA for highlighting duplicates in Google Sheets

How to Check for Duplicates in Google Sheets Using UNIQUE

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 data there are in your spreadsheet. Although, it doesn’t provide conditional formatting Google Sheets duplicates.

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
   

Highlighting Cells With Conditional Formatting FAQ

Can Google Sheets Highlight Duplicates?

Yes, you can highlight duplicates. You have to use a combination of the COUNTIF formula and conditional formatting. You can set up the conditional formatting to only highlight a cell if the COUNTIF formula returns a TRUE value.

What Is the Formula to Highlight Duplicates in Google Sheets?

Here’s how to identify duplicates in Google Sheets. You just need to use the COUNTIF formula inside the conditional formatting menu. The COUNTIF formula uses the following syntax.

=COUNTIF(range, criterion)

So, you’d need to enter the range you wish to highlight and >1 as the criterion. As in, if more than one instance exists, highlight the cell. Follow the guide above more a more in-depth look.

How Do I Group Duplicates in Google Sheets?

To group duplicates, you could use the SORT function or you could use the sort shortcut in the toolbar. Here’s how to find duplicates in Google Sheets with the Sort menu:

1. Select the column you wish you group duplicates
2.  Navigate to Data > Sort sheet

If you’ve used our above method to conditionally format duplicates. You can also sort by color to avoid sorting non-duplicate cells.

How Do I See Duplicates in Google Sheets? / Can Google Sheets Find Duplicates?

You can see duplicates by using conditional formatting in the format menu and a COUNTIF formula. Then choose the color you want to highlight the duplicates in.

How Do I Highlight the Same Cell With Duplicates in Google Sheets?

Unfortunately, you can’t search for duplicates in a single cell.

How Do I Compare Two Columns In Google Sheets to Find Duplicates?

You can use the COUNTIF formula as a custom formula inside the conditional formatting menu to find duplicates and compare two columns. Simply include the cell references in the formula and use >1 as the criterion.

How Do You Highlight Duplicate Values Except for the First Instance in Google Sheets?

Follow the above method for using the highlighting cells except use >2 as the criterion instead. This will mean the highlighting will only occur if there are two or more than two instances of a value, ie,= ignoring the first match.

How Do I Compare Different Google Spreadsheets for Duplicates?

You could do this with complicated formulas or scripts but the simplest way would be to add the required sheet into a single total spreadsheet and then compare the two sheets. To do this:

1. Right-click the sheet name at bottom of the page
2. Hover over Copy to
3. Click Existing spreadsheet and select the desired spreadsheet
4. Then you can use the sheet reference in the COUNTIF formula for example if you wanted to use a cell range from Sheet1 you could type =Sheet1!

Duplicate Your Learning

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 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 Use the UNIQUE Function
• How to Highlight Highest or Lowest Value in Google Sheets