Skip to main content

How to highlight duplicates in Google Sheets

If you’ve added the same data more than once by mistake on your spreadsheet or you’re simply looking to find multiple cells that contain the same word, number, or formula, Google Sheets has a simple way to highlight them.

Let’s take a look at how to highlight duplicates in Google Sheets.




5 minutes

What You Need

  • A computer, laptop, tablet, or phone with access to Google Sheets

Using the Conditional formatting tool

Step 1: Click the cells that you want to include within the search for duplicates. You can choose a column, specific cells, and rows. For demonstration purposes, I have highlighted the A column.

Selecting a column in Google Sheets in order to use it to highlight duplicate data.

Step 2: Click the Format tab and then select Conditional formatting.

The Conditional formatting tab in Google Sheets.

Step 3: Within the Conditional format rules section, the Apply to range field will already be filled via the column you’ve selected. In my case, that’s column A. You can change it yourself as well via this field manually.

The Apply to range box in Google Sheets for highlighting duplicate data.

Step 4: Click the Format cells if menu and select Custom formula.

The Format cells if drop-down menu in Google Sheets and the Custom formula is option within that menu.

Step 5: Within the Value or formula box, enter =countif(A:A,A1)>1. If you’ve not selected the column, but have instead selected the cells starting from, say, either A1 or A2, then the aforementioned formula should still be used.

Entering a custom formula that will highlight duplicate data in Google Sheets’ Custom formula is box.

Step 6: Click Done. The duplicates will be highlighted in the A column.

Google Sheets also provides further customization options for highlighting duplicates such as making them bold or changing their color.

The Formatting style section for customizing the appearance of how highlighted duplicate data appears.

Step 7: If you don’t want to see the duplicates highlighted anymore, click the Remove rule button via the trash icon.

The trash icon button for removing highlighted duplicate data in Google Sheets.

Deleting duplicates in Google Sheets

If you find any duplicates within your spreadsheet, it may be a part of your data and thus doesn’t need to be deleted. But in case you do need to remove it, Google allows you to perform this function with the Remove duplicates tool.

Step 1: Click the column where it contains duplicates. I’ve clicked column A.

Step 2: Click the Data tab > Data cleanup > Remove duplicates.

The Remove duplicates tool in Google Sheets.

Step 3: Select the Remove duplicates button. If you’ve applied the highlight duplicates formula for other columns such as column B and want to remove them as well, then click the Expand to A:B option and then select the Remove duplicates button.

The Remove duplicates button in Google Sheets.

If you're a new Google Sheets user, we have more helpful tips and tricks in our guides on how to search and how to merge cells in Google Sheets.

Editors' Recommendations