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

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

To highlight duplicates in Google Sheets using a single column:

1. Select the names dataset (excluding the headers)
2. Click on “Format” > “Conditional formatting”
   
3. 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” 
    

    

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“

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

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.

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

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

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

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

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:

• 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