Apply Conditional Format for alternate row color based on groups…

…that works even when filter is applied.

Excel Tip - CF to make color bands

The situation:

We have a table that we would like to apply color banding based on groups.  We can achieve this by inserting a helper column to identify the sequence of each group, with the following formula:

In C2, input

=IF(A1=A2,C1,SUM(C1,1)) ‘copy down

and then apply conditional formatting based on the sequence.

Excel Tip - CF to make color bands1

Select the range A2:C29, then apply this formula to conditional formatting:

=ISEVEN($C2) ‘note the usage of $

Here’s the result!  It works great… until you apply a filter on subset.

Excel Tip - CF to make color bands

Why is that?

Because we are referring to a static (even) number for the color banding.  The number won’t response to the filter.  After the filter, only subsets of even number will be highlighted.  @_@

The desired result

We want the color banding to be responsive to filter, like the screen cast below:

Excel Tip - CF to make color bands0

With some twists to the formula for the helper column, this can be done magically.

You may download Sample File to follow along. 

The Challenges

We need to overcome two challenges here

  1. To identify if a row is visible or not (by using SUBTOTAL)
  2. To get the correct sequence for visible groups only (by using MAX)

Solution to challenge 1

This can be solved by a surprisingly short formula

In D2, input

=SUBTOTAL(103,A2) ‘copy down

This formula returns a result of either 1 or 0 for visible and invisible row respectively.

Excel Tip - CF to make color bands2

To learn more about SUBTOTAL, read this post.

Solution to Challenge 2

When rows are hidden by filter, the corresponding values in column D will become 0.

Excel Tip - CF to make color bands3.png

When this happens, we want

  1. to reset the Subset sequence for Subset 2, i.e. values in C5:C6 from 2 to 0
  2. to change the Subset sequence for Subset 3, i.e. values in C7:C10 from 3 to 2 (this is the most tricky and challenging part)

Challenge 2.1 – Reset the subset sequence for hidden rows

We can solve this by multiplying C2 with D2

In C2, input the formula:

=IF(A1=A2,C1,SUM(C1,1))*D2   ‘copy down

=Excel Tip - CF to make color bands4

So now, when a subset is filtered out, the subset sequence becomes 0.  The rows will also been highlighted if we used ISEVEN for the conditional formatting… but it doesn’t really matter as we won’t see it (when it is hidden). 😛

Challenge 2.2 – Adjust the subset sequence for the next visible group

Let’s exam what the original formula does.

=IF(A1=A2,C1,SUM(C1,1))

The blue portion of the formula SUM(C1,1) tells Excel to add 1 to the number above the current cell when it detects a change in “Subset” (when A1=A2 is False).  It works fine when we do not filter the data.

However, when a filter is applied, the formula doesn’t work properly.  It returns 1 to all subsets that are next to the hidden subsets, because the sequence for hidden subset is reset to 0.

Excel Tip - CF to make color bands5

To achieve this, we need to twist the blue portion of the formula

from

=SUM(C1,1)

to

=SUM(MAX($C$1:$C1),1) Note the usage of $

By using =SUM(MAX($C$1:$C1),1), we instruct Excel to add 1 to the maximum number above the current cell.  It looks through from the very first cell on the top to the cell that is just above the current cell.  As such, the 0 resulted from hidden rows is ignored.

And we will get the correct sequence after filtering. 🙂

Excel Tip - CF to make color bands6

Challenge 2.2 is solved with this formula:

=IF(A1=A2,C1,SUM(MAX($C$1:$C1),1))*D2

Tip: You may substitute SUBTOTAL formula in D2 into the formula, i.e.

=IF(A1=A2,C1,SUM(MAX($C$1:$C1),1))*SUBTOTAL(103,A2)

so that you won’t need the extra helper column for identifying if a row is visible

Wrap up

Putting everything together, we need the following formula for the helper column:

=IF(A1=A2,C1,SUM(MAX($C$1:$C1),1))*SUBTOTAL(103,A2)

Then we apply conditional formatting to the range A2:C29 by the following formula:

=ISEVEN($C2)

Excel Tip - CF to make color bands1

Make sense?

Limitation

Indeed this solution is not working in all scenarios.  If you have multiple levels of hierarchical data, it works well only when you reference to the lowest level of data.

If you set the formula in helper column based on value on column C, but you apply filter to Column D, you may break the conditional format.

Excel Tip - CF to make color bands7

Try it out with the Sample File.

Thanks Casey for the question.  I hope this solves your problem.  If it does, please share this. 🙂

 

 

 

Advertisements

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged , , , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.