How to Make a Clustered Stacked and Multiple Unstacked Chart in Excel

How to Make a Clustered Stacked and Multiple Unstacked Chart in Excel

On my post How-to Create a Stacked and Unstacked Column Chart I received a comment on how can we do this for a Clustered Stacked and Multiple Unstacked Chart in Excel?  Here is the question from Ciara:

“Is there a way to do this where you have 1 stacked bar and 2 non-stacked bars (instead of just 1) for each month? For example a single bar for Sales Quota, another single bar for Stretch Goal, and then the actual coffee/tea stacked bar sales?”  – Ciara

Here is the Original chart:

Here is the Sample Chart I made with Ciara’s question:

Clustered Stacked and Multiple Unstacked Chart

This can be done, by modifying our data series that we will use in the chart to create more space for the extra columns.  Here is how you can do it.





The Breakdown

Here are the basic steps to create a Clustered Stacked and Multiple Unstacked Chart.

1) Add Filler Series to the Data Set

2) Create Stacked Column Chart

3) Switch Row/Column Chart Data Setting

4) Move Sales Goals and Filler Series 2nd Axis and Change Chart Type to Clustered Column

5) Change Gap Width on Stacked Series to 500%

6) Delete Filler Legend Entries

7) Delete Secondary Vertical axis

Step-by-Step

Here are the detailed steps to create the Clustered Stacked and Multiple Unstacked Chart

1) Add Filler Series to the Data Set

If we have our Excel data set as such where we want the Stretch Sales Goal and the Sales Quota as single clustered columns and the have the Tea and Coffee as a Stacked column.

  A B C D E
1   Sales Quota Sales Stretch Goal Tea Coffee
2 Jan 270 540 150 140
3 Feb 288 576 100 90
4 Mar 323 646 175 185

NOTE: If you want to try it yourself along with me, you can either copy and paste the data above to an Excel sheet or download the sample data and chart at the end of the post.





Now that we have our data set, we need to modify this data set to add 1 or more data series that will fill in column space to move the Clustered Columns on the side of the stacked Series. As the stacked column will always be in the center, you need to either push your data to one side or the other or split around the centered stacked column as you see in these three samples.

a) Clustered Columns to the Left

We need 3 series to push the clustered columns far enough off center left to not overlap the stacked columns. This is the one I will use for the rest of this tutorial.

A B C D E F G H
1 Sales Quota Sales Stretch Goal Filler Series 1 Filler Series 2 Filler Series 3 Tea Coffee
2 Jan 270 540 150 140
3 Feb 288 576 100 90
4 Mar 323 646 175 185

b) Clustered Columns Surrounding

We don’t need as many filler series as the others with this setup as the Filler series fits over the 1 stacked column that will be in the center.

A B C D E F
1 Sales Quota Filler Series 1 Tea Coffee Sales Stretch Goal
2 Jan 270 150 140 540
3 Feb 288 100 90 576
4 Mar 323 175 185 646

c) Clustered Columns to the Right

We need 3 series to push the clustered columns far enough off center right to not overlap the stacked columns.





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

Donate with PayPal here:





A B C D E F G H
1 Tea Coffee Filler Series 1 Filler Series 2 Filler Series 3 Sales Quota Sales Stretch Goal
2 Jan 150 140 270 540
3 Feb 100 90 288 576
4 Mar 175 185 323 646

2) Create Stacked Column Chart

Now that we have all of our data setup, we can create our chart. To do this, select your data range from Cell A1 to the bottom right of your data. In our case where we will be plotting our clustered columns to the left of the stacked column, so our data ends in cell H4. So select cells A1:H4 and then select the Insert ribbon and pick a stacked column chart as you see here:

Your initial Excel Stacked Column Chart you have created will look like this:

3) Switch Row/Column Chart Data Setting

After you have created your initial chart, you may have noticed that it is not how you thought it would look. If that is the case, always Switch Row/Column setting for your chart and see how it looks. To do this, select the chart and then go to the Design ribbon and click on the Switch Row/Column in the Data Group.

Switch Row Columns Button

Your chart should now look like this:

4) Move Sales Goals and Filler Series 2nd Axis and Change Chart Type to Clustered Column

We now have our chart setup and ready to create the Clustered Stacked and Multiple Unstacked Chart by moving the series to the secondary axis that we want to be Clustered Column versus a Stacked Column as well as change the chart type. Let’s show you how to do that. First, select the chart, then select any one of the series.

After selecting a series, go to the Design ribbon and then click on the Change Chart Type button in the Type group as you see here:





If you are using Excel 2016 or later, then you will see this dialog box pop up. Because we had a series selected when we hit the Change Chart Type, it will bring us right to the Combo chart type. If you are not on the Combo Chart Type, then select that from the All Charts Tab.

From this dialog box, select the Secondary Axis checkbox for the Sales Quota , Sales Stretch Goal and Filler Series 1 through 3. After selecting the Secondary Axis checkbox, you can then change the Chart Type to Clustered Column. Note that you must do it in that order or you will affect your Tea/Coffee stacked column chart. Your dialog box should look like this.

After you press the OK button, your chart will now look like this:

5) Change Gap Width on Stacked Series to 500%

As you can see the chart is now a Clustered Stacked and Multiple Unstacked Chart. But it needs some tweaks. First, we need to make the stacked column look smaller like the other columns. This is an optional step as you see fit.

To make your Stacked Column Chart series look similar to the clustered columns, first select the chart, then select a Stacked Column series like tea or coffee, then press CTRL+F1 to bring up the Format Series dialog box.

Then change the Gap Width till it looks good. For our example, 500% seems to work well so that the column is no longer hidden or overlapping the clustered columns.

Your chart should now look like this:

6) Delete Filler Legend Entries

The Filler Series do not show up in the chart, but the Legend Entries do show up in the Legend. I recommend deleting these legend entries as to not confuse your reader.

To do this, select the Chart, then select the Legend, then select the Legend Entry for one of the 3 Filler Series. Finally, press your delete Key.

7) Delete Secondary Vertical axis

Lastly, the only other thing to do is to to delete the Secondary Vertical Axis so that the Clustered Stacked and Multiple Unstacked Chart is aligned.





To do this, select your chart, then select the Secondary Vertical Axis and then press your Delete Key.

Your final chart should now look like this:

Final Clustered Stacked and Multiple Unstacked Chart in Excel

Here is what the chart would look like if you moved the Clustered Columns to the Right or Split on both sides:

Clustered Stacked and Multiple Unstacked on Right Chart in Excel
Clustered Stacked and Multiple Wrapping Unstacked Chart in Excel

So why do we have to do this?

When you create a Stacked Column Chart in Excel, it will only allow you to have ALL data series on the same axis with the Stacked Column Chart Type. So to create a Clustered Stacked and Multiple Unstacked Chart you first have to move all Clustered Columns to one axis.

Second issue is that Excel ALWAYS centers the Stacked Column Chart Type. There is no moving it. It will always be in the center. Clustered Columns are also centered, so they will overlap if we have a combo chart with these 2 types of charts. Since we can’t move the stacked column chart, we therefore need to move the Clustered Columns to one side or another around the Stacked Column.

Here is a graphical representation of what I mean that we are shifting the Clustered Columns using filler columns.

Graphical Representation of a Clustered Stacked and Multiple Unstacked Chart in Excel

Video Demonstration

Check out this Video tutorial on the techniques presented above.

Sample File Download

Click here to Download the Free Sample Excel Template File:
Clustered-Stacked-and-Multi-Unstacked-Chart.xlsx

All in all a pretty cool technique. You can make Excel do a lot of things as lon as you play in the rules, but it is very flexible. Let me know of other charts you want on this type of variation to see if it is possible.

Steve=True









2 COMMENTS

  1. This was really helpful, but what about a combination of many single bars and many stacked bars? For example, I have several brands, but some brands are part of a larger group while others aren’t. And I’d like to show the total for the quarter. Such as:
    * Total
    * 4-wheeler ATV
    * Cars: Sedans, wagons, hatchbacks
    * Motorcycles: cruisers, speedsters
    Ideally I’d like to have one bar for Total, one bar for ATV, one stack for cars, one stack for motorcycles, all clustered for a quarter.

    I’ve fooled around with the fillers, etc, but can’t get my two stacked groups to separate! Help please…

LEAVE A REPLY

Please enter your comment!
Please enter your name here