How To Highlight Duplicate Rows in Google Sheets

Highlighting duplicate rows in Google Sheets can be really useful to draw attention to duplicate data quickly. The process is straightforward using conditional formatting, but to understand what’s happening, we’ll look at the logic first so you can see how this works.

Trust me, it will make more sense if you understand the formula so that when you apply it to the conditional formatting, you’ll know exactly what to expect; otherwise, you may not be able to apply this successfully in your particular scenario.

How does the formula work?

In the example above, we’re using the COUNTIF and an ARRAY FORMULA function to check if a combination of values in two columns ($A$2:$A$11 and $B$2:$B$11) appears more than once in those columns. Let's break down how this formula works step by step:

1. Array Formula

ARRAYFORMULA($A$2:$A$11&$B$2:$B$11) 

This part of the formula combines the values in columns A and B from rows 2 to 11 into a single array. It essentially concatenates the values in column A with the corresponding values in column B for each row. This results in an array of concatenated values which look like this: EmilyJohnson

AlexanderLee

OliviaPatel

WilliamChen

SophiaRodriguez

BenjaminKim

AvaSingh

EthanPark

OliviaPatel

JamesGarcia

2. Concatenate Values

$A2&$B2

This part concatenates the values in the current row of columns A and B. The `$` sign before the column letters locks the reference to column A ($A) and column B ($B) while allowing the row reference to change as the formula is applied to different rows.

3. COUNTIF Function

`COUNTIF(...)`: This function counts the number of times a specific condition is met within a range.

4. Combining the COUNTIF and the ARRAY FORMULA

COUNTIF(ARRAYFORMULA($A$2:$A$11&$B$2:$B$11),$A2&$B2) 

This part of the formula counts the number of times the concatenated value of the current row in columns A and B (i.e., `$A2&$B2`) appears in the entire array of concatenated values from columns A and B.

5. COUNTIF Condition

COUNTIF(...)>1

The formula checks if the count calculated in step 4 is greater than 1. If it is, it returns `TRUE`, indicating that the combination of values in the current row appears more than once in the entire range. Otherwise, it returns `FALSE`.

You can see in the image below that there are two rows that show a value of TRUE, indicating that the count formula we just applied found more than 1 instance in the whole table.

You wouldn’t normally use a formula like this within a cell in this context; however, this is typically used in a conditional formatting rule or as part of a data validation rule in Google Sheets.

It checks if the combination of values in the current row of columns A and B appears more than once anywhere in the entire range of rows 2 to 11. If it does, it returns `TRUE`, indicating a duplicate combination; otherwise, it returns `FALSE`. This can be useful for identifying duplicate pairs of values in a dataset.

With that said, let’s apply this formula within a conditional formatting rule to highlight the duplicate rows in the table.

Conditional Formatting - Highlighting Duplicate Rows

Select the dataset excluding the header row and then click the Format menu and choose Conditional formatting.

This will bring up a sidebar where we can adjust the conditional format rules. You’ll notice the range at the top where this will be applied (you can adjust this if required), and below that, select the dropdown for Format rules and choose ‘Custom formula is. You’ll find this right at the bottom of the list.

Now you will see a text field appear where we can type in the formula, the one we just tested out in the sheet a moment ago:

=COUNTIF(ARRAYFORMULA($A$2:$A$11&$B$2:$B$11),$A2&$B2)>1

Go ahead and paste this into the field and watch the magic as your rows are now highlighted green. Don't forget to include the equals sign.

The rows are green because this is the default setting in the conditional formatting rules, but you can adjust this to suit your fancy by playing with the formatting style in the sidebar.

If your table of data is more than two columns wide, don’t panic; you will just need to adjust the formula to accommodate these extra columns.

This formula below now has three columns:

=COUNTIF(ARRAYFORMULA($A$2:$A$11&$B$2:$B$11&$C$2:$C$11),$A2&$B2&$C2)>1

All we did was add the entire column to the array part with the joining ampersand (&):

&$C$2:$C$11

Plus, you also need to add the concatenated part like this:

&$C2

Those are the only changes required for one extra column, but if you have more columns, just repeat the process.

Happy Highlighting!