How-to Make an Excel Clustered Stacked Column Chart with Different Colors by Stack

In a popular post, I showed you how to easily create a Clustered Stacked Column chart in Excel using Multi-Level Category Axis options.

Learn Excel Dashboard Course

Here is what the original chart looked like:image

But our Excel chart enthusiasts out there wanted to know if it was possible to have 2 different colors for the Budget Stacked Column vs the Actual Stacked Column in the cluster.

Here is what that would look like:

image





You can do this by simply adjusting your data.  I am not sure if it is a benefit for the chart end users.  What do you think?  Let me know in the comments below.  Here is how to make this chart in Excel.

 

You can check out other Clustered Stacked Column Chart Tutorials here:

Learn Excel Dashboard Course

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

How-to Close the Gaps Between Chart Series in an Excel Stacked Clustered Column Chart



How-to Create a Stacked Clustered Column Chart with 2 Axes

How-to Add Centered Labels Above an Excel Clustered Stacked Column Chart

 

Want to Receive the Next Post?
Join My Newsletter
Subscribe
Give it a try, you can unsubscribe anytime.
Privacy Policy

The Breakdown

1) Create the Chart Data Range





2) Insert Stacked Column Chart or Stacked Bar Chart

3) Switch Row/Column for the Chart Series

4) Change Column Gap Width

Learn Excel Dashboard Course

 





If you found the website and tutorials helpful, please consider donating to keep the lights on.

Donate with PayPal here:





 

Step-by-Step



1) Create the Chart Data Range

This is the critical step in making an EASY Stacked Clustered Column Chart with colors by stack.

This solution involves is using Multi-level Category Labels as your Horizontal Axis.  SNAGHTML10c3e3e0

 





 

Here is how we want to set up your data:image

Also, in cell A5, there is a Space entered.  We put a space in cell A5 so that the Multi-level Categories in Excel will put a tick line on the horizontal axis labels.

Learn Excel Dashboard Course

Here it is with a space                                         and                        without a space

image image

 



2) Insert Stacked Column Chart or Stacked Bar Chart

Now that you have set up your data, you can create your chart.  Highlight the range from A2:G7 and then choose the Stacked Column chart from the Column button on the Insert ribbon.image

Your chart should now look like this:image

 

3) Switch Row/Column for the Chart Series

Our chart is not in the right format.  We wanted the Product and Budget vs Actual labels on the horizontal axis, not the advertising groups.  To fix this, select the chart and then click on the Switch Row/Column button from the Design ribbon.image





If you want to know why Excel is doing this, please check this post:

Why Does Excel Switch Rows/Columns in My Chart?

Your chart should will look like this:image

 

Learn Excel Dashboard Course

4) Change Column Gap Width

This is an optional step.  To make the groupings appear more closely related, we can change the Gap Width from the Format Series Dialog Box.  You can get to these options by clicking on any data series in the chart and then press CTRL+1



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%.  image

This will move the series closer together so that the data can be compared more easily.

Your final chart will now look like this:image

 

Below is the video demonstration and a free file download.





 

Video Demonstration

Learn Excel Dashboard Course

 

File Download



You can download the free sample Excel template file here:

Excel-Clustered-Stacked-Column-Chart-with-Different-Colors-by-Stack.xlsx

 

Steve=True





Want to Receive the Next Post?
Join My Newsletter
Subscribe
Give it a try, you can unsubscribe anytime.
Privacy Policy

2 COMMENTS

  1. Hey. Thanks for the great tips.

    Is there a way to have exactly what’s mentioned above, with two vertical axis instead of one?

    For example, of the pairs of stacked columns, all of those on the left of each pair to use a left axis, and those on the right to use a right axis. Is this possible?

    Sorry for making this more complex. I write for Wikipedia, and I intend to use these techniques to make charts for the site. So your help will be beneficial to many people 🙂

LEAVE A REPLY

Please enter your comment!
Please enter your name here