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.
Image used with permission by copyright holder

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

The Conditional formatting tab in Google Sheets.
Image used with permission by copyright holder

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.
Image used with permission by copyright holder

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.
Image used with permission by copyright holder

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.
Image used with permission by copyright holder

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.
Image used with permission by copyright holder

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.
Image used with permission by copyright holder

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.
Image used with permission by copyright holder

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.
Image used with permission by copyright holder

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

Zak Islam
Former Digital Trends Contributor
Zak Islam was a freelance writer at Digital Trends covering the latest news in the technology world, particularly the…
How to delete a Discord server on desktop and mobile
Memrise bot in the Discord app directory.

Have you had enough of Discord for a while? We get it. It can be a little exhausting to say the least, especially if you’re running a jam-packed server, filled with multimedia and messages. Fortunately, if you’re in the mood to take a break, it’s not too hard to delete your Discord server.

Read more
How to do hanging indent on Google Docs
Google Docs in Firefox on a MacBook.

The hanging indent is a classic staple of word processing software. One such platform is Google Docs, which is completely free to start using. Google Docs is packed with all kinds of features and settings, to the point where some of its more basic capabilities are overlooked. Sure, there are plenty of interface elements you may never use, but something as useful as the hanging indent option should receive some kind of limelight.

Read more
How to get Android apps on a Chromebook
Dell Chromebook 3189 2-in-1 on a classroom desk floating in the air.

Over the last few years, Android apps have been added to more and more Chromebook models. A brilliant expansion of the overall user experience, Google went ahead and integrated the Play Store into most Chromebooks made after 2019. This is the most convenient way to download an Android app or two, but if you own an older Chromebook, the machine may not have native support for downloading and installing applets.

To confirm this, we recommend referencing this extensive Android app support list from The Chromium Projects.

Read more