logo
  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Creating a Clustered Stacked Chart in Excel – Excel 2013

February 16, 2015 7 comments Article Advanced, Excel

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.

[Excel 2013]

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

Related

Tags: 2013, Charts, Excel, Formatting, Office

7 comments

  • montaż anten Gorzów March 16, 2015 at 7:09 am - Reply

    Cudowny post, humor i dystans! Plus ogĂłlnie bardzo fajny blog 🙂 GratulujÄ™! 🙂

  • j Evans November 24, 2015 at 3:15 pm - Reply

    Thanks. Managed to follow this and produce the chart I wanted.

  • Pon November 7, 2016 at 9:54 am - Reply

    Thank you so much for this. I couldn’t get the result by following other sites, but I did it with your thorough steps.

  • User August 24, 2017 at 6:25 pm - Reply

    Fantastic and really easy to follow!
    Thanks!

  • RedJessie August 9, 2018 at 12:19 am - Reply

    Very nice instructions. Thank you.
    Despite an hour of experimenting I’ve not been able to get the labels lined up with the columns. The X Axis is months and there are 2 stacked bars for each month (for year on year comparison). For the secondary line of 0’s I have to have 2 line spaces after each month label to force the 12 labels to have the same overall width as the 24 bars. I either end up with the label under the left column, or the right column for each month and I can’t get it centered between the 2. I’ve also tried putting spaces at the front of each label but that just forces the label down and not across. Any thoughts?

  • RedJessie August 9, 2018 at 1:03 am - Reply

    Never mind. I worked it out. Needed a blank row both before and after the data for each month, so with 2 blanks and 2 bars the labels stay centered.

  • BestBryon September 4, 2018 at 7:27 pm - Reply

    I see you don’t monetize your site, don’t waste your
    traffic, you can earn extra cash every month. You can use the best
    adsense alternative for any type of website (they approve all websites), for more
    details simply search in gooogle: boorfe’s tips monetize your website

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Using the IFS() and SWITCH() Functions to test for more than one condition in Excel 2016
Creating Virtual Tables in Power BI Using DAX
Showing Data Bars as a 'Proportion to Total' in Excel
Populating one Drop Down based on the Selection of Another in Excel – Without VBA
Format or Remove Borders from a Slicer or Timeline in Excel

Meta

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Categories

Pages

  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Copyright System Secrets 2022 - Theme by ThemeinProgress