How to Highlight Duplicates in Google Sheets

Whenever we work with data in Google Sheets, you might come across the issue of duplicate data sooner or later. Duplicate data could be in the same column or rows in a dataset. Follow this guide to highlight duplicates in Google Sheets:

1. Select the column and rows in which you want to highlight duplicate values. I am selecting the range A1 to A11.

   

2. From the Format menu, choose Conditional Formatting. A dialog box, “Conditional format rules”, will pop up on the right-hand side.

   

3. If you’ve selected the column beforehand, “Apply to range” will be filled in for you. If not, type in your range. Under the Format cells if drop-down, select the Custom formula is option. Make sure to start the formula with the “=” sign.

   

4. We’re going to use the COUNTIF function to find the duplicate values.
   Syntax:

   =COUNTIF(range, criterion)

   Here, range is the range that is logically tested against the criterion. The criterion argument means the condition to apply to the range.

   In this example, we will type the following formula inside the custom formula text box

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

   Here, “>1” means is used to check if the criterion appeared more than once within the range.

   

5. Click on the Done button. This will be the output: