We can generally apply three types of highlighting rules to duplicates. Without understanding that, there is no meaning in proceeding with how to highlight visible duplicates in Google Sheets.

What are those three types of format rules?

Duplicates mean multiple occurrences of values. So there will be a first occurrence, a second occurrence, a third occurrence, and so on.

When you find a duplicate that repeats thrice, what you may want to do usually with conditional formatting?

Do you prefer to highlight all three occurrences, the last two occurrences, or the first two occurrences?

See the image below, where I have applied different conditional formatting rules.

You can see the type 1 rule ( I named it so for this tutorial purposes) in column A, type 2 in column C, and type 3 in column E.

I have explained these conditional formatting types in my tutorial – Highlight Duplicates in Single, Multiple Columns, and All Cells in Google Sheets.

I hope you have gone through that. If not, read that tutorial first, though it’s not a must here.

Visible Duplicates in Google Docs Sheets – What is it?

Suppose cells A1 and A2 contain the number 100, so duplicate values. I am hiding or filtering out row # 1 (cell A1).

After that, there is only one occurrence of the value 100 (in cell A2). So it’s not a duplicate considering visible duplicates.

In the following examples, you can learn how to exclude the values in hidden/filtered-out rows while finding duplicates and highlighting them.

How to Highlight Visible Duplicates in Docs Sheets

We must use a helper column in Docs Sheets to exclude hidden rows in conditional or criterion-driven formulas. So let me begin with that.

In the first example, we will use type 3 duplicate highlighting, i.e., highlighting all the occurrences of duplicates.

Highlight All the Occurrences of Visible Duplicates in Google Sheets

Here is the screenshot. In this, see the formula in cell B1, which I have dragged down until cell B9.

=subtotal(103,A1)

As an alternative, empty B1:B9 and insert the following formula in cell B1, where the MAP expands the SUBTOTAL.

=map(A1:A9,lambda(row,subtotal(103,row)))

What does this Subtotal formula do?

When a row is hidden, the value in that cell, I mean the Subtotal formula applied cell, becomes 0 (zero)!

You May Like:- Subtotal function with conditions in Excel and Google Sheets.

To test this Subtotal feature, let me use the below formula in cell D5.

=B3

The Subtotal formula returns 1 in cell D5. See the value in cell D5 changes to 0 when I hide row # 3.

I am going to make use of this feature of the Subtotal formula to highlight only the visible duplicates in Google Sheets.

Here is the custom formula to conditional format visible duplicates in Google Spreadsheet.

=countifs($A$1:$A25,$A1,$B$1:$B25,$B1)>1

I am applying this rule up to row # 25. So copy the Subtotal formula in cell B1 up to this row.

If you are using the Map formula, replace A1:A9 with A1:A25.

To apply this formula in conditional formatting, do as follows.

1. Go to the menu Format > Conditional Formatting.
2. There set the “Apply to range” to A1:A25.
3. Select “Custom Formula” under “Format rules” and enter the above formula.

If you refer to the above screenshot (this screenshot contains three images, I am talking about the leftmost one), you can see three occurrences of the value Orange.

When I hide row # 7, it appears twice. So again a duplicate.

But when I hide rows 6 and 7, only one Orange is visible.

Even though it has duplicates in the hidden rows, the highlighting won’t get applied.

The reason, there are no visible duplicates of this value.

Highlight All Visible Duplicates Leaving the First Occurrence

In the beginning, I mentioned the three types of formatting.

Here is the second one, in that, i.e., highlights all duplicates leaving the first occurrence.

Every setting is as per the above example, like using the Subtotal/Map formula and applying the conditional formatting rule.

But the custom conditional formatting formula is different.

Here is the formula to use in this case.

=countifs($A$1:$A1,A1,$B$1:$B1,B1)>1

The above is the type 2 formula to highlight visible duplicates in Google Sheets.

See the following screenshot to understand how this differs from the previous rule.

Highlight All Visible Duplicates Leaving the Last Occurrence

It is similar to the just above highlighting rule.

Here also, we are ignoring the values in the hidden rows with the help of the helper column and the Subtotal Formula.

Here, the only difference with the just above formatting rule is it highlights all the duplicates leaving the last occurrence (type 1).

=countifs(A1:A25,A1,B1:B25,B1)>1

Once again, I am going to make use of the screen capture to make you understand this.

That’s all. You can follow the above three types to highlight visible duplicates in Google Sheets. Enjoy!

More Conditional Formatting Tips:

1. Date Related Conditional Formatting Rules in Google Sheets.
2. Find All the Cells Having Conditional Formatting in Google Sheets.
3. AND, OR, or NOT in Conditional Formatting in Google Sheets.
4. Conditional Format Based on Group of Data in Google Sheets.