New Series: You Asked For It

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:

stalagmite chart data

The chart begins its life as a simple clustered column chart. I’ve changed the gap width to 100.

stalagmite chart - clustered column precursor

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.

stalagmite chart - primary and secondary columns

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.

stalagmite chart - my preferred column chart

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

stalagmite chart - hang the secondary series

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.

stalagmite chart - switch the secondary Y axis

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.

stalagmite chart - hide the secondary category labels

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.

stalagmite chart - finished

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.

Peltier Tech Chart Utility

Comments

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

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

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

  4. David – Plus I get to show off!

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

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

  7. Thanks Jon, I truly owe more than a cup of coffee, learnt a lot thru your tutorials.

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

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

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

  11. Sorry, Daniel. That’s the way it works. However, you can work around this limitation using the technique in Individually Formatted Dual Category Labels.

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

  13. Neil –

    Do you have a link to such a chart?

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

    http://peltiertech.com/WordPress/us-employment-slump-chart-how-to/

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

  16. Neil –

    Try the bar version of this, but use horizontal bars:

    http://peltiertech.com/Excel/Charts/StackedCharts.html

  17. Hello Jon,

    That will indeed do the job. Thanks very much for your time.

    Happy New Year to you.

    Neil

Subscribe without commenting

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites