Variable Sized Groups In Each Column

Every once in a while we get a unique challenge in Excel. This one required some creativity to quickly create a solution due to a pending deadline.

Clarifying the requirements was the first challenge due to the uniqueness of this request.

Why would someone want this? It doesn’t matter. Our concern is how to create a solution.

Here we see the full data-set: 7 columns of data and 30 rows. You can also see the helper column I created to the right of each column of data.

Each column of data looks up to the variables in rows 1, 2, & 3 (top x, top x value, % of group).

Conditional formatting rules can be formulas! I used this formula:

Helper column defines groups. I used this formula inside conditional formatting:

I use check boxes to turn the rules off & on. Notice $Z$2=TRUE and $Z$3=TRUE in the rules above.

If check box “Switch (value > top x value)” is checked then cell Z2 = TRUE.

4. Solution must scale to additional columns

I created 1 set of conditional formatting rules that work for all columns of data. I copied column D helper to other columns (H, K, N, Q, T, W).

Get the file below to examine all the conditional formatting rules (i.e. add borders around the groups, turn off non selected group, etc).

I created formulas this callout box uses to display stats about the selected group.

Click arrows below to change data & group selections (text in callout box above changes).

Download my Excel file here 🙂

Click here to watch my YouTube video.

My name is Kevin Lehrbass. I live in Markham Ontario Canada.

Why do I love working with data? It’s fun to solve data puzzles!

The site is about Microsoft Excel.