Sep 27, 20232 min read

How to Highlight Duplicates in Google Sheets Using Conditional Formatting

Duplications, intentional or not, can clutter your data.

Ever wondered how you can easily highlight these duplicates in Google Sheets without manually having to click through all the cells?

Good news: I will show you how to highlight duplicates in Google Sheets using conditional formatting.

We always keep our guides short and efficient, so let’s get started.

Guide: How to Highlight Duplicates in Google Sheets Using Conditional Formatting

Follow these five simple steps to learn how to highlight duplicates in Google Sheets using conditional formatting. Duplicate free our spreadsheet example to follow along.

1. Selection of Data

Start by opening your Google Sheet and selecting the column where you suspect there to be duplicates. For our example, let's consider column A, labeled 'Director'.

Data set with duplicates where you want to highlight the duplicates using conditional formatting in Google Sheets

2. Navigating the Formatting Menu

Go to the top of your Google Sheets interface. Here, you'll find the 'Format' option. Click on it, and a dropdown will appear. From this dropdown, choose 'Conditional formatting'. This action will open a sidebar on the right side of your screen dedicated to setting up your conditional formatting rules.

Navigating the Formatting Menu in Google Sheets to highlight duplicates

3. Selecting the Custom Formula Option

Within the sidebar, you'll notice a prompt labeled “Format cells if…” Click on this dropdown and scroll until you find the 'Custom formula is' option. Here's where the magic happens.

Selecting the Custom Formula Option to highlight duplicates in Google Sheets

4. Application of the Formula for Single Column Duplicates

In the formula box, type in =COUNTIF(A:A, A1)>1. Breaking it down:

COUNTIF(A:A, A1) means you're asking Google Sheets to count the occurrences of the value in cell A1 within column A
>1 instructs Google Sheets to highlight any value that appears more than once

Application of the Formula for Single Column Duplicates

Copy paste the formula here:

=COUNTIF(A:A, A1)>1

If you're focusing on the 'Director' column and let’s say it’s column C, you'd adjust your formula to =COUNTIF(C:C, C1)>1. As soon as you input this formula, Google Sheets will instantly highlight the duplicates for you.

5. Finalizing the Formatting

Finally, choose a highlight color that makes the duplicates stand out. Once satisfied, click 'Done'. Voila! Your duplicates will now be prominently displayed, ensuring you can easily spot and manage them.

In the world of data, duplicates can be nuisances. But with these straightforward steps, you can keep your Google Sheets clean, organized, and efficient.

Finalizing the Formatting of the duplicates in Google Sheets

Ready for the next data challenge? With Google Sheets on your side, you're more than equipped.

Eager to dive deeper into Google Sheets? Explore our comprehensive courses or subscribe to our newsletter for regular insights.