Creating a Clustered Stacked Chart in Excel – Excel 2013

Learn how to create a combined Stacked, Clustered Chart in Excel. One of the trickier charts to get to create in Excel – but is certainly possible.

Stacked charts are great for showing data where you want to see segmentation, and clustered charts are great for showing categorised relative performance.

But what if you want to combine the two, and show a stacked clustered chart in Excel?

Thankfully, this advanced chart type became a little easier with Excel 2013 using the ‘Custom Combination’ chart type.

Let’s say we want to monitor Train departure Times on an On Time / Late basis versus Last Year, but split by Region. We might have the following data…

The trick, as with the other Chart Examples I’ve done, is to effectively ‘layer’ multiple charts on top of each other. So the first thing we need to do is rearrange our data to allow this.

As you can see, I staggered the data for each region with the This Year and Last Year entries on a separate line – and an empty row between each.

Now we can start creating our chart. Select the data range we just created – range A7:E16.

Head to the INSERT tab and select the Column Chart drop down menu.

Select ‘More Column Charts’ to bring up the Insert Chart window. On the Left Hand Side, select ‘Combo’ option.

Once in the Combo menu, select ‘Custom Combination option at the top of the window.

At the bottom of the window, you should see your four headings, and to the right, you can select the chart type for that heading.

Change them all to stacked Column.

Then select OK

You should have the beginnings for your clustered stacked chart!

This is a good start. But the Axis isn’t quite how we want it – it’s aligned to the left column, rather than centred on the two clustered columns.

To get the right axis labels, we need to create a new ‘dummy’ series of data.

Start by creating a new field in your original dataset named ‘Axis’ with values of ‘0’

We can now add this to our chart. Right click on your chart and choose ‘Select Data’

On the ‘Select Data Source’ screen, click ‘Add’

In the Edit Series box, select your ‘Axis’ title under Series Name, and your columns of Zeros for your Series Values…

Click OK, and your ‘Axis’ entry should appear in the ‘Select Data Source’ screen. On the right, select ‘Edit’

In the Axis Labels box select your three axis labels

Select OK, then OK again to close the winow.

Your chart should look something like this…

Which still isn’t quite what we want. Right click in the chart, and select ‘Change Chart Type’. Again, go to Combo à Custom Combination. If any of the fields has changed to anything other than Clustered Column, change them back to Clustered Column, except the ‘Axis’ field. Leave this / change this to Line.

Then, on the right, tick the box for ‘Secondary Axis’ against the ‘Axis’ field.

Then select ‘OK’. Your chart probably looks even more of a mess by now – don’t panic though!

We now need to adjust which axes are visible.

Selecting the chart, select the ‘DESIGN’ tab and locate the ‘Add Chart Element’ button.

Click it, then select ‘Axes’ and toggle the 4 options until you have turned ‘on’ Primary Horizontal, Primary Vertical, Secondary Horizontal and turn off Secondary Vertical

Now your chart should start looking at a little better.

However, there is a zeroed line chart along the bottom from our 0-value axis.

Right click in the line, and select ‘Format Data Series’. On the right, select ‘Fill and Line’ menu.

Under ‘Line’ select ‘No Line’. This should remove the blue line from the chart.

Now we can focus on getting rid of the lower (primary) horizontal axis. Select it by left-clicking on it (You should see a box around it confirming the selection). On the right, select the ‘Axis Options’ menu.

Expand the ‘Labels’ sub-menu. Set both ‘Label Position’ to None. Whilst we’re here, go into the ‘Tick Marks’ sub menu and set both ‘Major Type’ and ‘Minor Type’ to ‘None’

Things should be looking pretty good now.

You probably want the axis titles at the bottom though – but we can move this. Select the secondary axis labels (again, so there is a box around them). On the right, again navigate to the Axis OptionsàLabels sub menu. Change ‘Label Position’ to ‘Low’.

Again, go into the Tick Marks sub-menu and change both types to ‘None’

Ok, we’re getting there now. Just a few more bits to tidy up!

We need to get rid of the ‘Axis’ Legend item. That’s easy – left click it a couple of times until it is selected on its own.

Press ‘Delete’ – it’s gone. I’m also going to move mine to the right (Left click so the whole legend is selected. Legend OptionsàPositionàRight) and just resize my chart, give it a title etc.

Now, we probably want to ‘group’ them a bit more clearly. Left click one of the main columns of data on the chart, and you should get the ‘Format Series’ menu on the right.

Under ‘Series Options’ you should have a ‘Gap Width’ option.

Change this value to Zero, or close to Zero (I find anything under 25% usually works well – but play around and find something you like the look of)

I find it useful to re-colour the columns to make it clearer to read. The red/blue works well – blue for on-time, red for late. So I want to replicate this for the Last Year values, but in a different shade.

Select the one of Purple (Late LY) values. On the right, select ‘Fill & Line’ and then the ‘Fill’ sub-menu. Change the ‘Color’ option accordingly – I chose ‘Red, Accent 2, Lighter 40%’. You should see that the Radio button above has moved from Automatic to Solid Fill. This is fine.

Now select the green / On Time LY series. Again, set the fill colour as you like – I chose ‘Blue, Accent 1, Lighter 40%’.

And that’s really about it. As you will have gathered, getting the data onto the chart area is the easy part – it’s forcing the various axis labels to fall in line correctly which is a pain – and there is more than one approach you can take to achieving this, sometimes with marginally different results.

Not the easiest chart to produce – and it’s easier in 2013 than I recall it being in earlier versions (I will try and do a follow-up at some point – but it’s conceptually the same process, but of course options will be in different places). Powerful and useful charts none the less.

David

4 comments