Get More From Excel - Banding by Groups

Having spent many years as a Business Analyst, I know how much Excel has become the lifeblood of many business users. I thought I’d share a few tips and tricks I’ve picked up over the years that you can use in just about any industry. Today’s article is about color banding by groups.

If you live in Excel, you know that sometimes presentation can be everything. Giving people a visual queue to help them understand groups can give that extra professional touch to your worksheet. Excel has banding built in, but it is limited to every alternating row. Take the below list of the 50 top grossing films of 2020.

Suppose we’d like to show the list by Distributor. First, sort the list by what you’d like to group by, and add a column to the left of the set.

Next, we want to add the below formula into cell A2. I’ll walk you through it step by step.

=MOD(IF(E2=E1, A1, A1+1), 2)

Let’s look at it from the inside out. First we specify which column we want to do our banding on. In this case, the “Distributor”.

=MOD(IF(E2=E1, A1, A1+1), 2)

Next, we are saying that if the current row has the same value as the one above it, repeat the same number, if it’s different, increment it by one.

=MOD(IF(E2=E1, A1, A1+1), 2)

Finally, we use a “modulus” function to separate them into odd and even values.

=MOD(IF(E2=E1, A1, A1+1), 2)

If we fill the formula down, we wind up with a list that looks like this:

Next, filter for the “0” records, and apply your color of choice (I’m partial to a nice sky-blue myself).

Remove your filters, and “Voila!”, you have your finished product, with alternating colors every time the Distributor changes.

Let’s take this one step further. What if we want to band by more than a single field? Let’s order by Genre, then by Distributor, and add another column to the left. Use a formula to concatenate all your fields you want to group by, in the same order your sort was in.

Do the same formula again, but this time use your new field as the target. Fill down the formula, filter for "0", apply your color, and remove the filter.

Here is a real world example, where I had to show that we had cases of multiple product matches for the same provided title from different sources. Sometimes this was because it was a movie bundle linked to the child titles, but sometimes it shows that one of the mappings was a mistake. This list is much easier for a user to review and digest quickly with the banding.

That’s it! It takes some practice, but once you use it a few times, you may not even need to refer back to the formula anymore. At this point you can remove or hide the extra columns, but I like to leave them in, in case I need to order by some other combination or refresh the data.

While this can be handy in the right circumstances, it does have some limitations. In particular, since it doesn’t automatically update, it won’t look good if the user re-orders or filters the data.

Let me know in the comments if you found this helpful, and be on the lookout for more tricks in the future!