How to Highlight Duplicates in Google Sheets

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.
    =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


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

    Use the Countif function.

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

    Duplicate values highlighted in the column.

Phoebe is an HR assistant in her day job, but is also a web blogger that loves writing guides and sharing her experiences. When she is not out with her friends, she enjoys traveling or binge-watching Netflix. She graduated with a BA in Communication and Media Studies from the University of Utah, and is a chief editor at TechObservatory.

Leave a Comment