How to Highlight Duplicates in Google Sheets

Being able to filter out duplicates in a cell can be an essential and convenient skill when arranging your spreadsheet. Learning how to do this saves you time, increases efficiency, and fixes so many problems for long documents in Google Sheets.

With that said, how can you highlight duplicates in Google Sheets? Well, there are a couple of ways that you can do this. More so, there are also several variations as to how you can specify which duplicates you want highlighted. With that said, there’s no shortcut button for this, so this article will be a little technical.

3 Ways to Highlight Duplicates in Google Sheets

  • Highlight all duplicates in the column or row
  • Highlight the rows of all duplicates in a column and vice versa
  • Highlight all duplicates except the first instance

These three methods should be more than enough for your regular needs. We’ll go over these variations in a step-by-step process throughout this entire article. If you aren’t familiar with formulas and Google Sheets functions, don’t worry! We’ve written this article with beginners in mind, so you can follow and do this for your spreadsheet with ease!

Without further ado, let’s get started!

Why Should I Highlight Duplicates in Google Sheets?

Being able to highlight the duplicates in your spreadsheet helps you get rid of entries that are repetitive and unnecessary. An important aspect of a good spreadsheet is its ability to be efficient and fast. 

A good example of when highlighting duplicates is convenient is when you’re filling out a registration sheet. Instead of looking through each individual name 1-by-1, or worse, comparing ID numbers manually, automatically highlighting duplicates will save you time and money. 

This is especially true for spreadsheets that have been used for months and are typically used by a lot of people who share the same spreadsheet. You’ll find that this is a common practice in big companies, startups, businesses, and even personal documents.

With that being said, it’s time to highlight duplicates! Here’s how you can do just that for your Google Sheets spreadsheets.


Method 1: Highlighting All The Duplicates in A Column or Row

Starting with the most basic method, we’ll be highlighting single cell duplicates in a column. This will highlight duplicates in a column, without marking the entire row. We recommend using this method when you’re simply trying to look for duplicates without needing to highlight the rows of those duplicates. 

Let’s go over the step-by-step process for this method.

Step 1: Open up a spreadsheet in Google Sheets.

Before anything else, you’ll need a spreadsheet open in Google Sheets so you can follow us throughout the guide. If you already have an existing spreadsheet, feel free to use that instead. However, for those of you without a sample document, you can make a copy of the spreadsheet we’ll be using in this method. 

Alternatively, you can also make one for yourself from scratch by opening a Blank spreadsheet in Google Sheets

Step 2: Highlight the cells you want to filter out.

In the next step, we’ll be highlighting the cells that we want to segregate. These will be the cells that will have duplicates highlighted. Since this method only tackles on one column, simply highlight the To Bring: column in the sample spreadsheet provided. 

You can do this by holding down left-click and dragging your selection over the target area. Alternatively, this also works if you press on the Shift + arrow keys on your keyboard.

Step 3: Open up ‘Conditional formatting’.

Now that you’ve highlighted the necessary cells, let’s start navigating to the Conditional formatting window. You can do this by clicking on the Format button at the top-left corner of your window and moving your mouse down to select Conditional formatting.

Step 4: Creating the formula.

This step will be a little lengthy, but this is the crux of the entire process. Under Format rules, click on the drop-down selection and scroll down until you can find Custom formula is. In the Value or formula text box, simply copy-paste the formula provided below. Before you proceed, keep in mind that the formula will be different for other spreadsheets depending on the cells you want to highlight. 

=countif($B$4:$B$21,B4)>1

Where: 

=countif() – function that counts the number of cells that meet the criteria you defined in the formula

$B$4 – refers to the starting point of the column

$B$21 – refers to the ending point of the column

B4 – is the criteria

>1 – counting anything that has more than 1 instance (or has a duplicate)

It should look something like this:

Feel free to change the color of the highlight by clicking on the paint bucket at the bottom-right corner of the Conditional format rules window. Alternatively, if you have your own spreadsheet and want to highlight all the columns in a row, simply change the starting point and end point into a row coordinate. 

Voila! You’ve successfully highlighted duplicates of a column in your spreadsheet! Now, let’s move on to the next step and make this more efficient. 


Method 2: Highlight The Row of The Cell That’s A Duplicate

In this method, we’ll be taking the next step and highlighting the entire row of the cells that’s marked as a duplicate. This will make your spreadsheet easier to read, especially if you have a long row of data. 

Here’s how you can do that!

Step 1: Bring out a spreadsheet file.

Of course, just like the previous method, you will first need to start by opening up a Google Sheet file. If you’re already using one of your own, you can go ahead and utilize that existing spreadsheet instead of creating a new one. For those of you who don’t have a spreadsheet ready, you can make a copy of our sample document, so you can follow this tutorial. 

Step 2: Highlight the cells you want to segregate.

Let’s start by highlighting the entire row and column of the cells that we want to filter out. This will be the length of the rows to be highlighted when we find a duplicate in a specific column. 

Step 3: Go to the conditional formatting window.

Just like the previous step, navigate to the conditional formatting window. While the highlights are active, go to the Format button and select Conditional formatting near the bottom-most part of the drop-down menu. 

Step 4: Encoding the formula.

While this step is generally long, we’ll try to break this down into bite-sized pieces. First, click on the drop-down menu for Format cells if… and select Custom formula is. Now, in the Value or formula field, copy-paste or encode the formula below. Keep in mind that the formula will be different for other spreadsheets, especially if you’re highlighting different cells.

=countif($B$4:$B$21,$B4)>1

Where: 

=countif() – function that counts the number of cells that meet the criteria you defined in the formula

$B$4 – refers to the starting point of the column

$B$21 – refers to the ending point of the column

$B4 – is the criteria

>1 – counting anything that has more than 1 instance (or has a duplicate)

Your spreadsheet should look something like this:

You can alter the color of the highlight by accessing the paint bucket at the Conditional format rules window. For those of you who want to highlight columns instead of rows, just use the coordinates on a horizontal plane. 

Congratulations! You’ve successfully highlighted the rows of duplicate entries in a column of your spreadsheet! Let’s move on to the third and final method.


Method 3: Highlight the Duplicates Except The First Instance

Now, what if you wanted to highlight the duplicates without highlighting their first instances? This is a great way to avoid seeing so many highlighted rows and simply filter out the cells that are duplicates of other cells. 

If that explanation was a little confusing, here’s what we mean. 

Without highlighting the first instance, the spreadsheet on the right side looks much cleaner than the one on the left. With that said, let’s figure out how you can do this on your spreadsheet.

Step 1: Open up a spreadsheet on Google Sheets.

If you already have a spreadsheet of your own, you can use that but just remember to change the formula for the cells specific to your spreadsheet. However, you can also make a copy of our sample document, dedicated for this tutorial, so you can follow us closely at every step of the way. 

Step 2: Select the cells that you want to highlight.

Next, we’ll be selecting the cells you want to filter out. These will be the cells where we’ll highlight the duplicate entries. Simply left-click and drag your mouse across the cells to highlight, or press the Shift + arrow keys to select the area.

Step 3: Open up the conditional formatting option.

From there, move your mouse to the top of your screen and navigate to Format and Conditional formatting

Step 4: Setting the formula.

Here’s where things get a little technical. Go ahead and click on the drop-down menu near Format cells if…, scroll down, then select the Custom formula is option. Copy-paste or type in the formula provided below in the Value or formula field. For those using a different spreadsheet, you’ll have to use your custom cell values instead.

=countif($B$4:$B4,$B4)>1

Where: 

=countif() – function that counts the number of cells that meet the criteria you defined in the formula

$B$4 – refers to the starting point of the column

$B4 – also refers to the starting point of the column

$B4 – is the criteria

>1 – counting anything that has more than 1 instance (or has a duplicate)

After you’ve done that, it should look something like this:

You can change the color of the highlights by clicking on the paint bucket, near the Formatting style accessories. You can also do this in a different orientation, highlight columns instead of rows, just by simply using row coordinates instead. 


Conclusion

Congratulations on reaching the end of this tutorial! Hopefully, we’ve helped you highlight duplicates in Google Sheets. With that said, here’s a brief summary of everything we covered today.

Highlighting duplicates in Google Sheets can be done in multiple ways. You can highlight single cells in a column, highlight the rows of those columns, and even highlight the duplicates without highlighting the first instance.

We hope you found this tutorial helpful!

Leave a Comment