I field a fair number of questions, either in my blog’s comments, in the various online forums, or via email. Sometimes the question in a comment is a bit off topic, or the answer is more than I want to bury in my own comment. The forum’s text-only format may not be sufficient to show the answer. Or the email question is of more general interest and I want to share it.
To address this situation, I’ve decided I need a new category of blog posts: You Asked For It.
In this first installment, I’ll answer a question from the comments about Clustered-Stacked Column Charts. Janez asked how to make a chart that has a column chart with composite values (e.g., 1, 2) growing like stalagmites from the bottom, and clustered constituent values (1a, 1b, 2a, 2b) hanging like stalagtites from the top. I’m not sure this is the best way to represent this information, but I’ll show this chart and I’ll show my preferred approach.
I made up some simple data:
The chart begins its life as a simple clustered column chart. I’ve changed the gap width to 100.
The A and B series are moved to the secondary axis. I’ve also added data labels to these two series, positioned the labels in the central position, and edited the text of the labels.
I’ve changed the secondary axis gap width to 200, which for two series matches it up with the primary axis 100 gap width with one series. I’ve also removed the secondary Y axis, so all bars are measured on the same basis. Finally I’ve added custom data labels to the Total series as well. This is my preferred way to display this kind of data.
So what if we still want the opposing columns? Take the second chart, and add a secondary category axis. The axis is added at the top of the chart, and the secondary series hang from it. They hang down to their values on the secondary Y axis, so the bar lengths do not encode the values: the space between the bars and the zero at the bottom of the chart represents the values. (I’ve temporarily changed the primary axis gap width so that the primary series are not totally obscured.)
Format the secondary Y axis so that it is plotted in reverse order, and so the secondary category axis does not cross at the maximum value.
That’s almost done. Let’s adjust both axis scales to run from zero to 140, so the bar lengths are scaled the same and they no longer overlap. The gap widths for primary and secondary series are 100 and 200.
This needs a little help. I’ve hidden the secondary category axis labels, and I’ve hidden the lines for both Y axes. I’ve hidden unneeded Y axis labels using custom number formats [<=80]General;;; and [<=60]General;;; and I’ve added labels to the Total series.
I think the preferred version of the chart (the third one in this post) is better for a few reasons. First, I think it’s less confusing or disorienting if all of the bars start at the same baseline and stretch in the same direction. Second, it’s more evident that the A and B columns are somehow related to the Total columns. Third, the resolution is better: the full Y axis scale is 80. The bottom chart’s fully Y axis scale of 140 squashes all of the bars and reduces resolution. Finally the first chart is a little less complicated to create and maintain.
Liu 's chart blog says
This chart is too hard to understand, I think you shouldn’t give your approach, though it is of trick, your advice should is : tell your reader , never use this type of chart.
Dennis Wallentin says
Jon,
In my opinion this is an excellent service and I hope that other, like me, will appreciate it and follow it.
Good luck!
Dennis
David Ritchie says
Sometimes you need to try something to know it doesn’t work.
It’s also fun to try to figure these type of requests out, you never know what you might learn while doing it.
Jon Peltier says
David – Plus I get to show off!
TV says
I’m curious what you think of histograms like at this link:
http://www.stat.columbia.edu/~cook/movabletype/archives/2009/10/variations_on_t.html
They link to some R code, which I’ve never used. I suppose the XL crowd could spend a lot of time trying to keep up with specialized code like this… Still, once the widths are calculated, you’ve presented the relevant plotting techniques already, I believe.
Jon Peltier says
TV –
I saw Andrew Gelman’s post and scanned the article he cited. I haven’t had a chance to think about it.
Given the widths and heights of the bars, you can build a chart relatively easily. It’s a Marimekko Chart with one area series and the area series is regular or stacked area, not 100% stacked.
chrisham says
Thanks Jon, I truly owe more than a cup of coffee, learnt a lot thru your tutorials.
Sal Paradise says
Another way is to do it with dummy series.
You put gaps between each numbered series, like this:
(gap)
1A value
1B value
(gap)
2A value
2B value
(gap)
etc.
You then create a formula next to 1A saying =1B
And a formula next to 1B that says =1A
Then copy said formula to your other pairs (so if A/B are 1-2, the column next to A/B will be 2-1).
Then graph it as a stacked bar chart. Make the gap 0. The only ‘catch’ is you have to manually color the alternating A/B values (or you can offset the B values by a column, and the totals by another column).
Let me know if you need more detail (like screenshots). I have a special application that I use graphs like this for, but if you’re not putting special value markers or anything on it (in other words, you don’t need the secondary series for anything else), then Jon’s way is a lot better and cleaner in many ways.
Jon Peltier says
Here’s an illustration of Sal’s approach. The data has the A and B values switched in the Total column.
Make a stacked column chart.
Set gap width to zero, and it’s done.
Don’t forget how versatile Excel can be. You can also put the Total column in the front of the data range, and put the real total in the cells.
Make a clustered column chart
Set gap width to zero and overlap to 100. The later series (A and B) slide in front of the earlier series (Total).
Daniel says
A bit out of context, but I don’t know where to place this.
I have charts that use two columns of data as the x-axis labels (Only one axis). When I try to format the axis label alignment (say to vertical text), only one set of labels is affected. How do I fix this?!!
Thanks.
Jon Peltier says
Sorry, Daniel. That’s the way it works. However, you can work around this limitation using the technique in Individually Formatted Dual Category Labels.
Neil Clarke says
Hello Jon,
I am a newbie to charts and i am hoping you can give me some pointers as i have been tasked with creating an XL solution for a manually produced ‘chart’ which is currently made by cutting and pasting multiple charts and visuals together.
I would be very grateful if you could point me in the right direction to creating this myself as having searched your blog i dont see anything similar.
I would describe it as a Bar chart with currencies on the Y axes and 2 vertical axes (start date & end date) in seperate columns to chart the sum of values between two dates.
Does this sound possible?
Many thanks in advance
Jon Peltier says
Neil –
Do you have a link to such a chart?
Neil Clarke says
Jon,
here is an image i have uploaded.
http://imageshack.us/photo/my-images/832/neilchartexample.png/
im sure this explains the issue better than i could.
Your employment chart was the closest one i found visually at least.
https://peltiertech.com/us-employment-slump-chart-how-to/
Neil Clarke says
Hi Jon,
I don’t want to bug you but i am keen to know if what i am trying to achieve is possible.
Appreciate your comments
Regards
Neil
Jon Peltier says
Neil –
Try the bar version of this, but use horizontal bars:
https://peltiertech.com/Excel/Charts/StackedCharts.html
Neil Clarke says
Hello Jon,
That will indeed do the job. Thanks very much for your time.
Happy New Year to you.
Neil