Column – Stack combination chart in Excel

Not very common, but sometimes useful, this chart is made up of a series of column pairs, where the one of them is a simple column and the other one is broken down in segments.

Last time I used it to show budgeted project expenditures versus actuals, month by month. The right column, actuals, was broken down to show capital investment. The chart can also be created so the “entire” column is to the right and the “broken” one is to the left.

Here is the cookbok to make the chart:

• Create 4 series, horizontally as shown below: broken (bottom part) and broken (top part), entire and dummy. I’ll refer to these series as BB,BT,E and D respectively.
  
• Fill your data as needed. On the D series, put some value on the first cell – this is just to allow you to select the series and later on you will delete this value. Make this value close to the average of the rest of the data, so you can handle the segment comfortably.
• Select all five lines of the data: titles, the three series for the columns and your D series. Create a chart, choose column type, and stacked column on the subtype. Make sure series in columns is the selected option. Finish the chart wizard.
• Right click on the segment for the D series on the first column and move it to the secondary axis: Choose “Format data series” and on the “Axis” tab choose “Secondary Axis”.
• Do the same with the data corresponding to the E series. Before closing the “Format data series” dialog, go to “Options” and choose overlap -100, gap width 80. This setting will apply to both the D series and the E series.
• In the “Series order” tab you can choose if your E series will go to the left or to the right by moving it up or down
• Go to “Format series”, now for the BT series. overlap should be 100 and gap 320.
• Double-click the right axis to bring the “Format Axis” dialog. Choose the maximum to be equal to that of the left axis. Even if Excel chose by default the same value for both, make sure the checkbox close to maximum is unchecked. You want to make sure manually your axis are synchronized to avoid showing misleading information. By default, Excel charts start at 0, but if you change this, you must do it in both axis. While you are at this dialog, go to the “Patterns” tab and choose “None” in “Thick mark labels”.
• Delete the dummy value, the dummy label on the chart and format the chart to your liking

Enjoy!

Related posts:

1. Tornado Charts in Excel 2007 Update This is an update to my previous post on tornado...
2. Extreme Makeover: Chart Edition – July 19 As every week I end up receiving at lest one...