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.

Possibly Related Posts:

• Clustered-Stacked Column Charts with Vertical Separators
• Clustered-Stacked Column Charts
• Clustered-Stacked-Column Combo Chart With Lines
• US Employment Slump Chart - How To
• Overlapped Bar Chart - Thinner Bars in Front
• Peltier Goes Bar Hopping
• Area Chart - Invert if Negative
• Stacked Area Chart Challenge