Highlighting Duplicates in Google Sheets

Highlighting duplicates is a great technique to know when analyzing large pieces of data, especially within Google Sheets. Duplicate data can be a huge pain in the neck and a major eyesore for any professor or boss who has to read the spreadsheets that you make. Thankfully, Google Sheets has a quick and effective way to quickly identify duplicates in your spreadsheets. 

checking for merging duplicates in google sheets is a very important aspect of any statistics, and this technique is invaluable. I can't count count all of the times that I had to use this function to find duplicate cells in spreadsheets that had dozens of rows and columns.

I will show you the technique that I use to highlight any and all duplicates quickly and easily in google sheets. You can search for duplicates in google sheets using these three steps.

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 


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. Like I said in the opening segment, identifying duplicates in google sheets can and will be a critical part of your data analysis in the future.

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"}