Display Duplicates Using Conditional Formatting

To display duplicates using conditional formatting, use the COUNTIF function and check if the result of this function is greater than 1.


Usage Example

To search for duplicates in the 4 lists below, add conditional formatting and choose the formatting rule "Custom formula is".

Then enter the following formula (do not forget to add the $ to the cell range):

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

google sheets cf duplicates png conditional formatting

The duplicates are correctly displayed:

google sheets duplicates conditional formatting