Removing Gaps in an Excel Clustered Column or Bar Chart (Part 2)

Removing Gaps – Excel Clustered Column or Bar Chart (Part 2)

In an earlier Friday Challenge, I posted a problem where someone wanted to remove the whitespace from a given data set.  That one turned out a lot less complicated than I thought.   You can check it out here:

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

Then I found a harder Excel challenge and posted it last week.  I had several great attempts, but no one else found a way to solve this problem.  Here is the Excel chart issue again:

The Problem:
Clustered Column Chart – Removing Gaps for Zero Value Series
Take this data set:

A B C D
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

and instead of creating this Excel chart:image

You want to create this Excel Clustered Column Chart without gaps for each series like this:image





 

Challenge Submissions

We had a few great entries in the challenge.  One from Don and one from Pete.  Both worked well, but they were not able to overcome the individual coloring like the example above.  Both of their solutions had all the same color for all series data points.  You may be able to correct this issue by changing each data point color, but that is not dynamic and takes a lot of work.

Also, Peter had a WAY cool way to dynamically create his chart data set.  You should check it out.  I am sure that he will update it when he sees this solution.  Here are their Excel Spreadsheets chart challenge submissions (Note, you can download the completed/final Excel Dashboard Template spreadsheet free of charge at the bottom of the article):

Don’s looks great:

Dons-Challenge-Answer-2-7-14.xlsx


SPECIAL - SAVE 10% until July 20th. Use code EDT.


Pete’s has an AWESOME macro/VBA code that builds the chart data range:

Petes-Answer-Part-2-Removing-Gaps-from-a-Clustered-Column-Chart.xlsm

 (Updated file is available for Pete’s NEW version at the bottom of the article)

Now before we get to the solution to the challenge, I need to disclose that I was not smart enough to figure this out, but thought it should be shared amongst all the Excel fans out there.  Full credit for the Excel solution goes to the GREAT Andy Pope.  He is awesome when it comes to Excel charting and I hope to grow up to be just like him one day.

 





The Breakdown

1) Arrange the Chart Data

2) Create your Chart

3) Adjust the Gap Width and

 

Step-by-Step

1) Arrange the Chart Data

So there are no macros or VBA needed for this Excel solution.  You just need to arrange the data so that each related series is in the same column.  You need to arrange your data like this:


SPECIAL - SAVE 10% until July 20th. Use code EDT.


A B C D E F G H I J K L
1 Series 1 Series 2 Series 3 Series 4 Series 5 Series 6 Series 7 Series 8 Series 9 Series 10
2 Subject 1
3 Series 3 5
4 Series 4 2
5 Series 7 7
6
7 Subject 2
8 Series 1 1
9 Series 3 4
10 Series 6 2
11
12 Subject 3
13 Series 1 1
14 Series 2 1
15 Series 3 1
16 Series 4 9
17 Series 5 3
18 Series 6 3
19 Series 7 2
20 Series 8 6
21 Series 9 4
22 Series 10 5

 

First in order to create a the Horizontal Axis groupings,

image

We need to arrange the data in the following layout:image

Don’t worry, Excel will do it for you, but here is what it is setting in the Axis options:





image

You can learn more about Multi-level Category Labels here:

How-to Easily Create a Stacked Clustered Column Chart in Excel

Stacked bar chart with data from multiple columns – How would you do it?

Case Study Solution – Mom Needing Help on Science Fair Graphs/Charts

Then to line up the data points so that they are in the same column if they are representing the same series.  See below how the Series 3 data is only in column 3.  This will make sure that the colors of the data points are all the same:image

Also, the blank rows in row 6, 7, 11 and 12 are optional, but allow for the space padding between the Subjects.

2) Create your Chart

Now that you have created your chart data layout, you have done the bulk of the work.  To create your chart, all you have to do is highlight the range A2:L22


SPECIAL - SAVE 10% until July 20th. Use code EDT.


image

Then go to the Insert Ribbon and choose the 2d Clustered Column Chart:image

Your chart should now look like this:image

3) Adjust the Gap Width and Series Overlap

Select any series in your chart and press CTRL+1 then choose the series options and change your Series overlap to 100% and your Gap Width to 0%:image

Your final chart should now look like this:image





Video Demonstration

Watch this technique to remove gaps in your column chart here:

Free Excel Dashboard Chart Template File

Download the Excel challenge solution file here:

Clustered-Column-Chart-Removing-Gaps-for-Zero-Value-Series.xlsx

 Pete’s Updated Macro File that Creates the Layout for you!!!

Friday-Challenge-Part-2-Removing-Gaps-from-a-Clustered-Column-Chart-Rev-2.xlsm


SPECIAL - SAVE 10% until July 20th. Use code EDT.


Thanks Pete and Don for your Submissions and thank you Andy Pope for teaching me so much and also for figuring out this chart gap.

Steve=True





21 COMMENTS

  1. I have sent in an updated version of my solution that combines the layout that you suggest and the dynamic updating that my original solution has.

    This has proven to be a worthy challenge. 🙂

    • Thanks Pete! I love the macro that creates the layout for you. I have added a link to it at the bottom of the Excel tutorial. Glad it was fun!

    • Thanks Sarah. I don’t have one yet, but I can put it on my list. Do you have sample data? I am curious because with a stacked clustered column chart, would you put errors on both columns of a stack or just one of the columns? Thanks. Steve=True

  2. Tried this 3 times in XL2010 but it turned out a pig’s breakfast each time. Then manually selected the data and it worked perfect. Great outcome, thanks.
    Eg. Series 1 = data from C2:C22
    Series 2 = data for D2:D22, etc.
    X-values in series 1 = A2:B22.

  3. Can i change the Series to say something different or remove it completely this is great and just to solution i needed but i don’t need the series headings.

  4. Wow, this is a really cool technique.
    Unfortunately, the vast majority of users I know (including IT people) would tell me that VBA is not an option for them which often means missing out on good stuff like Steve’s macro. So I tried reproducing the result using a pivot table, a really cool Excel feature that has been covered on many pages here at EDT. It works just great!
    Allow me to share:
    1. Analyse your data set (If you have none, grab the data from the table at the beginning of this article and follow along!). In order to use the pivot table feature, you will want to have your data in the list format, in case of this article’s sample data like this:
    Series Subject Value
    Series 1 Subject 2 1
    Series 1 Subject 3 1
    Series 2 Subject 3 1
    Series 3 Subject 1 5

    If your data is in the list format, go to step 2. Otherwise, your data may be in a format like the data set at the beginning of this article. That is, the possible values of one field (“Subject”) are not in a column but spread out along the horizontal axis like the “Subject 1||Subject 2||Subject 3”. This format can be thought of as the result of pivoting the Subject column to the the horizontal axis. So in order to obtain a list-format data set, we need to “unpivot” the subject field back into a column. And that is exactly what this great article is about.
    https://www.exceldashboardtemplates.com/how-to-convert-an-existing-excel-data-set-to-a-pivot-table-format/
    If you followed the steps at the link destination, your resulting list-formatted data set will be an Excel table – auto-formatted and with drop-down menus next to the field titles. If your original data set had empty cells like this article’s sample data, the list will contain some records with an empty Value field. You can then use the filter in the drop-down menu to filter out all empty-Value records and copy the filter result over to a new sheet.
    2. In order to have spaces between the main category (“Subject”) clusters, we need to add to our list as many dummy rows with Series being a single space and Subject being incrementing number of space (one, two, three). We use spaces because we need something invisible and the number of spaces – and thereby the value – must be different because otherwise the pivot table would aggregate those multiple records into one.
    3. Copy the subcategory column (“Series”) and paste it to the right of your set. I explain why later.
    4. Select some cell in your data list and press Alt > N > V > C. Follow the wizard to create a pivot chart. You will have the pivot table panel on the right of the Excel window. Now drag the main category (“Subject”) from the field list to the ROWs box, then drag the subcategory (“Series”) below that. Since you copied the “Series” column in your data, you can drag the corresponding field to the COLUMNS box and lastly the “Value” field to the VALUES box. And boom! You can already see the pivot chart that looks pretty much like the desired result. Since we added those rows with spaces to the data list, those show up at the top of the vertical axis. Hover your mouse pointer over one of those at a time till the pointer becomes a cross with arrow tips and drag that space “Subject” between the actual subjects to create the spacing.
    5. If all the bars have the same height now – 1 -, open the drop-down menu from the arrow next to the “Value” item in the VALUES box, select field settings and change the aggregation mode from count to sum.
    If someone at EDT likes this trick, maybe they can make a video about it. It looks like a lot to do but is actually more easily done than explained and contains a bunch of useful techniques.

  5. When I try this, I get lines in between each Series Label in the chart and a slightly longer line separating the Subjects. In format table options I select no line but unfortunately gets rid of each line (including between subjects). Any way to get rid of the lines between Series and not the Subjects? Thanks

  6. Hi there, such a cool tutorial, it helped me sort out my problem too. But now I am stuck again because I need to add error bars to my chart and the way I usually do it (creating a table with inputed error values as the custom value for the error) doesn’t work…I really need help with this. Many thanks in advance! Happy new year everyone!

LEAVE A REPLY

Please enter your comment!
Please enter your name here