We all know the frustration of dealing with duplicate values in our data. Thankfully, Google Sheets provides powerful tools to help us identify and highlight these duplicates. In this article, we’ll explore three different types of highlighting rules for visible duplicates in Google Sheets. So let’s dive in!
What are the Three Types of Format Rules for Duplicates?
Before we get started, it’s important to understand the three types of format rules that we can apply to duplicates. These rules determine how we want to highlight the duplicate values in our data. Do we want to highlight all occurrences, only the first two occurrences, or perhaps the last two occurrences? Let’s take a look at an example to better understand these rules.
In the above image, columns A, C, and E demonstrate the three different types of formatting rules. To learn more about these conditional formatting types, check out my tutorial on Highlight Duplicates in Single, Multiple Columns, and All Cells in Google Sheets.
Visible Duplicates in Google Sheets – What is it?
Before we proceed, let’s clarify what we mean by “visible duplicates”. Imagine you have two cells, A1 and A2, both containing the value 100. Initially, these are considered duplicate values. However, if you were to hide or filter out row #1 (cell A1), you’d be left with only one occurrence of the value 100 (in cell A2). In this scenario, the value in cell A2 is not considered a duplicate because it is the only visible occurrence.
How to Highlight Visible Duplicates in Google Sheets
To highlight visible duplicates in Google Sheets, we’ll need to use a helper column. This column will help us exclude hidden or filtered-out rows when applying conditional formatting rules. Let’s walk through the process step by step.
Highlight All the Occurrences of Visible Duplicates in Google Sheets
In this example, we’ll use the “type 3” duplicate highlighting rule, which involves highlighting all occurrences of duplicates. Here’s the formula you need to apply to the helper column:
=subtotal(103,A1)
Alternatively, you can use the following formula in cell B1 and drag it down until cell B9:
=map(A1:A9,lambda(row,subtotal(103,row)))
This formula makes use of the SUBTOTAL function, which returns a value of 0 for hidden rows. By applying the COUNTIFS formula in conditional formatting, we can highlight only the visible duplicates. Here’s the custom formula:
=countifs($A$1:$A25,$A1,$B$1:$B25,$B1)>1
To apply this formula in conditional formatting:
- Go to the Format menu and select Conditional Formatting.
- Set the “Apply to range” to A1:A25.
- Choose “Custom Formula” under “Format rules” and enter the above formula.
In the screenshot above (leftmost image), you can see three occurrences of the value Orange. However, when rows 6 and 7 are hidden, only one Orange value is visible. The highlighting won’t get applied because there are no visible duplicates of this value.
Highlight All Visible Duplicates Leaving the First Occurrence
In the previous example, we highlighted all occurrences of duplicates. Now let’s focus on highlighting all duplicates while leaving out the first occurrence. The process is similar to the previous example, but the formula changes. Here’s the custom conditional formatting formula:
=countifs($A$1:$A1,A1,$B$1:$B1,B1)>1
Refer to the screenshot below to see how this differs from the previous rule.
Highlight All Visible Duplicates Leaving the Last Occurrence
Finally, let’s look at the third type of formatting rule, which highlights all duplicates while leaving out the last occurrence. Again, we’ll make use of the helper column and the COUNTIFS formula in conditional formatting. Here’s the formula you need to use:
=countifs(A1:A25,A1,B1:B25,B1)>1
Take a look at the screenshot above to better understand this rule.
That’s it! You now have three types of rules to highlight visible duplicates in Google Sheets. Experiment with these rules to find the best fit for your data. If you want to learn more about conditional formatting in Google Sheets, be sure to check out my other Conditional Formatting Tips and Tricks.
Now go ahead and make your data shine with perfectly highlighted visible duplicates in Google Sheets! And remember, for more helpful articles and resources, head over to Crawlan.com. Happy sheeting!
