Duplicate data is the bane of spreadsheet solutions, especially at scale. Given the amount and variety of data teams are now entering, duplicate data in tools like Google Sheets can be relevant and necessary, or can be frustratingly distracting from the main purpose of spreadsheet work.

The underlying question raises a good question: How do I highlight duplicates in Google Sheets?

We’ve walked you through how to highlight duplicates in Google Sheets, with images to make sure you’re on the right track with deduplication.

Highlight duplicate data in Google Sheets

Google Sheets is a free, cloud-based alternative to proprietary spreadsheet programs, and — unsurprisingly, since we’re dealing with Google — offers many powerful features to help simplify data entry, formatting, and calculations.

Blank Google Sheets for highlighting duplicate data

Google Sheets has all the familiar features: file, editing, viewing, formatting, data, tools, and more, making it easy and fast to enter data, add calculations, and discover key relationships. What Sheets doesn’t, however, is an easy way to find and highlight duplicates.

While other spreadsheet tools, such as Excel, have built-in conditional formatting tools that can pinpoint duplicate data in a worksheet, Google’s solution requires more manual work.

Step by Step: How to Highlight Duplicates in Google Sheets (with Pictures)

So how do I automatically highlight duplicates in Google Sheets? While there are no built-in tools for this purpose, you can take advantage of some built-in functions to highlight duplicate data.

Here is a step by step guide:

Step 1: Open the spreadsheet.

Step 2: Highlight the data to check.

Step 3: Under Format, select Conditional Formatting.

Step 4: Select “Custom Formula Yes”.

Step 5: Enter a custom duplicate check formula.

Step 6: Click “Finish” to view the results.

Step 1: Open the spreadsheet.

First, head to Google Sheets and open the spreadsheet you want to check for duplicates.

How to Highlight Duplicate Data in Google Sheets: Open Spreadsheet

Step 2: Highlight the data to check.

Next, left-click and drag the cursor over the data you want to examine to highlight it.

How to Highlight Duplicate Data in Google Sheets: Select Data

Step 3: Under Format, select Conditional Formatting.

Now, head to Format in the top menu row and choose Conditional Formatting. You may get a “Cell is not empty” notification – if so, click it and you should see:

How to Highlight Duplicate Data in Google Sheets: Turn on Conditional Formatting Options

Step 4: Select “Custom Formula Yes”.

Next, we need to create a custom formula. Under Format Cells If, select the drop-down menu and scroll down to Custom Formula Is.

How to highlight duplicate data in google sheets: select custom formula is

Step 5: Enter a custom duplicate check formula.

To search for duplicate data, we need to enter a custom duplicate check formula, which for our data column looks like this:

=COUNTIF(A:A,A1)>1

This formula searches for any text string that occurs multiple times in our selected dataset, highlighted in green by default. If you like a different color, click the little paint can icon in the format bar and choose the color you like.

How to Highlight Duplicate Data in Google Sheets: Insert Formula

Step 6: Click “Finish” to view the results.

Voila – we’ve highlighted duplicate data in Google Sheets.

How to highlight duplicate data in google sheets: results

How to highlight duplicates in multiple rows and columns

You can also highlight duplicate data in multiple columns or rows if you’re examining larger datasets.

This starts the same way as the duplicate checking process above – the only difference is that you change the data range to include all the cells you want to compare.

In practice, this means entering an extended range of data in the Conditional Formatting Rules menu and the Custom Formatting box. Let’s take the example above as a starting point, but instead of just searching for duplicates in column A, we’ll search three columns: A, B, and C, and rows 1-10.

How to highlight duplicate data in google sheets: data with multiple rows

When we enter the conditional formatting rule, Apply to Range becomes A1:C10 and our custom formula becomes:

=COUNTIF($A$2:G,indirect(address(row(),column(),)))>1

This will highlight all duplicates in all three columns and all 10 rows, making it easy to spot the data doubles:

How to highlight duplicate data in google sheets in multiple rows and columns: results

handle duplicates repeat Google Sheets

Can you highlight duplicates in Google Sheets? Absolutely. While this process requires more effort than some other spreadsheet solutions, it’s easy to replicate once you’ve done it once or twice, and once you’re comfortable with the process, you can scale up to find things that span rows, columns, and even more A larger dataset of duplicates.

Business Google Form Template