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.

    Select Range
    Select the range you wish to highlight the duplicate values.
  2. From the Format menu, choose Conditional Formatting. A dialog box, “Conditional format rules”, will pop up on the right-hand side.

    Conditional Formatting
    Click on the Conditional Formatting option from the Format menu.
  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.

    Custom formula is
    Click on the “Custom formula is” option.
  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.

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

    Output
    Duplicate values highlighted in the column.

Was this article helpful?

Leave a Comment