How To Highlight Duplicate Cells In Google Sheets

In this tutorial, you will learn how to highlight duplicate cells in Google Sheets.

How To Highlight Duplicate Cells In Google Sheets

Highlighting duplicate cells can be a great way to quickly identify and fix errors in your data. For example, you may have a list of names, and you want to make sure that no two names in the list are the same.

One way to highlight duplicate cells is by using the conditional formatting feature. This feature allows you to set up rules that will color a cell a certain way if the values meet specific criteria.

In this guide, we will show you how to use conditional formatting to highlight any duplicate cells in Google Sheets.

How to Highlight Duplicating Values in Google Sheets

Here’s our step-by-step guide on how to highlight duplicating values in Google Sheets.

Step 1

First, open the document where you want to highlight duplicate cells.

Select the specific range where you want to highlight duplicates. In this example, I want to highlight duplicate IDs in column A.

Step 2

While the range is still selected, click on Format > Conditional formatting.

Step 3

Under the Format cells if… section, select the Custom formula is option in the dropdown menu.

We’ll use the COUNTIF function to highlight all cells that appear more than once in any of the prior cells above it. In this example, we’ll use the formula =COUNTIF($A$2:A2, A2)>1.

Step 4

All duplicate cells should now be highlighted using the custom conditional formatting rule.

Summary

This guide should be everything you need to learn how to highlight duplicate cells in Google Sheets.

Click this link to make a copy of this sample document to try doing it yourself!