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:
You want to create this Excel Clustered Column Chart without gaps for each series like this:
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
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:
| 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,
We need to arrange the data in the following layout:
Don’t worry, Excel will do it for you, but here is what it is setting in the Axis options:
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:
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
Then go to the Insert Ribbon and choose the 2d Clustered Column Chart:
Your chart should now look like this:
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%:
Your final chart should now look like this:
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
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
