How To Highlight Duplicates in Google Sheets

To highlight duplicates in Google Sheets, use conditional formatting and the COUNTIF function. My guide below covers this in detail. Specifically, I’ll show how to use conditional formatting to highlight duplicates, limiting your target to a specific column, and expanding your highlighting to include multiple columns.

How To Highlight Duplicates in Google Sheets

The most common way to highlight duplicates in Google Sheets is with conditional formatting. It’s really easy to do that in a single column, so I’ll cover that first. All you have to do is use a COUNTIF function to see whether a cell is duplicated (Note that you can also use the UNIQUE function for similar results).

Then, apply conditional formatting based on the results. Here’s an animation that shows the process.

Use the COUNTIF function and the Array Formula to highlight a duplicated lines in Google Sheets

As you can see in my animation, I start with the “format” dropdown menu. Then I use a COUNTIF formula and conditional formatting to highlight duplicates.

Note that I also have a guide on how to remove duplicates. It goes into detail about the process. You don’t need a remove duplicates add-on. My comprehensive guide shows the formulas for highlighting and removing cells containing duplicate data from  your sheet. That’s helpful for data cleanup, and I regularly use it to show only unique values.

How To Highlight Duplicates Using a Single Column

Because you’ll often need to highlight duplicates in a single column, we’ll start there. Next, I’ll share more complex methods. That includes how to find multiple cells with the same value in multiple columns. Here are the steps to highlight duplicate data in a column:

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

You can omit the header row by not selecting it when you choose your selected range for the data set. Just highlight everything below those, and you’ll only highlight the relevant data in your set. Once they’re highlighted, it’s easy to remove duplicates in Google Sheets.

Video Guide

I also made a video that shows exactly how to do it. You can watch it below or check it out on YouTube.

My video is about 10 minutes long and walks through everything you’ll need to know about highlighting multiple instances of any data in your spreadsheet. It covers everything in my guide here, but I’m also including a written guide with screenshots below.

Visual Walk-Through

If you prefer to see step-by-step instructions, I also took screenshots. Here’s how to highlight duplicates with my method 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

And here’s how it looks when you’re finished. Note that all the duplicates are now highlighted.

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.

How Does The Highlight Duplicates Formula 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.

If you want a more detailed explanation, I made a pretty popular YouTube video that covers this in more detail. It uses my same method (with the COUNTIF and conditional formatting) but breaks everything down even more.

Note: I 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). Here’s how it looks once you’ve set up the formula.

Dataset where duplicates are highlighted in multiple columns

 

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

So we’ve talked about how to highlight duplicate data in Google Sheets, but we’ve really only covered how to find duplicate cells. What if you want to find whole rows of duplicated data? There’s a solution for that too. Here’s what to do when you want to highlight rows with duplicated data. The key here is array formulas.

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” from the top menu
  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 ARRAYFORMULA Work with the COUNTIF Formula?

You know I like to break down the syntax of my formulas. That’s how I figured out how to make everything 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. Let me explain in more detail.

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

There’s also a use for concatenation in Google Sheets. I’ve added this to my repertoire for building formulas where I want to highlight duplicates of data based on multiple cells.

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

On a related note, I made this guide on building dropdown menus in your spreadsheet.

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 in your column range.

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
  • There’s a formula-free way to do it, but you need to install an add-on
  • It’s likely that Google Sheets will roll out an automatic conditional formatting option as Gemini becomes more powerful

Frequently Asked Questions

In order to keep all of the most common questions in one place, I’m listing some below. As always, you can leave a comment if you have anything that’s not covered here. I curated some of these from the comments here and from my YouTube channel.

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 for a more in-depth look.

How Do I Group Duplicates in Google Sheets?

There’s an easy way to group duplicates in Google Sheets. It’s actually a function all its own. 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. Note that Excel has a similar method for removing blank cells.

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

You can’t search for duplicates in a single cell, but you can quickly find duplicated data across an entire sheet. That’s what I’ve covered with the COUNTIF, UNIQUE, and Conditional Formatting tools.

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!

Wrapping Up

I hope that I’ve helped you learn how to highlight duplicates in Google Sheets. After all, Spreadsheet Point provides plenty of other Google Sheets tutorials. My goal is to help you quickly find the solution you’re looking for.

Most Popular Posts

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

  1. I’m using this formula: =COUNTIF(Arrayformula($A$2:$A$400&$B$2:$B$400&$C$2:$C$400&$D$2:$D$400), $A2&$B2&$C2&$D2) > 1

    And it’s highlighting rows that are above the duplicated row. Any idea why?

    Reply
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!