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
The duplicates are correctly displayed: