One of my most popular blog posts of all times is Clustered-Stacked Column Charts. This post has received the most visits and is the most heavily commented post I’ve written. One reader wants to know how to add vertical lines between the clusters. This is an adaptation of the technique in Add a Vertical Line to a Column or Line Chart: Error Bar Method. I’ll rebuild the clustered stacked chart, then add the lines.

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.

We can select this data and create a clustered column chart

or a stacked column chart

But however we try to arrange series, we cannot achieve a clustered-stacked 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 th 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 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.

The axis labels aren’t right, though. We need the months centered between existing labels, for example, “Jan” 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 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 (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 100% (from zero) to make it easier to follow. The Left and Right 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.

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

Adding Vertical Lines – The Easy Way

As Andy Pope pointed out in his comment, there’s a very easy way to add vertical lines between the main categories. Since the categories are on the primary X axis, we can add major gridlines to this axis. Right click on the chart, choose Chart Options, click on Gridlines, and check Major Gridlines under Category (X) Axis.

Adding Vertical Lines – The Hard Way

To add the vertical lines the hard way (as I originally posted), we first will add a series of points along the lower edge of the chart. This will be an XY type series, and we will apply vertical error bars. The data for this series is as follows. Jan is at 1, Feb is at 2, and Mar is at 3; we want points between these positions, so we need X values of 1.5 and 2.5. The Y values are zero so the points lie along the horizontal axis.

Copy the range K1:M2, select the chart, and use Paste Special to add the data as a new series, with data in rows, categories in the first row, and series names in the first column. The data is first added as another stacked column series, with zero height, so it’s not visible.

Select the Verticals series (perhaps by selecting a visible series and using the arrow keys to cycle among the series in the chart), and from the Chart menu, click on Chart Type, then select an XY type. I chose the type  with markers and lines just to be visible in this example.

If you look closely, you can see that the secondary category axis has changed, moving the outer clusters closer to the center. Select the secondary category axis (I used the Chart Element dropdown on the Chart toolbar), on the Format menu, choose Selected Axis, and on the Scale tab, uncheck the box for Value (Y) Axis Crosses Between Categories.

That’s better. Now double click the Verticals series, click on the Y Error Bars tab, click on the positive error bar icon, and enter a value of 70. If the Y axis has an automatic scale, it will change to 80, so it reaches higher than the error bars, so you will have to enter 70 as the axis maximum for the Y axis.

Now hide the Verticals series by formatting it with no markers and no lines. Select the legend, then select the Verticals legend entry, and press Delete.

Now you have the vertical separation between months of data. In fact, this is a panel chart.

Related Posts:

• Clustered-Stacked Column Chart with Target Line
• Clustered-Stacked-Column Combo Chart With Lines
• Clustered-Stacked Bar Charts
• Clustered-Stacked Column Charts
• US Employment Slump Chart – How To
• High-Low Line Alternatives in Excel Charts
• Horizontal Bands in the Background of an Excel 2007 Chart
• How to Build a Simple Panel Chart
• Order of Legend Entries in Excel Charts
• Area Chart With Gap