Many users like to create a chart that Excel doesn’t have as a chart type. It is a combination of a clustered column and a stacked column. There are tutorials out that that are somewhat complicated, but you can check out an easier way on this article:
How-to Easily Create a Stacked Clustered Column Chart in Excel
The cool thing was when I actually saw a user need to use it and they wanted to know how to do this:
How-to Add Centered Labels Above an Excel Clustered Stacked Column Chart
Then I recently saw a question wanting one more tweak to the model and this was our last Excel challenge. In a nut shell, using this data set:
A | B | C | D | E | |
---|---|---|---|---|---|
1 | January | February | |||
2 | Actuals | Plan | Actuals | Plan | |
3 | group A | 250.48 | 300.58 | 187.15 | 155.96 |
4 | group B | 306.00 | 367.20 | 382.51 | 318.76 |
5 | group C | 315.00 | 378.00 | 425.38 | 354.48 |
6 | TOTAL | 871.48 | 1,045.78 | 995.04 | 829.20 |
Instead of creating this chart, how can we shrink the gaps in the stacked columns like this:
The Breakdown
1) Create Chart Data Range
2) Unmerge Cells January and February
3) Move January and February one cell left
4) Create Stacked Column Chart
5) Choose Gap Width of 10%
6) Add Space in Cell I3 to correct tick marks
Step-by-Step
1) Create Chart Data Range
This is our data set, but it won’t work for what we need.
A | B | C | D | E | |
---|---|---|---|---|---|
1 | January | February | |||
2 | Actuals | Plan | Actuals | Plan | |
3 | group A | 250.48 | 300.58 | 187.15 | 155.96 |
4 | group B | 306.00 | 367.20 | 382.51 | 318.76 |
5 | group C | 315.00 | 378.00 | 425.38 | 354.48 |
6 | TOTAL | 871.48 | 1,045.78 | 995.04 | 829.20 |
We need to add padding cells. One before January column, two before the February column.
Your data set will look like this:
A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|
1 | January | February | |||||||
2 | Actuals | Plan | Actuals | Plan | |||||
3 | group A | 250.48 | 300.58 | 187.15 | 155.96 | ||||
4 | group B | 306.00 | 367.20 | 382.51 | 318.76 | ||||
5 | group C | 315.00 | 378.00 | 425.38 | 354.48 | ||||
6 | TOTAL | 871.48 | 1,045.78 | 995.04 | 829.20 |
2) Unmerge Cells January and February
We should unmerge the cells for January and February so that Excel will properly use them in the chart and so that we can move them in the next step.
Your data set will look like this:
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | January | February | ||||||
2 | Actuals | Plan | Actuals | Plan | ||||
3 | group A | 250.48 | 300.58 | 187.15 | 155.96 | |||
4 | group B | 306.00 | 367.20 | 382.51 | 318.76 | |||
5 | group C | 315.00 | 378.00 | 425.38 | 354.48 | |||
6 | TOTAL | 871.48 | 1,045.78 | 995.04 | 829.20 |
3) Move January and February one cell left
We need January and February moved over one cell to the left so that Excel can create the multi-level horizontal axis.
Your data set will look like this:
A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|
1 | January | February | |||||||
2 | Actuals | Plan | Actuals | Plan | |||||
3 | group A | 250.48 | 300.58 | 187.15 | 155.96 | ||||
4 | group B | 306.00 | 367.20 | 382.51 | 318.76 | ||||
5 | group C | 315.00 | 378.00 | 425.38 | 354.48 | ||||
6 | TOTAL | 871.48 | 1,045.78 | 995.04 | 829.20 |
4) Create Stacked Column Chart
Now highlight A1:I6 and create a stacked column chart by going to your Insert Ribbon and choosing the Stacked Column Chart type.
Your chart will now look like this:
5) Choose Gap Width of 10%
Now we can make it a little better by changing the gap width of the chart series to 10%. First select a data series and then press CTRL+1 to bring up the Chart Series Options and change the Gap Width to 10%
Your chart should now look like this (Note – I also moved the legend to the right but you should check out the video on why I did this and also another way to possibly solve this challenge):
6) Add Space in Cell I3 to correct tick marks
Did you notice in the previous graph on step 5 had an extra tick mark? You can see it here:
To get rid of this extra chart axis tick mark, you need to add a space in cell I3. Then Excel will think that the I column is part of the February series and not a new series. So it will get rid of the extra tick mark and your final chart will look like this:
Video Tutorial
You can see this and another possible solution in this video demonstration:
File Download
You can download the free sample template file here:
How-to-set-the-distance-between-chart-series-in-stacked-column-chart.xlsx
Thanks to Don and Peter who came up with the solutions. And THANKS to you all for being a fan and sharing it with your co-workers.
Steve=True
Good job Steve. The more we do these, the more I learn how to fool Excel charts into doing something I didn’t think Excel could do.
Thanks Don!
Steve,
I just found your blog by searching on how to create this clustered stacked column. I have a simple data table where I am trying to create just such a chart but am having great difficulty trying to copy what you did.
Is there a way I can share my table with you.
Many thanks,
Nathan
Nathan, I may use your file as a Friday Challenge :). Steve
Thank you so much for this tutorial!
I was struggling with the correct spacing of the tick marks
and your trick was exactly what I was looking for!
Wonderful site – neat and clean!
Awesome, so glad to help. Also, thanks for the great comment!!
Steve, thank you as well for helping me created a clustered stacked chart in excel with the proper gaps between the data sets. One more question, is there any way to shrink the distance of that gap between the January and February sets?
Hi Freddy, you would want to put more filler columns on the outside of the real data columns to push them closer.
Thanks for this excellent guide Steve! I am just having one problem – entering a space doesn’t seem to be working to make the extra marking go away. Do you have any advice on what might be going wrong here?
Hi Shreya, I assume you mean the tick marks on the horizontal axis. you should try and add or remove text (typically a “space”) in different blank cells to see how it affects the horizontal axis.