Highlighting Duplicates in Google Sheets

Highlighting duplicates is a great technique to know when analyzing large pieces of data, especially within Google Sheets.

I will show you the technique that I use to highlight any and all duplicates quickly and easily.

Step #1: "Conditional Formatting"

How to find Duplicates in Google Sheets Step 1

In the "Format" menu, you will find an option titled "Conditional Formatting". That will be where you highlight your duplicates.

Step #2: "=COUNTIF()"

How to find Duplicates in Google Sheets Step 2

This part can be a little tricky the first time, but it becomes incredibly easy to do in the once you get the hang of it. There are three steps that you need to understand:

1. Designate the Area Where You Want to Highlight Duplicates

You can either highlight or input the range remotely. Since it is the cells in the D column, the range will be "D3:D12".

2. Select the "Custom Formula Is" Format Rule

This format rule is found at the very bottom of the dropdown menu. It will let you input special commands to Google Sheets and have even more control of how you can format your data table.

3. Use the =countif()>1" function

What this function does is it highlights a cell if there is a duplicate exactly like it in the selected area. Since the column starts at cell D3, the Formula will look like 

"=countif(D3:D,D3)>1"

If the cells were in column C and started at C6, the function would read: as "=countif(C6:C,C6)>1"

Step #3: Make Sure it Worked

How to find Duplicates in Google Sheets Step 3

Finally, you will be able to look at all of the duplicates, now that they have been highlighted.

In this case, the items that duplicated are:

  • Marty-Mart
  • Broger
  • Tony's Meats

In the case of this data table, those duplicates tell us that if you are shopping, you should stop at those three stores for the most efficiency on your errands.


And there you have it; that is how you find and highlight duplicates in Google Sheets.

Leave a Reply

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

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Google Sheets Magic Tricks!

Interactive, Easy and Free Way to Learn 5 Awesomely Cool Google Sheets Tricks!