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:

Final Stacked and Non Stacked Clustered Column Chart
Final Stacked and Non Stacked Clustered Column 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.

 ABCDE
1 Sales QuotaSales Stretch GoalTeaCoffee
2Jan270540150140
3Feb28857610090
4Mar323646175185

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.

ABCDEFGH
1Sales QuotaSales Stretch GoalFiller Series 1Filler Series 2Filler Series 3TeaCoffee
2Jan270540150140
3Feb28857610090
4Mar323646175185

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.

ABCDEF
1Sales QuotaFiller Series 1TeaCoffeeSales Stretch Goal
2Jan270150140540
3Feb28810090576
4Mar323175185646

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:





ABCDEFGH
1TeaCoffeeFiller Series 1Filler Series 2Filler Series 3Sales QuotaSales Stretch Goal
2Jan150140270540
3Feb10090288576
4Mar175185323646

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