Conditional formatting menu option

Google Sheets | Highlight Duplicate Data in a Column or Row

When you’re working with data in a spreadsheet, there are often duplicate values that need to be considered. This tutorial will show you how to highlight the repeated items in a few easy steps.

First, highlight the column or row that you want to evaluate.

Click Format in the top menu then Conditional formatting…

Conditional formatting menu option
Menu option for conditional formatting

The Conditional Formatting menu option will pop up a Conditional format rules menu on the right side of the screen (on the desktop version of Sheets).

If you want to learn more about the complex subject of conditional formatting, I have created a course about it over at Datacamp. This is an affiliate link and if you use it to make a purchase I will receive a portion of the proceeds. Thank you for supporting my channel!

Rules for conditional formatting

Rules for conditional formatting

Click the plus sign to begin adding the rule. In the drop-down menu for Format cells if choose the last option which is Custom formula is.

Custom formula menu option

Last menu option is custom formula

In the box that appears below Custom formula is enter =COUNTIF(A:A,A1)>1. Note that you have to start the formula with a = sign just like any other spreadsheet formula. If you were highlighting a row instead of a column, specify the row as 1:1 if the row were the 1st row. If you are not familiar with COUNTIF, learn how to use it here.

This will start at A1 and check to see if the value in A1 occurs more than once in the selected range. Then, the conditional formatting goes to the next cell, A2, and so on, until it gets to the end of your specified range. Now, all of the duplicates in your list are highlighted.

The A:A may seem a little strange but this is a method of specifying an entire column no matter how many rows are used.


Result with highlights duplicated

Video explanation

 Live examples in Sheets

Go to this spreadsheet for examples
of highlighting duplicates that you can study and use anywhere you would like.


Leave a Reply