Spacing of columns in stacked column chart

[adambc]

I have a Pivot Table with Year/Month/Status (Open/Closed) as nested categories, Dept (A/B) as series and Count of Records as Value ...

This drives a stacked column chart, which meets all requirements except ...

I want to bring the Open/Closed columns closer together within a Month, but then increase the space between Months ...

Have played with everything I can find/think of (Series Overlap, Gap Width, etc) without any joy ...

Any ideas?

Many thanks ...

 

[sijpie]

Can you post your data table and an image of the graph?

 

[adambc]

@sijpie

I will do, but it will have to be in the next couple of days - I need to populate both with test data (the data in the table/chart I'm working with is confidential) ...

Thanks ...

 

[adambc]

@sijpie

See attached ... HOW DO I ATTACH/UPLOAD A FILE?

I would like to be able to format the chart with two things ...

1. the Open and Closed bars in different colours

2. the months spaced from each other

Are either of these possible?

Many thanks ...

 

[adambc]

OK, let's try this ...

My Table ...

And my Chart ...

Hope that works?

Many thanks for any help you can give me ...

 

[sijpie]

OK, I see where the problem lies: it is easy to cluster bar charts or to create stacked charts, but no simple solutions for clustering stacked charts. Now, if you ever need to know how to make special charts, then go to Jon Peltier's website. He will explain how to get the most unusual charts. You can do it by hand or buy hit toolkit.

There is a page for exactly what you want: the clustered stacked chart: Clustered and Stacked Column and Bar Charts - Peltier Tech Blog

Unfortunately it will involve some work from you, because your data comes from a pivot table. So you will need to copy the table with links and then move the data about as he explains to get the clustering.

 

[adambc]

@sijpie

Thanks for the link to Jon’s website - I had already found this and tried to adapt his step-by-step instructions to what I’m trying to achieve, but failed!

I won’t be able to until mid next week, but I will have another go and let you know the outcome?

In the meantime, many thanks for your help, it’s much appreciated.

 

[Jon Peltier]

My commercial software doesn't even quite do what you want, but you can do it by hand without much trouble.

You need to arrange your data as shown. Four rows per month, with rows for Open and Closed sandwiched between blank rows. The cells filled in with yellow are not blank but contain a space character (this makes the tiered axis labels work), the other cells that look blank are really blank, nothing in them, not even a formula that returns "".

Select the entire range and insert a stacked column chart. Make the gap width something small, like 25%. Format the horizontal axis, specify the interval between labels equal to 1.

If you want different shading between open and closed, add a couple columns and stagger the data further:

 

[sijpie]

Always something to learn! Thanks Jon