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.
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.
Step 2: Click the Format tab and then select Conditional formatting.
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.
Step 4: Click the Format cells if menu and select Custom formula.
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.
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.
Step 7: If you don’t want to see the duplicates highlighted anymore, click the Remove rule button via the trash icon.
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.
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.
If you're a new Google Sheets user, we have more helpful tips and tricks in our guides. Learn how to search in Google Sheets so you don't misplace any data. For data management, learn and how to merge cells in Google Sheets.
