Spacing of columns in stacked column chart

adambc

Board Regular
Joined
Jan 13, 2020
Messages
155
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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 ...
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Can you post your data table and an image of the graph?
 

adambc

Board Regular
Joined
Jan 13, 2020
Messages
155
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
@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

Board Regular
Joined
Jan 13, 2020
Messages
155
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
@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

Board Regular
Joined
Jan 13, 2020
Messages
155
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

OK, let's try this ...

My Table ...

1606929072048.png


And my Chart ...

1606929101076.png


Hope that works?

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

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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.
 
Solution

adambc

Board Regular
Joined
Jan 13, 2020
Messages
155
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

@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

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

ClusterStackTieredAxis1.png


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

ClusterStackTieredAxis2.png
 

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,585
Members
414,460
Latest member
uctc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top