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.

Possibly Related Posts:

• US Employment Slump Chart – How To
• Marimekko Replacement – 2 by 2 Panel
• Clustered-Stacked Bar Charts
• Clustered-Stacked-Column Combo Chart With Lines
• Plot Two Time Series With Different Dates
• Add Percentages on the Secondary Axis
• Stock Charts in Excel 2007
• Clustered-Stacked Column Charts with Vertical Separators
• Clustered-Stacked Column Charts
• Date Axis with Centered Years