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
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.
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: Open your Google Spreadsheet file.
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!