How to Highlight Duplicates in Google Sheets

If you’re looking to Highlight Duplicates in Google Sheets, you have landed at the right place.

First, here’s a quick video featuring both methods:

Now, here’s a detailed explanation of the Google Sheets formula to Highlight Duplicates:

#1 – By using COUNTIF function

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

The COUNTIF formula counts the number of times a value appears in a range. In this case, it counts the number of times the value in cell A2 appears in the range $A$2:$A.
The dollar signs in $A$2:$A make the range absolute, which means it won’t change when the formula is applied to other cells.
The formula checks whether the count of the value in cell A2 is greater than 1. If it is, the cell is formatted with the chosen style.

=MATCH(A2, $A$2:$A$100, 0)<row()-1

COUNTIFS formula counts the number of times multiple criteria are met. In this case, it counts the number of times the value in cell A2 appears in the range $A$2:$A and the value in cell B2 appears in the range $B$2:$B.
The dollar signs in $A$2:$A and $B$2:$B make the ranges absolute, which means they won’t change when the formula is applied to other cells.
The formula checks whether the count of the values in cells A2 and B2 is greater than 1. If it is, the cell is formatted with the chosen style.

That’s it.

Other cool Google Sheets formulas:

And if you get stuck somewhere while applying the GSheets formula to Highlight Duplicates, kindly let me know in the comments below.