Skip to content

How to Highlight Duplicate Values in Google Sheets

In this article, I will show you how to highlight duplicate values in Google Sheets using conditional formatting.

Step 1

Start by selecting all the data you want to search for duplicate values.

Then go to Format Conditional Formatting.

Step 2

In the panel on the right, select Custom formula is.

Then enter the following formula.

=COUNTIF($B$5:$D$13, B5)>1

Make sure the first argument references the selected range. And, also make sure that reference is an absolute reference with dollar signs.

=COUNTIF($B$5:$D$13, B5)>1

The second argument should reference the top-left cell of the selected range.

Formula Explanation

This formula checks if any value in the selected range appears more than once. If a value appears more than once, then the formula evaluates to TRUE, and Google Sheets applies the conditional formatting.

Step 3

Select the fill color you want to apply. Then click on Done.

All duplicate values are now highlighted.

This Post Has 2 Comments

  1. I love learning more about conditional formatting! It’s been a journey for me. If I wanted to highlight unique values between two columns of data, would this same formula work if I change the last argument to “=1”?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top