How To Highlight Duplicate 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.

Table of Contents

1. How To Highlight Duplicates in Google Sheets

2. How To Highlight Duplicates in Multiple Columns

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

4. How To Show Duplicates in Google Sheets With Added Criteria

5. How To Edit Conditional Formatting Rules

6. Potential Problems When Highlighting Duplicate Cells in Google Sheets

7. Some Tips When Highlighting Duplicates in Google Sheets

8. Frequently Asked Questions

9. Wrapping Up

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.

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 in Google Sheets 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

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

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.

To highlight duplicates in Google Sheets using a single column:

1. Select the names dataset (excluding the headers)

2. Click on “Format” > “Conditional formatting”
   
   

4. Select the “Add another rule” option

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

• Click on “Format cells if” > “Custom formula is”
  

• In the field below, enter the following formula:
  =COUNTIF($A$2:$A$10,A2)>1
  
  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”
    
    

And here’s how it looks when you’re finished. Note that all the duplicates are now 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 Duplicate 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.

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“

3. Choose the “Add another rule” option
   

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

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($A$2:$C$10,A2)>1
   

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

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

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.

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

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

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.

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

3. Use the asterisk symbol (*) and the and operator after the first formula

4. 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)

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

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

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. 

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.