Create combination stacked / clustered charts in Excel

I was asked how to do this recently by a colleague, so as usual I decided to turn it into a blog!

A lot of the people I work with don’t have access to statistical software like JMP and minitab and have to rely on Excel.  For many, this limits their ability to show complex information in charts.  For example, say you want to create a combination stacked clustered bar chart like the one shown below:

Unfortunately it is not very simple in Excel.

How can you do it?  Well, let’s imagine that your have the data as shown below:

The first thing you need to do is to separate the data into lines with spaces in between.  To get a chart arranged as above we need to make the data look like this…  (I will explain columns F and G later).

Then we need to select B1:E14 and click on the insert tab, then select the small drop down arrow below column in the Charts section and then choose the second 2-D Column chart type.

You can then format your data series so that they look more coherent.  Left click on the first data series you wish to change then press Ctrl+1 to bring up the format data series dialog box.  Click on Fill, then solid fill and change the colour.  Then click on the next series and repeat until all of the series are correct.

Once the chart looks like the second one above, click on the series options tab.  In the gap width section, move the slider until it shows no gap or 0%.  The graph will update autolatically and you can press close.

We now need to adjust the axes.  This is where columns F and G are important.  First select cells G1:G5 then with these selected, hold down the CTRL key and then select cells F1:F5 and press CTRL+C to copy.

Left click on the graph and then click on the home tab.  Left click on the small down arrow below paste and choose paste special.

Ensure the options shown below are selected, then click on OK.

The chart will update as shown below.

Temporarily put a value in cell G2 e.g. 200, so that you can see the value in the chart.  Then right click on the value in the chart and select “Change Series Chart Type”.

Then choose the first of the line chart options and click on OK.

Change cell G1 back to blank.  Then click on the layout tab and ensure that the series “Axis” is shown in the current selection box.  If not select it using the drop down arrow.

Press CTRL+1 again to see the format series menu box and select secondary axis.  This will add a vertical axis on the right hand side of the chart.  Press Close.

Then click on the small drop down arrow of the Axes button in the Axes section.  Click on Secondary Horizontal Axis and Show Left to Right Axis.

This will add a second axis above the chart.

We now need to move the top axis to the bottom.  To do this, left click on the left vertical axis and click CLTRL+1.  Select the last check box in the Axis Options tab so that the Horizontal xis crosses at the maximum axis value.

Then click on the Right vertical axis and ensure that the Horizontal axis check box is showing automatic.

Then click on the top axis.  In the Axis options tab, at the bottom in the Position Axis section, choose the “On tick marks” check box.  this will adjust the graph so that there is only a half space before and after the columns.

Select the right hand side vertical axis, which is scaled from 0 to 1, and delete it.

Format the top horizontal axis, which is scaled from 1 to 13, and format it so it has no tick marks or tick labels, and no line type.

Finally, double click on the the Axis legend entry.  Press Delete.  Left click on the Axis line and / or markers and then format the Axis series to be invisible (no marker, no line).  Then press close.  You can also move the legend to the bottom of the chart etc if you want.

Stacked / Clustered Chart