CountIF Google Sheets Duplicates: How to Highlight Them

Want to highlight duplicate values in your Google Sheets?

While Google Sheets is a great tool, there is no built in feature to do this.

But don’t worry, you can still do this quite easily

How to Highlight Duplicates in Google Sheets

Want to highlight duplicates in a specific column? Just follow these steps:

1 - Select the column

2 - Go to 'Format' and choose 'Conditional Formatting'

3 - Pick 'Custom formula is' and enter:

=COUNTIF(A:A, A1)>1

That’s it!

Color-Coding Your Duplicates

Sure, you've learned how to highlight duplicates in Google Sheets using CountIF.

Bravo! 👏

But what if I told you that you could take it to the next level?

Imagine being able to showcase different types of duplicates.

Let’s go through it.

Why Color-Code?

Color-coding isn't just about aesthetics - it allows for rapid data analysis.

Imagine you’re managing an inventory. Yellow could show items with duplicates that need reordering, while red could flag items that are overstocked.

With just a glance on your data, you gain actionable insights.

How to Set Up Color Codes in Google Sheets

In Google Sheets, you can add multiple conditional formatting rules for the same range of cells.

The first rule applied will be the first color shown.

So, to color-code, you'd set up multiple instances of CountIF formulas, each with its unique criterion and corresponding color.

Repeat the CountIF Steps from above.

Then:

1 - Click on the "add another rule" option

Find it at the bottom of the conditional format rules box.

Click it.

Voila, you're now setting up a new color.

2 - Modify the CountIF formula by changing the condition to match your new criteria

For example, to highlight items appearing more than twice, your formula would be:

=COUNTIF(A:A, A1)>2

3 - Now, choose your new color

Any cell meeting this new condition will glow in this new color.

To add more colors, simply keep adding rules following the steps above.

Fine-Tuning Your Color Codes

If you add more than one rule, Google Sheets will prioritize the first rule on the list.

But don’t worry, you can easily drag the rules up and down to change the order.

This ensures that the most critical duplicates catch your eye first.

Example of Highlighting Duplicates in Google Sheets

Let’s use an example to show each step clearly:

The Scenario

You are preparing for a webinar and have a list of email addresses. You want to avoid spamming the same email addresses with multiple invites.

Let’s use CountIF to highlight the duplicates in Google Sheets.

Follow along the example with this spreadsheet (example 1).

How do I Find Duplicates in Google Sheets Using CountIF?

Step 1: Select Your Column of Interest

Navigate to your Google Sheet and click the label "A" at the top of your email column, thereby highlighting it.

Step 2: Enter 'Conditional Formatting'

Hover over to 'Format' in the toolbar, click it, and from the dropdown menu, select 'Conditional Formatting'.

Step 3: Activate the Formula

Select 'Custom formula is' and then type in the formula

=COUNTIF(A:A, A1)>1

Identifying Duplicates Across Multiple Columns

Now that you're fluent in pinpointing duplicates within a single column, why stop there? The power of the CountIF formula in Google Sheets extends beyond one column.

Follow along the example with this spreadsheet (example 2).

The Scenario

Suppose you're running a movie club in addition to your webinar duties.

You have a list of movie titles and their respective directors, but you want to make sure that you don't feature the same title or director twice.

Let's crack this.

How Do You Use Conditional Format to Find duplicates in Two Columns in Google Sheets?

Step 1: Highlight the Desired Columns

Select Columns A and B by clicking and dragging your mouse from the "A" label to the "B" label.

Step 2: Enter 'Conditional Formatting'

Click on 'Format' on the top toolbar and then 'Conditional Formatting' from the dropdown.

Step 3: Choose Your Formula Option

Use the formula:

=COUNTIF(A:A, A1)>1 

This locks the specific columns and rows where duplicates may reside.

Hit 'Done' and you are done!

Your duplicates across multiple columns are highlighted.