How-To Easily Make a Stacked Clustered Column Chart in Excel

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

• There is one type of chart that is always
  requested, however, Excel doesnt offer this type
  of chart.  What could it be?

3
Excel does not offer a Clustered Stacked Column
chart, nor does it offer a Clustered Stacked Bar
chart.   Excel offers a Clustered Column chart
type and a Stacked Column chart type, but it
doesnt offer the combination of these two
charts.  Same goes for a Clustered Stacked Bar
chart. What does Clustered Stacked Column Chart
show? Well a clustered stacked column chart would
allow you to group your data (or cluster the data
points) but use it in conjunction with a stacked
column chart type.
4
Here, is what a Clustered Stacked Column Chart
would look like
5
Here is what a Clustered Stacked Bar Chart would
look like
6
Clustered Stacked Column or Bar Chart Type is Not
Available in Excel
Why doesnt Microsoft Excel offer these types of
charts?  I dont know, but they are commonly
requested by users.  In this tutorial, I will
show you how to make a Clustered Stacked Column
Chart, but the technique is the same when
creating a Clustered Stacked Bar Chart.  The
only difference is that you will choose a stacked
bar chart instead of a stacked column chart. 
Other than that, everything else is exactly the
same.
7
The Breakdown

• Create a Chart Data Range
• 2) Create Stacked Column Chart or Stacked Bar
  Chart
• (Note this tutorial will show only the Stacked
  Column Chart Type but the techniques are the
  same)
• 3) Switch Row/Column
• 4) Change Column Gap Width
• 5) Change Vertical Axis
• 6) Change White Fill Series to a Color of White
• 7) Remove the White Fill Label from the Legend

8
Step-by-Step - 1) Create a Chart Data Range
This is the critical step in making an EASY
Stacked Clustered Column Chart.  There are
other ways to create this Excel chart type, but
they are not easy and usually confuse
people. What my solution involves is using
Multi-level Category Labels as your Horizontal
Axis format.  
9
More On Excel Multi-Level Categories in Charts

• Multi-level categories is the key.  You can check
  out other examples of charts that I have created
  with Multi-level Category Labels here
• Case Study Solution Mom Needing Help on Science
  Fair Graphs/Charts
• This chart is going to compare the advertising
  spend on two different products in four
  categories for both budgeted numbers and actual
  numbers.

10
So here is how we want to set up our data
11

• As you can see, I have 2 products.  Then I have a
  separate line for the budgeted advertising
  dollars and the actual advertising spent. 
• There are 4 categories of the budget and
  expenditures related to Radio, Print,
  Television and Internet. 
• You will also see a chart data series on the
  right called White Fill.  This is used to create
  a blank between the clusters.  You can check out
  a post with a similar trick here
• How-to Make a Wall Street Journal Horizontal
  Panel Chart in Excel

12
Stacked Column Example

• I am going to spend the bulk of this post here,
  so lets examine the way that I have set up the
  data. The first row of the data contains the
  items that we want to appear in the legend as the
  categories of our stacked columns.

13
The next few rows represent our the first section
of our first Stacked Clustered data group. 
This will be for Product 1 and we have 2 rows,
1 that represents the budgeted advertising
dollars and one that represents the actual
advertising dollars for all 4 categories (radio,
print, TV and internet
14
The biggest thing to remember here is that we
have created a Multi-level Category.  We have
done this in Column A.  Notice that A3 has the
Product name, but there is nothing in A4. What
this does is create a grouping or Multi-level
Category for the chart for the first product. 
Here is what it will look like in the final
chart
15
Notice how it creates a grouping for Product 1
and has subcategories for the budget vs. actual
numbers. The next row appears to be a blank
row.  It is mostly blank except in 2 cells.  In
cell G5, I have put the value of the highest
horizontal gridline that I want to display in the
graph. 
16
Also, in cell A5, I have put in a Space.  Just a
simple space with your keyboard.  I put a space
in cell A5 so that the Multi-level Categories in
Excel will put a line from the chart to the
bottom of the horizontal axis labels. Here it
is with a space and without a space. See how
the grouping for Product 1 is tight around the
budget and actuals vs. on the right when it spans
across the empty space?  The space is important
to tell Excel where the first Multi-level
Category ends.
17
Finally, the last area of the chart data range
is our next advertising expenditures grouping for
Product 2
18
2) Create Stacked Column Chart or Stacked Bar
Chart
Now that you have set up your data, you can
create your chart.  Highlight the range from
A2G7 and then choose the Stacked Column chart
from the Column button on the Insert ribbon.

19
Your chart should now look like this
9/20/2019
19
20
3) Switch Row/Column
Now our chart isnt in the right format that we
want.  We wanted the Product and Budget vs.
Actual labels on the horizontal axis, not the
advertising groups.  We can fix this by selecting
the chart and then choose the Switch Row/Column
button from the Design ribbon. If you
dont know why Excel is doing this, please check
this post Why Does Excel Switch Rows/Columns in
My Chart?

21
After you have done this, your chart should now
look like this
22
4) Change Column Gap Width
This is looking very close to what we want.  Now
lets make the groupings appear more closely
related.  We can do this by changing the Gap
Width from the Format Series Dialog Box.  You
can get to these options by right clicking on any
data series in the chart and then selecting
Format Data Series from the pop up menu.
23
Then change the Gap Width from the Series Options
to your desired size.  In this case, I am going
to change the gap width to 25.
24
Here is what your chart should look like
now This will make the clustered stacked
columns move closer together so that the data can
be compared more easily. 

25
5) Change Vertical Axis
I dont like the default vertical axis maximum
that Excel sets for us.  I would like to change
it to something that is closer to my
data.  Right click on the vertical axis and then
choose Format Axis from the pop up menu.
26
Then from the Format Axis dialog box in the Axis
Options, change the Minimum to 0 and the Maximum
to 701 as you see here
27
Your resulting chart should now look like this

28
6) Change White Fill Series to a Color of White
We are almost done.  Now we added an additional
series called White Fill that will create even
more separation between the 2 different clustered
stacked column chart.  So to make it do this,
we need to right click on the White Fill column
and choose the Format Data Series from the pop
up menu.

29
Then, from the Format Data Series dialog box, go
to the Fill options and then change the Fill
choice to Solid Fill and change the Fill Color to
White.
30
Your chart should now look like this
31
7) Remove the White Fill Label from the Legend
Looks great.  We are almost done.  Only one
last thing to do.  We need to remove the White
Fill legend entry.  We can do this pretty
quick and easily by first selecting the chart,
then select the legend and then finally selecting
the White Fill legend entry.
32
Now it should look like this Once you have the
White Fill legend entry selected, simply press
the delete key.  Your final chart will now look
like this
33
This is how you can create a very simple and easy
Clustered Stacked Column Chart. Check the link
below to see this tutorial in action in a video
demonstration. Video Tutorial If you would like
to see a video demonstration of this technique in
Excel Chart building, please visit our YouTube
Channel at this link http//youtu.be/ztphRTL_rAw
Thanks for viewing this presentation! Please
visit our blog and let us know if you found this
helpful by posting a comment and dont forget to
sign up for the RSS Feed. That way you are sure
to get the most current Excel Dashboard Tutorial
http//www.exceldashboardtemplates.com/