Clustered-Stacked Bar Charts
by Jon Peltier
Monday, October 13th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
|
I have created the Peltier Tech Cluster Stack Chart Utility to create such charts automatically from raw data. This utility, a standard Excel add-in, lays out data in the required layout, then constructs a chart with the right combination of chart types. This is a commercial product, tested on hundreds of machines in a wide variety of configurations, which saves time and aggravation. The Peltier Tech Cluster Stack Utility creates charts in either horizontal or vertical orientation. Please visit the Peltier Tech Cluster Stack Chart Utility page or the Peltier Tech Cluster Stack Chart Utility Documentation page for more information. |
This is a companion article to Clustered-Stacked Column Charts.
Excel offers clustered bar charts and stacked bar charts among its standard options.
A common request is to make a bar chart where the bars are clustered together, while one or more of the clustered bars are divided into sections stacked on top of each other. This isn’t built into Excel, but by rearranging the worksheet data, it can be constructed readily.
Suppose we have the simple data below, with four series of data over three months. One series is to be by itself in the top bar of the cluster, and three series stacked on the bottom.
We can select this data and create a clustered bar chart
or a stacked bar chart
But however we try to arrange series, we cannot achieve a clustered-stacked bar chart.
If we keep our wits about us, we can envision a worksheet arrangement that will give us what we want. We need a gap, one series stacked in the first filled slot, three series stacked in the second slot, then another gap, and the pattern repeats. So this is really a stacked chart. Where we have gaps, the series have zero values; where one series is alone, the others have zero values; where the three series are stacked, the other has a zero. This new arrangement is shown below. Gaps in the data occur at columns B, E, H, and K. The left column of each cluster has data in columns C, F, and I; the right column in D, G, and J. For clarity in this exercise, I’ve duplicated the column letters in the category labels range (B7:K7, tan). The top left cell (A7) is blank.
Select this range and create a stacked bar chart. It’s halfway done, we just need some formatting. To get the categories in top-down order, format the category (X) axis scale so that the categories are in reverse order and the value (Y) axis crosses at the maximum category.
Change the gap width of any series to zero, and it looks almost done.
The axis labels aren’t right, though. We need the months centered between existing labels, for example, “Alpha” between the current positions of “C” and “D”. This simple data works; note its arrangement parallels the original data. I’ve used 10 for the values so the bars show up in the charts. When I finish I’ll change the values to zero.
Here is what the chart’s axis should look like when we’re done:
Copy the data in F1:I2, select the chart, and use Paste Special to add the data as a new series, with data in rows, category labels in the first row (we’ll have to reapply this later), and series names in the first column. The data is stacked on top of the first three categories.
What we need to do is put the axis series on the primary axis and the stacked data series onto the secondary axis (if we reverse this, we will have problems displaying the Alpha-Beta-Gamma axis labels). Select one of the series, and move it to the secondary axis. I usually move the lowest valued series first (Right 3 in this example) so that the secondary axis series don’t obscure the primary series I need to select and move next.
In turn, select and move all of the Left and Right series to the secondary axis. I’ve changed the gap width of the Axis series to 50% (from zero) to make it easier to follow. The Left and Right series are clustered, not stacked, because that’s Excel’s default bar chart variation.
Select one of the secondary axis series, and change its chart type to stacked. All bar series on that axis will follow suit. Also, change the gap width of a secondary axis series to zero.
Excel has given us a secondary value (Y) axis, but we only have the primary category (X) axis. Using Chart menu > Chart Options > Axes tab, or in Excel 2007, the Chart Tools > Layout tab, to add the secondary axis to the chart. At first, we don’t have the appropriate names as our labels, because Excel applied the existing labels (B through K) to all series.
Edit the series source data or the series formula for the Axis series, so that the months in G1:I1 are used for category labels.
Almost done. The month names are not centered correctly next to the clustered bars. Format the scale of the secondary category axis (B through K at the right of the chart) so that the value axis does not cross between categories.
Perfectly aligned. Now format the secondary category axis to display no tick labels and no tick marks.
Use Chart menu > Chart Options > Axes tab or Ribbon > Chart Tools > Layout tab to remove the secondary value (Y) axis (or select it and press Delete). Format the Axis series on the secondary axis so that it has no fill. Change the Axis series values in G2:I2 from 10 to zero. Select the legend, then select the Axis legend entry, and hide it by pressing Delete.
Not too complicated, and it shows exactly what we want.
Related Posts:
- Clustered-Stacked Column Charts
- Clustered-Stacked Column Charts with Vertical Separators
- Clustered-Stacked-Column Combo Chart With Lines
- Clustered-Stacked Column Chart Utility
- Clustered-Stacked Column Chart with Target Line
- Conditional Stacked Clustered Column Chart with Targets
- Overlapped Bar Chart – Thinner Bars in Front
- Peltier Goes Bar Hopping
- Order of Legend Entries in Excel Charts
- Label Totals on Stacked Column Charts
Posted: Monday, October 13th, 2008 under Combination Charts.
Comments: 28
Comments
Comment from derek
Time: Monday, October 13, 2008, 2:06 pm
Allow me to shake my fist at the sky a bit and rage that none of this would be necessary if Excel just had a button to turn everything round X for Y, just as if it were viewed with head a-tilt, except for keeping labels horizontal and reconforming to the aspect ratio of the graph area. Dot plots, too, would become trivial instead of impressive exercises in Excel wizardry.
Curse you, Microsoft! (shakes fist)
Comment from Chris
Time: Thursday, October 30, 2008, 9:56 am
How might one go about adding a line chart onto the above clustered-stacked bar chart? I am trying to overlay data onto a stacked bar chart via several lines attempting to use the method outlined in “Clustered-Stacked-Column Combo Chart With Lines” discussion.
Thanks,
Chris
Comment from Jon Peltier
Time: Thursday, October 30, 2008, 10:47 am
Chris -
You have to add it as an XY chart series, but Excel won’t let you, at least not easily. Since you have bar series on both primary and secondary axes, and Excel doesn’t let a bar chart series share its axis group with any other type of series, there are no axis groups left to accommodate the XY series. You have to backtrack, and remove the bar series used for the vertical axis labels. With the bar series on the primary axis, you can put one or more XY series onto the secondary series. So add two, one for the line you want to show, and the other to serve as placeholders for the vertical axis labels. The two techniques you need are covered in Vertical Category Axis and Bar-Line Combination Chart, and when I have a moment I’ll write a new post that brings it together.
Comment from Nathan
Time: Monday, June 15, 2009, 11:55 pm
This article was very helpful to me.
Thank you very much for a wonderful article.
I noticed a small problem with this method.
The tag names(Tool tip text) of the series are not correctly displayed.
After finishing all steps, when I take mouse over Left series of Alpha category, the text displayed is
Series:Left Category:Beta
Value:60
but, it has to show
Series:Left Category:Alpha
Value:60
Please tell me how can I do this?
Thanking you
Comment from Nathan
Time: Tuesday, June 16, 2009, 2:37 am
I got the solution for my previous comment.
Give another range for secondary axis label, that will take care of the rest.
For the above example, the range has to be(put one in each cell)
Alpha Alpha Alpha Beta Beta Beta Gamma Gamma Gamma
Thanks for your time
Comment from Jon Peltier
Time: Tuesday, June 16, 2009, 5:23 am
Nathan -
This works the same as using B, C, D, etc., which I used in the example. The labels are hidden finally, so exactly what they say does not matter.
Comment from Nathan
Time: Tuesday, June 16, 2009, 6:05 am
Yes.
But I needed to show them to the user. So, I used that method
Comment from Jon Peltier
Time: Tuesday, June 16, 2009, 8:37 am
Sometimes you have to choose between doing it right, and doing it right now.
Comment from Nathan
Time: Wednesday, June 17, 2009, 1:20 am
Thank you very much John.
Is it possible to align the Category axis TickLabels such as xlRight ?
I tried with the code
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels
.Alignment = xlRight
.Offset = 0
.ReadingOrder = xlRTL
.Orientation = xlHorizontal
End With
but it is not working.
Please tell how to do that?
Comment from Jon Peltier
Time: Wednesday, June 17, 2009, 6:09 am
Nathan –
Two points:
1. The labels are already right-aligned, aren’t they?
2. I recall puzzling over why this alignment property didn’t have any effect anyway.
Comment from Nathan
Time: Wednesday, June 17, 2009, 10:16 pm
Yes, they are right aligned.
But I slightly changed the text.
I modified the text using
Alpha = Alpha & vbCrLf & 10
With this, the tick table text is displayed as
Alpha
10
But I wanted to show it as
Alpha
10
Any ideas?
Comment from Jon Peltier
Time: Thursday, June 18, 2009, 5:46 am
Remove the built-in axis tick labels, add a hidden series that displays the tick labels you want as data labels. This is described in Individually Formatted Category Axis Labels | PTS Blog. Don’t change the collors of the labels, but right align them.
Comment from Ken Stern
Time: Tuesday, September 1, 2009, 5:24 pm
Jon -
Love the site as always and this is a great tutorial.
Following up on your comment to Chris above, I was unsuccessful in getting the “target line” to add to a Clustered-Stacked Bar chart. I get the “Some chart types cannot be combined with other types. Select a different chart type” error, and all hell breaks loose when I try to remove the dummy axis series. I’ve always (not just this time) had some trouble with adding those extra series of XY points, so this may just be another instance of that problem, but it seems like this is something non-trivial to be done here…
Thanks for any suggestions-
Comment from Jon Peltier
Time: Tuesday, September 1, 2009, 9:52 pm
Ken -
Don’t even add the bar chart series with the alpha-beta-gamma labels. Instead of these bars, you will need to build a set of secondary X and Y axes that will work with the XY series. The X axis will have the same scale as the Y (horizontal) axis of the bar chart, and the Y axis will simply scale from 0 to 1.
You need to change the data range for the axis series:
G1:I1 – keep category labels
G2:I2 – fill with zero values
G3:I3 – insert a fraction corresponding to the height of the label**
**For example, “alpha” is the third label from the bottom (let i=3), and there are a total of 3 labels (N=3). The fraction is
(i-0.5)/N
So the fractions for alpha, beta, and gamma are 0.83333, 0.5, and 0.16667.
Clear F2 and put the “axis” label into F3.
Copy F2:I3, select the chart, paste special to add the data as a new series, data in rows, categories in first row, series names in first column. It is added as a bar chart series, but when you change it to an XY type, Excel adds the secondary X and Y axes, and you need to rescale tham as I’ve described above. Repeat the copy-paste special with the data to add the target line as described in Add a Vertical Line to a Column or Line Chart: Series Method or in Add a Vertical Line to a Column or Line Chart: Error Bar Method.
Comment from Brooke Denny
Time: Thursday, January 7, 2010, 9:16 am
Jon,
Thanks for this very useful article.
I am using Excel 2007 and it seems to create all my graphs ‘upside down’. When I follow the first step of your tutorial by selecting the data range & inserting a stacked bar chart, the Y axis is labelled with K at the top and B at the bottom whereas your chart is the other way up.
I have checked the ‘Horizontal (Category) Axis Labels’ in the chart data and they run from B – K as I would expect to see. Do you know how to reverse the order they appear in on the chart?
Thanks for any suggestions.
Comment from Jon Peltier
Time: Thursday, January 7, 2010, 5:17 pm
1. In a horizontal bar chart, the vertical axis is the category axis, therefore the X axis.
2. The upside-down appearance is explained in Why Are My Excel Bar Chart Categories Backwards?
Comment from Erik Murk
Time: Wednesday, February 17, 2010, 3:50 am
I try to make a population piramid with the prime data in columns and the”comparison” e.a. national data as a line.
This is extremely easy with a bar-line chart apart from the fact that this is 90 degrees off from what i want; a vertical piramid..
Is this AT ALL possible in Excel?
Comment from Jon Peltier
Time: Wednesday, February 17, 2010, 8:21 am
Eric -
Perhaps you are looking for Clustered-Stacked-Column Combo Chart With Lines.
Comment from Erik Murk
Time: Thursday, February 18, 2010, 2:26 am
Hi Jon, thanks for your respons.
eh, yes and no;
That system is right and ive been experimenting on that route already,
but i im trying to make a population piramyd along that line and “the clustered stack column combo chart with lines” (quite a finger breaking term this:)
is 90% off. It should look something like chart 4 on this page
http://www.fin.gov.on.ca/en/economy/demographics/projections/
Comment from Jon Peltier
Time: Thursday, February 18, 2010, 8:38 am
Eric -
I see now what you are trying to plot. Check out my tutorial on Tornado Charts.
Comment from Erik Murk
Time: Thursday, February 18, 2010, 8:58 am
Hi Jon,
almost there:)
I am able to make a chart like “tornado1″ so that hurdle is already taken.
What i am aiming for is a combination of “Tornado1″ and a line drawing of a simular shape, so, two vertical lines in the shape of a “christmas” tree.
The Tornado chart would present the averages of say, New York,
and the line drawing (the christmas tree) would present the averages in the USA
Again, I can produce that effortlessly with a bar line combo butthat whole thing should be tilted 90% to fit my goal…:)
Comment from Jon Peltier
Time: Thursday, February 18, 2010, 8:34 pm
Eric -
Now you have to plot the lines using XY series, where X is the horizontal value, and Y is the vertical value. These are plotted on the secondary axes, which are scaled with respect to the primary axes (on which the bars are plotted) so they represent the data appropriately. For some guidance, read how to Build a Bar-Line Combination Chart.
Comment from Erik Murk
Time: Monday, February 22, 2010, 7:02 am
Hi Jon,
I tried it acording to the “how to” page, but the result makes no sense;
since the data on the bars and the data on the lines arent that far apart
you’d expect them to show a “synchonised” pattern.
In my case where i use horozontal bars, the line also runs kinda horizontal
so i expect that im missing something (somewhere:)
Comment from Jon Peltier
Time: Monday, February 22, 2010, 7:24 am
Erik -
You need to take measures to align the lines (actually XY series) with the bars, which I describe in Build a Bar-Line Combination Chart.
Comment from Erik Murk
Time: Monday, February 22, 2010, 7:29 am
To prevent things getting lost in translation (im from the netherlands)
the XY series which you refered to earlier;
is that the same as a scatter chart?
Comment from Erik Murk
Time: Monday, February 22, 2010, 8:08 am
Lightning strikes..!
I have to plot first and make the actual chart with an extra column
accordingly?
Comment from Jon Peltier
Time: Monday, February 22, 2010, 9:02 am
Erik -
1. Yes, the chart type is called “XY (Scatter)” in English versions of Excel, and I tend to use the simpler name “XY”.
2. You have to plot the data first, add more data (i.e., for the XY series) if necessary, then change the chart type and axis of the series you need to change from bar to XY.
Comment from Erik Murk
Time: Monday, February 22, 2010, 9:51 am
ah…
That explains a lot:)
So instead of “charting” the actual data from your tables you calculate
where you want your “points” for the XY charts, put these in a column
and THEN you chart?
Smart thinking:)
What escapes me is why Excel CAn do a “bar-line combo” with standing bars
but can’t do the same with lying bars?
Anyway, thanks!
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.
Read the PTS Blog Comment Policy.