Excel offers clustered column charts and stacked column charts among its standard options.

A common request is for a clustered-stacked column chart where the columns are clustered together, while one or more of the clustered columns 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.

In Clustered-Stacked Column Charts I showed how to construct such a chart.

But now I’ve been asked to extend the combination to include a set of lines on the secondary vertical axis.

Suppose we have the simple data below, with four series of data over three months. One series is to be by itself in the left column of the cluster, and three series stacked on the right.

Following the rationale in Clustered-Stacked Column Charts, the data must be rearranged to the following format, so all of the columns will be positioned as desired.

Select this range and create a stacked column chart. It’s halfway done, we just need some formatting.

Change the gap width of any series to zero, and it looks almost done.

In Clustered-Stacked Column Charts, we used a dummy series to make the category axis labels look right. To add a set of lines which are cenetered on each cluster of columns, we will use the same approach. Here is the data for the line series. This simple data works; note its arrangement parallels the original data.

Here is what the chart’s axis should look like when we’re done:

Copy the data in F1:I3, 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 line series on the primary axis and the stacked column series onto the secondary axis (if we reverse this, we will have problems displaying the Jan-Feb-Mar axis labels). Select one of the series, and move it to the secondary axis. I usually move the lowest valued series first (B3 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 A and B column series to the secondary axis. The A and B series are clustered, not stacked, because that’s Excel’s default column chart variation.

Select one of the secondary axis series, and change its chart type to stacked. All column series on that axiss will follow suit. Also, change the gap width of a secondary axis column 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 month 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.

The stacked column Y axis (secondary) is along the right edge of the chart, and the line Y axis (primary) is along the left. We want to switch these axes. Format the secondary X axis (top) so that the Value Y Axis does not cross at the maximum category (uncheck the box). Then format the primary X axis (bottom) so that the Value Y Axis does cross at the maximum category (check the box).

Almost done. The month names are not centered correctly under the clustered columns. Format the scale of the secondary category axis (B through K at the top of the chart) so that the value axis does not cross between categories.

Perfectly aligned. Now convert one of the line series from a column to to a line style.

Repeat for the other line series.

Now format the secondary category axis (B to K along the top of the chart) to display no tick labels and no tick marks. Adjust the plot area width so the legend does not obscure the Y axis labels.

You may want to reposition the legend.

This chart illustrates a problem with secondary axes, which Stephen Few wrote about in Dual-Scaled Axes in Graphs-Are They Ever the Best Solution?, and which I followed up in Secondary Axes in Charts. The problem is twofold. First, having the primary and secondary series overlapping in the same chart space makes it unclear which series corresponds to which axes. Second, it is impossible to separate the primary and secondary series visually. For example, although the lines and columns are independent, the red line seems to match with the green column at the endpoints but with the blue column in the middle.

These problems can be avoided by using two separate charts, or if you’re a bit more adventurous, by creating a panel chart.

Arranging the chart that way without changing its height shortens the vertical scales, reducing resolution of the axes. The legend can be moved and the chart made taller and narrower.

This illustrates a problem with chart legends. First, they force the reader to move his eyes between the charted data and the legend. Second, in this case, the legend is completely out of order compared to the plotted points. It is usually better to use data labels right in the chart.

Possibly Related Posts:

• Clustered-Stacked Bar Charts
• Clustered-Stacked Column Charts
• Clustered-Stacked Column Charts with Vertical Separators
• US Employment Slump Chart - How To
• Stacked Area Chart Challenge
• Add a Target Line
• Integer Values on Line Chart Category Axis
• Area Chart - Invert if Negative