How To Highlight Duplicates in Google Sheets

Need to highlight duplicates in Google Sheets? You’re in the right place. I compiled a step-by-step guide to show you exactly what to do. My advice includes using conditional formatting to highlight duplicates, limiting your target to a specific column, using the UNIQUE function, and more.

If you’re ready for an in-depth look, read on! Here’s how to highlight duplicates in Google Sheets.

How To Highlight Duplicates in Google Sheets

Below are the steps to highlight duplicate data in a column:

  1. Select the dataset
  2. Go to “Format” > “Conditional formatting
  3. Click on the “Format cells if” drop-down and then click on “Custom formula is
  4. Enter the following formula:
    =COUNTIF(Search Range,Cell reference)>1
  1. Under “Formatting style,” specify the formatting and click “Done

How To Highlight Duplicates in Google Sheets Using a Single Column

The most common situation for highlighting duplicates in Google Sheets occurs when you have a dataset in one column. 

Suppose you have the following dataset and you want to highlight all the names that repeat in Column A.

Highlight duplicates in Google Sheets—Names data with duplicate

To highlight duplicates in Google Sheets using a single column:

  1. Select the names dataset (excluding the headers)
  2. Click on “Format” > “Conditional formatting
    Click the Format option in the menu and click conditional formatting
  3. Select the “Add another rule” option
Click on add another rule

4. Ensure the range (where you’ll highlight the duplicates) is correct. If it isn’t, change it from the “Apply to range” section.

Check the range
  • Click on “Format cells if” > “Custom formula is
    Click on custom formula is option in the drop down
  • In the field below, enter the following formula:
    =COUNTIF($A$2:$A$10,A2)>1
    Enter the formula to highlight duplicates

    7. From the “Formatting style” options, specify the formatting option to highlight the duplicate cells.

    • By default, it will use the green color, but you can specify other colors and styles (e.g., bold, italics) 
    • Click “Done” 

      Specify the formatting in which to highlight duplicates
Dataset where duplicates are highlighted

 

Remember: Conditional Formatting Is Dynamic

One great thing about conditional formatting is its dynamic nature. If you happen to
change any cell data, the formatting will update automatically.

For example, if you remove one of the names that have a duplicate, the highlight from that name (in another cell) will also disappear.

Read more: How To Copy Conditional Formatting In Google Sheets

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. In such cases, you may want to remove the duplicates altogether to clean your data.

If the formula returns TRUE for any given cell, it will highlight the data in the specified format; if it returns FALSE, the cell will not be highlighted.

In the above example, if any of the data range (names) is duplicated, it will return TRUE for the COUNTIF formula, and highlight the cell. Otherwise, it will remain unchanged.

Note: I have used the range: $A$2:$A$10 (with a dollar sign ($) before the column alphabet and the row number). This is crucial because it ensures that when the formula moves to the next cell (the row below), the range is checked for duplicated data.

If you want to remove the highlighted cells, you must remove the conditional formatting feature.

To do this, follow the steps below:

  1. Select the cells that have conditional formatting applied
  2. Click on the “Format” option
  3. Click on “Conditional Formatting
  4. Delete the conditional formatting rule from the pane that opens on the right

How To Highlight Duplicates in Multiple Columns

You can also use conditional formatting to highlight duplicates (in this case, it’s a name that occurs more than once in all three columns).

To highlight duplicate data in multiple columns, follow the steps below:

  1. Select the names dataset (excluding the headers)
  2. Click the “Format” > “Conditional formatting
Click the Format option in the menu

 

  1. Choose the “Add another rule” option
    Click on add another rule
  2. Ensure the range (where you’ll highlight duplicate cells) is correct. If it isn’t, you can change it from the “Apply to range” section.
  3. Click on the “Format cells if” drop-down and then click on the “Custom formula is” option
    Click on custom formula is option in the drop down
  4. In the field below, enter the following formula:
    =COUNTIF($A$2:$C$10,A2)>1
    Formula to highlight duplicates in multiple columns
  5. From the “Formatting style” options, specify the formatting to highlight duplicate cells.
    • By default, it will use the green color, but it’s easy to specify other colors and styles, such as bold or italics
      Formula to highlight duplicates - multiple columns
  6. Click on “Done

Note: The above steps will highlight the cell if a name appears more than once (in all three selected columns).

Dataset where duplicates are highlighted in multiple columns

 

How To Highlight Duplicate Values in Google Sheets: Finding Duplicate Rows

Suppose you have a dataset and you want to find all the duplicates through highlighting.

Row Duplicates highlighted

 

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

You don’t have to check individual cells at this point: You have to check the entire row (and only highlight the rows where all the cells repeat).

  1. Select the dataset (excluding the headers)
  2. Click the “Format” > “Conditional formatting” > “Add another rule
  3. Choose the “Format cells if” drop-down > “Custom formula is” option
  4. 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

5. From the “Formatting style” options, specify the formatting for highlighting the duplicate cells.

Formatting for duplicate highlight in rows

6. Click on “Done

How Does This Work?

Since an entire row is being compared with all other rows, we’ve combined the content of all 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 is combined, using the and 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 with all the values in a row. This is done using the following formula: 

$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 is repeated and will end up highlighting all the records that are duplicated.

How To 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 will use the asterisk symbol (*) and the and operator. This will enable the COUNTIF function to use both criteria, using the syntax below:

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

Using our data from previous sections. Let’s pretend we discovered that there were two people named Henry in the marketing department. We updated the ID number of one of them.

Changed ID number

 

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

  1. Navigate the conditional formatting rules
  2. Enter the first part of the formula with the range and format:
=(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 asterisk symbol (*) and the and operator 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)
  • In this case, we will use: =(COUNTIF($A$2:$C$10,$A2)>1) to show whether there are duplicates in the A row
  • This won’t exclude the now-mismatched ID of the second Henry, so you’ll need these additional steps
  1. Use the asterisk symbol (*) and the and operator after the first formula
  2. Add the second condition to the syntax of (COUNTIF(Range,Criteria)>1), but ensure you’re addressing the other row
  • In this case, it will be Row C 
  • The whole formula should look like this:
=(COUNTIF($A$2:$C$10,$A2)>1)*(COUNTIF($A$2:$C$10,$C2)>1)

 

how to highlight duplicates in Google Sheets with multiple criteria

 

Depending on your needs, you could also:

  • Use a different after the criteria i.e., >0 or <5 
  • Add a third criteria
  • Multiple  “*” conditions

How To Edit Conditional Formatting Rules

You may want to keep some duplicates but remove highlighting. The solution is simple: Edit or delete the conditional formatting rule:

  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
How to delete a conditional formatting rule

How To Check for Duplicates in Google Sheets Using UNIQUE

This formula is much simpler to use when working with smaller data sets. You’ll need to determine the number of unique pieces of data in your spreadsheet (although it doesn’t provide conditional formatting Google Sheets duplicates).

The syntax is:

=UNIQUE(Range)

In our previous example, you’d find all the unique values: 

  1. Select an empty cell and press “Enter
  2. Type =UNIQUE and click and drag over the cells you want to check for unique data.
How to use the UNIQUE function to find duplicates

3. Press “Enter,” and a list of each unique value will show in the column

Results after using the unique function

Potential Problems When Highlighting Duplicate Cells in Google Sheets

Occasionally, you might follow all the above steps and use the same formulas — but Google Sheets still doesn’t highlight the duplicates. There are a few issues to check for:

Extra Spaces in the Cells

Are there any extra spaces (e.g., leading, trailing characters) in one cell and not the other?

Since we’re looking for an exact match for two or more cells, any extra spaces will lead to a mismatch. Even if you can see that there is a duplicate, it may not get highlighted.

Note: You can use the TRIM function to remove all the extra space characters.

Incorrect Reference

There are three different kinds of references in Google Sheets:

  1. Absolute references (e.g., $A$1)
  2. Relative references (e.g., A1)
  3. Mixed references (e.g., $A1, A$1)

If a formula calls for one type of reference and you end up using the others, you will likely have an issue. Check the references to ensure Google Sheets highlights the duplicates correctly

Some Tips When Highlighting Duplicates in Google Sheets

  • Remove conditional formatting rules for the desired cells that can return false results
  • Remove missing spaces from your searches
  • Don’t select headers when using an ARRAYFORMULA for highlighting duplicates

Frequently Asked Questions

Can Google Sheets Highlight Duplicates?

Yes, you can highlight duplicate data. You should use a combination of the COUNTIF formula and conditional formatting. Set up the conditional formatting to highlight a cell only if the COUNTIF formula returns a TRUE value.

What Is the Formula for Highlight Duplicates in Google Sheets?

Use the COUNTIF formula inside the conditional formatting menu with the following syntax.

=COUNTIF(range, criterion)

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 can use the SORT function or the “sort shortcut” in the toolbar:

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

You can use 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? 

You can view duplicates through conditional formatting in the format menu, as well as a COUNTIF formula. Choose the color you prefer. 

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

You can’t search for duplicates in a single cell.

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

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

How Do I Compare Different Google Spreadsheets by Finding Duplicates?

Complicated formulas or scripts are possible, but we recommend combining both Sheets into a single spreadsheet, and then comparing them:

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

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 the 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!

Watch Video – Highlight Duplicates in Google Sheets

Wrapping Up

We hope that we’ve helped you learn how to highlight duplicates in Google Sheets. We provide plenty of other Google Sheets tutorials, so be sure to check them out! 

Looking to upskill your Google Sheets knowledge? We highly recommend checking out these online Udemy courses. Whether you’re a novice or expert, we guarantee there will be plenty of insider info that you don’t want to miss!

Other Google Sheets tutorials you may like:

Most Popular Posts

Sumit

Sumit

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

10 thoughts on “How To Highlight Duplicates in Google Sheets”

  1. Hi, as of 21/02/2022, this formula seems to have stopped working on google sheets.

    I also tried using “;” instead of “,” , but i get an error. and can’t click “Done” to apply the conditional format.

    Please, any sugestions?

    Reply
  2. How about when the criteria can be from any of cells?

    If there are 20 cells, and I need to check if there are duplicate entries on those non-contiguous cells, is there a way to check it that the criterion can be any entry from a drop-down list?

    Reply
    • Working with drop-down lists can be a little tricky, but you can often use the source cells from the drop-down list to check against. Check out the sections titled “Show Duplicates in Google Sheets With Added Criteria” as it shows how to add extra criteria to the search.

      Reply
  3. Others seem to be copying one another but you obviously know what you are writing about.
    This is such a comprehensive article I have seen on the subject of highlighting duplicates in Google Sheets, that I just had to write and say well done!

    Reply
  4. Hey, I’m facing little bit more complicated situation. I am working on too many keywords for my website. I have jackets selling website. I do keywords research and found some good keywords. But there are a lot of keywords which is repeating but not the whole keyword is repeating. A partial thing from keyword is repeating like:
    Ranboo Bomber Jacket, Ranboo Varsity Jacket and Ranboo Letterman Jacket. These are 3 keywords but not 3 products. These are 3 different names of 1 product. It is case like BBQ and Bar Bi Que, Kids and Children. I mean, the different keywords with the same meaning. So, in this case I can’t remove these duplicates. How can I make my keywords unique?

    Reply
    • You can search the whole phrase or just part of the phrase. For instance if you just want to search for duplicates with the keyword “Ranboo” you can use that and then the * wildcard like Ranboo*

      Reply
  5. Appreciate the thread and time taken to explain these solutions.
    That said, I’m staggered that ‘duplication’ is not one of the drop down options like for example in excel. It seems like a fundamental offering that should be available.

    Reply
  6. hey mate, thanks for the info,

    what would you do if you were trying to get a duplicate row of items that appear in any combination

    such as:
    row 1: a,a,a
    row 2: a,b,c
    row 3: c,b,a
    row 4: b,b,b

    in the above, I want to highlight row 2 and 3, any ideas?

    Reply

Leave a Comment

Related Posts

Disclosure: Spreadsheet Point is supported by its audience. When you purchase through links on our site, we may earn an affiliate commission.