How to Highlight Duplicates in Google Sheets

When working with spreadsheets, in Google Sheets or Microsoft Excel, you often need to deal with duplicates. Duplicates are common in the dataset and it is one of the important steps when you prepare and clean your data before the analysis. Please see below how to highlight the duplicates in Google Sheets.

Step 1: Select the cells or the data range that you want to highlight;

Step 2: Click the "Format" tab from the ribbon;

Step 3: Click the "Conditional formatting" from the drop-down list;

Step 4: On the right of your spreadsheet, you will see the "Conditional format rule" panel. In the "Format rules" box, select "Custom formula is" from the bottom and copy the formula "=COUNTIF($A$1:$G$10, A1)>1" in the next box;

Step 5: If you want a different color from the default color, you can change the color in the "Formatting style" section, e.g., "green";

Step 6: After you click the "Done" button, the cells with duplicates will be in green;

Leave a Reply