How to Highlight Duplicates in Google Sheets 3+ Simple Ways

I know how to highlight duplicates in Google Sheets. This process includes evaluating, correlating, and showing where duplicates are found. Google Sheets has some simple solutions built in to help you save time by highlighting duplicates. Here’s a step-by-step guide. Here are some ways to highlight duplicates in Google Sheets.

Watch Video – Highlight Duplicates in Google Sheets

How to Highlight Duplicates in Google Sheets

To highlight duplicate data in columns:

1. Select your dataset
2. Go to Format > Conditional Formatting.
3. Click the ‘Format Cells’ dropdown and click ‘If custom formula is‘
4. Enter the following formula:

=COUNTIF(search range,cell reference)>1

1. Specify the formatting in “Formatting style” and click “Finish.

How to Highlight Duplicates in a Single Column in Google Sheets with Examples

The most common situation where you’ll need Google Sheets to highlight duplicates is when you have a dataset in columns and want to highlight duplicates immediately.

For example, suppose you have a dataset like the one below and you want to highlight all repeated names in column A.

Below are the steps to highlight duplicates in a column:

1. Select name dataset (excluding headers)
2. Click on the “Format” option in the menu
3. In the options that appear, click Conditional Formatting. This will open the Conditional Formatting Rules pane on the right.
   

   

4. Click the “Add another rule” option.

1. Make sure the range (where duplicates should be highlighted) is correct. If not, you can change it from the “applies to ranges” section
   

   

2. Click the “Format cells when” dropdown and click the “When custom formula is” option
   

   

3. In the field below, enter the following formula:
   =COUNTIF($A$2:$A$10,A2)>1

   

4. Specify the formatting for highlighting duplicate cells from the Formatting Style options. Green is used by default, but you can specify other colors and styles like bold, italic, etc
   

   

5. Click “Finish
   

   

The above procedure will highlight all cells with duplicate names with the specified color.

One of the great things about conditional formatting is that it’s dynamic. This means that the formatting will automatically update if you change the data in the cell. For example, if you remove one of the duplicate names, the name will no longer be highlighted (in other cells) because it will be unique.

Read more: How to copy conditional formatting in Google Sheets

How does this work?

When you use a custom formula with conditional formatting, each cell is checked using the specified formula to find duplicates in Google Sheets. You can also remove duplicates to keep your data clean.

If the formula returns TRUE for the cell, the cell is highlighted with the specified format; if it returns FALSE, the cell is not highlighted.

In the example above, each cell is checked and if the name occurs more than once in the range, the COUNTIF formula returns TRUE and the cell is highlighted. Otherwise, it will not be changed.

Also note the use of the range $A$2:$A$10 (the dollar sign precedes the column alphabet and row number). This is very important because when the formula moves to the next cell (row below) it makes sure that the entire range where the number of names is checked remains the same.

If you want to remove the highlighted cells, you need to remove the conditional formatting. To do this, select the cell that has the formatting applied, click the Format option, click Conditional Formatting, and remove the rule from the pane that appears on the right.

Find Duplicates in Google Sheets by Highlighting Them in Multiple Columns

In the example above, all the names were in one column.

But what if the names are in multiple columns to perform conditional formatting on duplicates in Google Sheets (see below).

You can also use conditional formatting to highlight duplicate names (names that occur multiple times in all three columns combined.

To highlight duplicate data in multiple columns:

1. Select name dataset (excluding headers)
2. Click on the “Format” option in the menu

1. In the options that appear, click Conditional Formatting.
2. Click on the “Add another rule” option
   

   

3. Make sure the range (where duplicates should be highlighted) is correct. If not, you can change it from the “applies to ranges” section
4. Click the “Format cells when” dropdown and click the “When custom formula is” option
   

   

5. In the field below, enter the following formula:
   =COUNTIF($A$2:$C$10,A2)>1

   

6. Specify the formatting for highlighting duplicate cells from the Formatting Style options. Green is used by default, but you can specify other colors and styles like bold, italic, etc
   

   

7. Click “Finish

The above procedure will highlight a cell if a name appears multiple times in all three selected columns.

How does this work?

This also worked similarly to the previous conditional formatting of duplicates in Google Sheets.

In the COUNTIF formula we covered all the cells in the three columns. Therefore, each cell in the range is checked using the specified formula and returns TRUE or FALSE. The COUNTIF formula is like a duplicate formula in Google Sheets.

If there are repeated names in any of the columns, they will be highlighted using the specified format.

Again, note the use of the $A$2:$C$10 range (the dollar sign before the column letter and row number). This is very important as it ensures that the range remains the same while conditional formatting checks the number of names in the cell.

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

This is a little difficult.

Suppose you have a dataset as shown below and you want to find all duplicates through highlighting in Google Sheets.

In this case, records with exactly the same value in each cell within a row (such as rows 2, 4, 7, and 8 in the example above) would be duplicates).

The reason this is a little harder is that you don’t have to check individual cells. It should check the entire row and highlight only those rows where all cells are repeated.

But don’t worry. It’s not that difficult.

To use conditional formatting to highlight duplicate rows:

1. Select a dataset (excluding headers)
2. Click on the “Format” option in the menu
3. In the options that appear, click Conditional Formatting.
4. Click on the “Add another rule” option
5. Click the “Format cells when” dropdown and click the “When 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. Specify the formatting for highlighting duplicate cells from the Formatting Style options. Green is used by default, but you can specify other colors and styles like bold, italic, etc
   

   

8. Click “Finish

The above procedure highlights all repeated records in the dataset (similar to the original example).

How does this work?

This works the same as the first example (simply highlighted the cells in the column with duplicates).

However, I have a whole line that I need to compare with every other line, so I combined the contents of all the lines and created one string per line.

The next part of the formula creates an array of strings that is the concatenation of the contents of all the cells in the row (concatenation using the ampersand symbol).

=ARRAYFORMULA($A$2:$A$10&$B$2:$B$10&$C$2:$C$10)

This array is used in the COUNTIF expression and the condition used is also a concatenated string containing all values ​​in a row. This is done using the following criteria:

$A2&$B2&$C2

This translates into a simple column type structure and the COUNTIF function checks how many times this combined string is repeated in the array of strings it creates.

This will highlight all duplicate records.

Show Duplicates in Google Sheets With Added Criteria

Google Sheets also lets you highlight duplicates using almost any additional condition you can think of. For example, you can set the system to only highlight duplicates of certain values.

The syntax must use the ‘*’ (and) operator so that COUNTIF uses both criteria. The syntax looks like this:

=(COUNTIF(range,criteria)>1) * (new condition))

You can use previous employee data to show how this works. Let’s say you’ve actually found out that you have two Henrys of hers in the marketing department, so you’ve updated her ID number for one of them.

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

1. Work with conditional formatting rules, like in the example above
2. Enter the familiar range and format =(COUNTIF(Range,Criteria)>1) for the first part of the formula. So an example would be:

=(COUNTIF($A$2:$C$10,$A2)>1)

This will tell you if there are duplicates in the A row, but will not filter out the second Henry’s mismatched ID, so you’ll need the following extra steps

1. Use the “*” (and) command after the first formula
2. Add a second condition to the syntax (COUNTIF(Range,Criteria)>1) but make sure you address the other row (row C in this case). The full expression looks like this:

=(COUNTIF($A$2:$C$10,$A2)>1)*(COUNTIF($A$2:$C$10,$C2)>1)

Of course, in this exact example, we could have just used the second half of the expression to get the same result, but that’s just one way to add a second condition. Depending on your needs, you can also:

• Use another value after the criterion (>0 or <5 etc
• Add a third criterion
• Many other “*” conditions

Editing or Deleting Conditional Formatting Rules

If highlighting finds all duplicates, you may want to keep some duplicates, but not highlight any more. The solution is simple, just edit or delete the conditional formatting rule. To do so:

1. Highlight cells with conditional formatting rules applied
2. Go to Format > Conditional Formatting
3. Click the trash can icon next to an existing rule

Potential Problems with Simple Solutions when Highlighting Duplicate Cells in Google Sheets

In some cases, even after following all the steps above and using the same formula, Google Sheets does not highlight duplicates. If not highlighted, how would I remove the duplicates?

Let’s check some possible reasons:

Extra Spaces in the Cells

Do you have extra spaces (leading or trailing space characters) in the text in one cell and not in the other?

Because we are looking for an exact match of two or more cells that should be considered duplicates, any extra space character in a cell will cause a mismatch.

So even if you know there are duplicates, they may not be highlighted.

To get rid of this, you can use the TRIM function (and CLEAN function) to remove all extra space characters.

Incorrect reference

There are three types of references in Google Sheets.