How can you create this chart in Excel?
The first time I saw a similar chart was in a report published by Udemy called Learning Trends & predictions in 2017.
They used a similar chart to the one below (this one just has sample data though). I found the chart informative, visually effective and interesting. It really grabbed my attention and I set out to create it in Excel.
Of course in a dynamic manner. There were two obstacles:
One was to get the exact same shape proportions for each category (i.e. no stretched out bars) and second to get the conditionally formatted part (the one with the green bar) to update automatically if another week day happens to have the most users.
You might be familiar with using shapes in charts. The problem though is, they get stretched out. Like this example below:
Notice that the week days with the most users, Thursday for example, has a stretched out tip as compared to Saturday. This is standard behavior if you try to use shapes or images for the bars.
To use shapes in the first place, all you have to do is copy your shape (Ctrl + C), then click on the series and paste (Ctrl + V). If you’d like to use the same shape I have in the image, you can insert it first from the insert/shapes options in Excel.
Here is the trick: To avoid the stretched out shape, you need two sets of shapes. One that doesn’t matter if it gets stretched out and the other is the one you want to avoid from getting stretched.
You basically need to create a stacked column chart using two sets of shapes. The part that can get stretched here is the rectangle. That’s Stack 1 in the image on the left. Stack 2, is the tip which we don’t want to get stretched.
The formula in my example for Stack 2 is =MIN(users)/2. This ensures that the tip for all the bars will remain the same. To get the bar conditionally formatted, i.e., to highlight the maximum, you need to introduce two sets of series using the max formula. The green series needs to be plotted on the secondary axis, otherwise they will end up as stacks on top of the original series (the grey ones).
This basically means that the chart that looks like a simple column chart, is in fact two sets of stacked charts. Yes, it does require some work, but once you have it setup, you can re-use as much as you want.
I find such charts a really nice addition to Dashboards and special reports!
Watch the steps in this video:
Feel free to Download the Workbook HERE.
The new Excel Dashboards course is here!
Now available on Udemy
Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.