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

Zak Islam
Computing Writer
Zak Islam was a freelance writer at Digital Trends covering the latest news in the technology world, particularly the…
Google Sheets vs. Excel: Which is better?
Spreadsheet on a laptop on a table.

If you’re trying to decide on a spreadsheet application, it’s likely that Google Sheets and Microsoft Excel have made their way onto your list of options. After all, these are two of the most popular and widely used spreadsheet tools on the market.

So, what makes one better than the other? Is it a matter of features, access, cost, collaboration, or something else? To help you decide which one is best for you, we’ll break down these categories in our Google Sheets versus Excel comparison.
The basics
It’s worth stating from the start that both Sheets and Excel are excellent spreadsheet applications. So if you have the opportunity to try them both out, you may have a clear preference from the start. But if you’re doing your research first, just know that you can’t go wrong with either.

Read more
How to freeze rows and columns in Excel
A woman on the phone sitting at a table using a laptop.

Excel spreadsheets can quickly get unruly and hard to navigate and scroll through, especially if there are a lot of rows and columns involved. But there are ways to take charge of the situation and make them easier to read.

Here's how to freeze a row in Excel in just a few clicks, so they stick with you while you scroll.

Read more
How to reset a Chromebook
A person sitting at an Asus Chromebook 15 and using it.

Everything used to work perfectly on your Chromebook. The speeds were quick. Your media played flawlessly. But now, you’re experiencing lag, and you can’t figure out what went wrong.

If you need to fix specific issues without completely wiping your Chromebook, we provide a separate guide on how to fix the most common Chromebook problems. However, if resetting appears to be your only solution, we'll show you how to get your Chromebook running like new.

Read more