Skip to content

Highlight Duplicates in Google Sheets

The question is: how do you highlight duplicates in Google Sheets?

In this article, I will show you how to highlight duplicate values in Google Sheets, whether you are working with one column or multiple columns.

Highlight Duplicates in a Single Column

To highlight duplicates in a single column, start by selecting the cells containing data in that column.

Next, go to Format Conditional formatting.

Under Format cells if, select Custom formula is.

Enter the following formula.

=COUNTIF(data_range, top_cell_of_data)>1

The data range needs to be an absolute reference, and the second argument should be a mixed reference with the dollar sign in front of the column letter.

 

Next, select the fill color you want to use to highlight the cells.

Then click on Ok.

You now have all of the duplicate cells highlighted.

Highlight Duplicates in Multiple Columns

To highlight duplicates in multiple columns, start by selecting the cells containing data.

Next, go to Format Conditional formatting.

Under Format cells if, select Custom formula is.

Enter the following formula.

=COUNTIF(data_range, top_cell_of_data)>1

The data range needs to be an absolute reference, and the second argument should be a relative reference with no dollar signs.

Next, select the fill color you want to use to highlight the cells.

Then click on Ok.

You now have all of the duplicate cells highlighted.

If you have any other questions you’d like answered in the Common Questions category of the Spreadsheet Life blog, leave a comment below!

This Post Has One Comment

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