We can use a custom formula in conditional formatting to achieve alternating colors for groups of rows in Google Sheets. However, this formatting may break when a filter is applied due to hidden rows within the range.

Specifically, I’m referring to a highlighting rule that highlights rows when the value changes (indicating the start of a new group) in a column, and the problem this rule encounters with filtering.

I have a tutorial on creating conditional format rules for achieving alternating colors for groups of data in Google Sheets. You can find it here: Conditional Format Based on Group of Data in Google Sheets.

Here, in this tutorial, we’re also discussing the associated filter issue.

If you have some spare time, you can follow my above tutorial (though it’s not necessary) to set up conditional formatting for groups of rows with alternating colors. Then, try filtering your highlighted grouped data.

You’ll notice that sometimes filtering breaks the pattern of alternating colors for groups.

In the example screenshot (GIF image) above, there’s a filtered list in column A. Similar items have been grouped by sorting, and alternating colors have been set for groups of data (rows).

Observe what happens when I filter out the row containing ‘Orange’. The alternating group colors break, don’t they? This tutorial offers a solution to this issue.

Alternating Colors for Groups in Visible Rows

In our example, you can apply the following rule to the range A2:A12 to achieve alternating group colors for filtered data in Google Sheets:

=ISODD(XMATCH($A2, UNIQUE(FILTER($A$2:$A$12, MAP($A$2:$A$12, LAMBDA(r, SUBTOTAL(103, r)))))))

Where:

• $A2: denotes the starting cell in the category/group range.
• $A$2:$A$12: represents the category/group range to be highlighted.

This formula will highlight every alternating group with your chosen color.

Now, let’s apply this formula (highlight rule) to the range A2:A12.

1. Select A2:A12.
2. Click Format > Conditional Formatting.
3. If any rule is already applied to the range, click “+ Add another rule”. Otherwise, move to the next step.
4. Under “Format rules”, select “Custom formula” from the drop-down menu.
5. Copy and paste the provided highlight rule in the given field.
6. Choose a fill color under the formatting style.
7. Click Done.

This approach applies alternating colors for groups without breaking when you apply a filter to the range.

It highlights the first visible group, third visible group, fifth visible group, and so on, leaving the groups in between with no color.

If you want a different color for visible groups 2, 4, 6, and so on, apply the following rule as well:

=ISEVEN(XMATCH($A2, UNIQUE(FILTER($A$2:$A$12, MAP($A$2:$A$12, LAMBDA(r, SUBTOTAL(103, r)))))))

When applying, choose a different fill color (please refer to the 6th step above).

This method ensures the proper application of alternating colors to each category in a filtered range in Google Sheets.

Note: The list must be sorted before applying the rules.

Formula Breakdown

There are three key components in the formula:

1. A formula that extracts unique values in the visible rows.
2. An XMATCH that matches each value in the highlight range in this extracted unique value range.
3. ISEVEN / ISODD to test whether the XMATCH output is an odd number or even.

Let me explain it:

The following formula extracts unique values from the filtered range A2:A12, as detailed in my tutorial titled: UNIQUE Function in Visible Rows in Google Sheets:

=UNIQUE(FILTER($A$2:$A$12, MAP($A$2:$A$12, LAMBDA(r, SUBTOTAL(103, r)))))

Assuming it returned the following values:

The XMATCH function matches $A2 in this range and returns 1 if the value in cell $A2 is “Apple”, 2 if the value is “Orange”, 3 if the value is “Banana”, and so on:

XMATCH($A2, ...)

ISODD this result returns TRUE if the number is an odd value:

=ISODD(XMATCH(...))

So it will highlight the groups Apple, Banana, and Mango. Because $A2 will become $A3, $A4, … This test will take place in each row in the range A2:A12.

Resources

Here are two related resources:

• Applying Alternating Colors to Visible Rows in Google Sheets & Excel
• Customizing Alternating Colors of a Table in Google Sheets