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:

Challenge:

Is it Possible to make this Excel Column Chart?

Answer:

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.

image

 

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

ABCD
1Subject 1Subject 2Subject 3
2Series 111
3Series 21
4Series 3541
5Series 429
6Series 53
7Series 623
8Series 772
9Series 86
10Series 94
11Series 105

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!!!!





 

Steve=True





1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here