Friday Challenge – Part 2 Removing Gaps from a Clustered Column Chart

Okay Excel Fans, in a previous challenge, we had a problem that we solved for a specific data set.  We wanted to remove white space in a clustered column chart.  If you don’t remember or are new to the blog, here is a link to the previous challenge and answer:


Is it Possible to make this Excel Column Chart?


Remove Excel Chart Whitespace from Empty or Zero Columns  (Part 1)

Now I promised that there would be a harder and more challenging Part 2.  And low and behold, here it is!


The Problem:

Clustered Column Chart – Removing Gaps for Zero Value Series

So this question has been asked many different ways, but I can’t find a workable solution. I have a clustered column chart that has, for example, 3 categories and 10 series. The third category has a value for each series, while the other two only have values in some of the series. The resulting graph leaves gaps/spaces for those series that don’t have values. Is there a way to resolve this to where those gaps/spaces are not there? I’ve tried populating the 0 value cells with =NA(), but that just removes the data labels.



The problem visually:

I can create this graph:image

But what I REALLY want is this graph:image


Here is your data set.  You can copy and paste this directly into your Excel Spreadsheet:
Excel 2012

1 Subject 1 Subject 2 Subject 3
2 Series 1 1 1
3 Series 2 1
4 Series 3 5 4 1
5 Series 4 2 9
6 Series 5 3
7 Series 6 2 3
8 Series 7 7 2
9 Series 8 6
10 Series 9 4
11 Series 10 5

Can you figure it out?  If so, please add a comment below with your email address so that I can contact you.  Then you can send me your solutions.  Good luck!!!!