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.

 

 

Requirements

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

 

  1. Bold the top x numbers (top 7 in this column)
  2. Highlight group green if 20% of values in top 7
  3. Switch off 1. & 2. above (remove formatting)
  4. Solution must scale to additional columns

 

 

 

 

 

 

 

 

 

 

 

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

 

Additional Columns of Data

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).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Solution

1. Bold the top x numbers (currently set to top 7 in column D)

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

 

 

 

 

2. Group = green if x% of values in top x (cell D1 =  7 values, D3 = 10%)

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

 

 

 

 

 

3. Switch off 1. & 2. above (remove 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).

 

 

BONUS!

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).

 

 

 

 

Excel File

Download my Excel file here 🙂

 

 

YouTube Video

Click here to watch my YouTube video.

 

 

About Me

My name is Kevin Lehrbass. I live in Markham Ontario Canada and work as a Data Analyst at McKinsey & Company in Toronto.

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

Check out my recommended Excel Training!

 

 

Post a comment