Excel – Alternating color in visible rows based on column value in Excel

conditional formattingmicrosoft excel

This is similar to Alternate grid background color in excel when a value of a single column changes? , and I believe it can be done using SUBTOTAL(109,... , but I can't quite figure it out.

The first column in my table is a group number, and all rows with the same group number should have the same background. The table is sorted by the group number.

First view

I want to alternate the row color per group, based only on the visible rows. In this example I've hidden A and C . Note that praesent and libero have swapped colors based on the visible rows.

Filtered view

I'm free to add in hidden helper formula columns, but I'd prefer it all to be in the conditional formatting.

Best Answer

  • Here is an answer with two helper columns (of course you can hide them):

    • helper1: =AGGREGATE(2,5,A2)
      • it just shows 1 for visible and 0 for invisible rows (of course you always see 1 :) )
    • helper2: =IF(C2=1,IFERROR(MAX($D$1:D1)+(COUNTIFS($A$1:A1,A2,$C$1:C1,1)=0),1),"")
      • MAX($D$1:D1) - looks for greatest group number so far
      • COUNTIFS($A$1:A1,A2,$C$1:C1,1) - checks whether current value is present in ABOVE VISIBLE rows
      • MAX(...)+(COUNTIFS(...)=0) - increases group number if it's a new group
      • IFERROR(...,1) - sets group number to 1 for first visible row
      • IF(C2=1,...,"") - calculates group number only for visible rows

    Setting up conditional formatting:

    • go to: Home - conditional formatting - new rule - use a formula...
    • in formula enter =MOD($D1,2)=1
    • set your desired formatting

    enter image description here