How-to Create a Stacked and Unstacked Column Chart in Excel

Final Stacked and Non Stacked Clustered Column Chart
Final Stacked and Non Stacked Clustered Column Chart

Stacked and Unstacked Column Chart in Excel

Excel is awesome because, even when a certain chart type is not a standard option, there may be a way that you can create the type of chart that you really wanted to make. For instance, I received the following Excel chart question but this is not something that you can do normally in Excel without knowing this trick/technique.

Excel User Question

“Is there any way to compare a stacked column to a non-stacked column? Example, at work, I have to graph a sales quota bar next to actual sales which are made up of two types. Is there any way to have the left bar be a solid “quota number” and to the right of it have a stacked bar made up of the two types of sales. The horizontal axis should be in months. Can’t figure it out!” –Adam

This is what the final chart would look like:

Final Stacked and Non Stacked Clustered Column Chart
Final Stacked and Non Stacked Clustered Column Chart

Excel Chart Standards

In Microsoft Excel, data plotted as a Stacked Column or Stacked Bar Chart Type on the same axis will be stacked into a single column. This means that you can only choose a stacked column chart OR clustered column chart for each axis. Any column type data (vs line data as you can combine line and column charts) will be plotted as part of the stack on the same axis. There is not an option for a stacked and clustered column chart on the same axis.

The Breakdown

In order to achieve an Excel Chart with both stacked and unstacked columns that are side-by-side, you will have to move some of the data to the secondary axis and also manipulate the chart data by adding some filler series.





1) Add Filler Series
2) Create Clustered Chart
3) Switch Row/Column Chart Data Setting
4) Move Stacked Column Data to Secondary axis
5) Change Chart Type of Secondary Axis Data
6) Change Chart Gap Width
7) Delete Filler Legend Entries
8) Delete Secondary Vertical axis

Step-by-Step

1) Add Filler Series

Here is a representation of the data that Adam describes in the problem statement

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

First, we need to modify the data and add in 2 filler series. We need to put two additional series between the sale quota column data and the actual sales stacked column data, as you see here.

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

You should now be all set to create your Stacked and Unstacked Column Chart.

2) Create Clustered Chart

The next step is to create our chart. So for our representative data, select the range of A1:F4. Then select the Insert Ribbon and chose a 2-D Column chart from the Columns button in the Charts group.



Insert 2-D Clustered Column Chart for Stacked and Non-Stacked Clustered Column Chart

Your resulting chart should look like this:

Initial 2-D Clustered Column Chart for Stacked and Non-Stacked Clustered Column Chart
Initial 2-D Clustered Column Chart for Stacked and Non-Stacked Clustered Column Chart

3) Switch Row/Column Chart Data Setting

Unfortunately, Excel will make a choice for you on how you want to setup your chart by choosing the rows and columns setting for you. If months are not the categories of the horizontal axis of the chart, you need to select your chart, then select the Design Ribbon and click on the Switch Row/Column button from the Data group.

Switch Row Column for Stacked and Non-Stacked Clustered Column Chart
Switch Row Column for Stacked and Non-Stacked Clustered Column Chart

If you want to know why Excel makes this choice for you, read this article:
Why Does Excel Switch Rows/Columns in My Chart?

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

Your updated chart will now look like this:

Switched Row Column for Stacked and Non-Stacked Clustered Column Chart
Switched Row Column for Stacked and Non-Stacked Clustered Column Chart

4) Move Stacked Column Data to Secondary axis

As discussed above, you can only choose stacked or non-stacked columns on each axis. You can’t have both on the same axis. So to get the chart that we desire, we need to move the series for the stacked columns to the secondary axis. To do this, select your chart and then double-click on either the Tea or Coffee columns. Then change the Plot Series On radio button to Secondary Axis on the Format Data Series dialog box.





Move Series to 2nd Axis for Stacked and Non-Stacked Clustered Column Chart
Move Series to 2nd Axis for Stacked and Non-Stacked Clustered Column Chart

Repeat this step for all of the stacked column data series. After you move both, your chart will now look like this:

Move 2nd Series to 2nd Axis for Stacked and Non-Stacked Clustered Column Chart
Move 2nd Series to 2nd Axis for Stacked and Non-Stacked Clustered Column Chart

5) Change Chart Type of Secondary Axis Data

Now that we have the series on 2 different axes, we can change the chart type of the series on the secondary axis to a Stacked Column Chart. To do this, select your chart, then select either the Tea or Coffee series, then select the Design Ribbon and then select the Change Chart Type button in the Type group. Then from the Change Chart Type dialog box, select the Stacked Column option.

Change the 2nd Axis to Stacked Column Chart
Change the 2nd Axis to Stacked Column Chart

Your combined clustered column and stacked column chart will now look like this:

Initial Stacked and Non-Stacked Clustered Column Chart
Initial Stacked and Non-Stacked Clustered Column Chart

 6) Change Chart Gap Width

Because we are tricking Excel, your chart may not look perfect as you see above that the stacked column chart on the secondary axis overlaps the clustered column series on the primary axis. In order to correct for this, you will need to adjust the Gap Width of the Stacked Column chart. To do this, double click on either stacked column data series (Tea or Coffee) and then change the Gap Width on the Series Options to 350% from the Format Data Series dialog box as you see here.





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

Donate with PayPal here:





Aligned Stacked and Non-Stacked Clustered Column Chart
Aligned Stacked and Non-Stacked Clustered Column Chart

This will then align the non-stacked columns with the stacked column in the chart as though they were on the same axis.

7) Delete Filler Legend Entries

And now for the Excel Chart clean-up, we first need to clean up the legend of the 2 filler series. To remove the filler series from the legend, first, click on the chart, then click on the legend, and then click on the legend entry. Once you have selected the one you want to delete, press your delete key. Repeat this step for both filler series.



Delete Legend Entry Stacked and Non-Stacked Clustered Column Chart.png
Delete Legend Entry Stacked and Non-Stacked Clustered Column Chart.png

8) Delete Secondary Vertical axis

Our chart is almost completed. The last thing is to fix the vertical axis values. We can do this by either fixing the values to match or just delete the secondary axis. To delete secondary vertical axis, first click on the chart, then click on the secondary vertical axis. Once you have selected it, then press your delete key.

Delete Secondary Vertical Axis Stacked and Non-Stacked Clustered Column Chart
Delete Secondary Vertical Axis Stacked and Non-Stacked Clustered Column Chart

Here is what your final chart would look like:

Final Stacked and Non Stacked Clustered Column Chart
Final Stacked and Non Stacked Clustered Column Chart

Thoughts About This Chart Type

One minor flaw with this solution that you can see in the chart above is that the Stacked Column Chart is centered over the horizontal category axis while the non-stacked column chart will not appear that way. It is an annoyance in my mind, but not a critical issue. I would love to know your thoughts about this in the comments below.

Alternate Charts

If you are considering using this type of chart, here are a few alternate types of Excel Charts that might meet your needs.  Click on the links to see a similar step-by-step tutorial with Videos and Sample File Downloads:





  1. How-to Add Lines in an Excel Clustered Stacked Column Chart
  2. Bullet Charts

Sample File

You can download the sample file here so that you can try and recreate the same ‘Stacked and Unstacked Column Chart in Excel’: Stacked-and-Non-Stacked-Clustered-Excel-Chart.xlsx

Video Demonstration

Check out this quick tutorial to learn how you can quickly recreate this chart in Excel.


Let me know what you think about this chart tip/trick in the comments below.

Steve=True





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

26 COMMENTS

  1. This was extremely helpful! I needed to do this for work and I struggled all day with it until I came across your post.

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



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

    Donate with PayPal here:




  3. Hi, thank you so much!!
    I have a problem and I think this could help.
    I need to use 2 columns stacked and 4 unstacked but its too dificult

  4. This worked great but I need to use a data table and so it shows the Filler rows – is there a workaround for this?




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

    Donate with PayPal here:




  6. This was a great and really useful tutorial – I struggled for some time trying to figure out how to do this. Only one problem for me — no matter what I do my columns are still on top of each other. I can make one wide and the other narrow, but I cannot make them side by side like yours. Any idea why? Thanks again.

    • Hi Rebecca,

      I would imagine that you need an additional filler series is on the secondary axis. Hope that helps. Let me know. Steve

  7. Is there a way of doing this with the horizontal axis, showing tea, coffee, hot chocolate, water, etc instead of months and the stacked chart being the month expenditure with the un-stacked being annual expected expenditure?

    • Hi Jack, I think you might be describing a graph that is very complicated, so maybe I am not understanding. Are you going to have a stack of 3 boxes for Jan/Feb/Mar for Tea and on top of that 3 boxes for Coffee Jan/Feb/Mar? It will get very muddy to see which box in the stack is for Feb-Coffee. Or are you proposing a more simple chart of 1 column of Total Sales Quota and then Total Sales per month for all products? The latter is a much simpler chart. you just need 2 rows like this (one line for total quota and one line with sales per month and NO total for that line):
      ………. Jan Feb Mar Total
      Sales Quota 881
      Sales ……..190 190 360

  8. Want to Receive the Next Post?
    Join My Newsletter
    Subscribe
    Give it a try, you can unsubscribe anytime.
    Privacy Policy
  9. Thank you – this is really great.

    I had to make something similar in the past and I kind of cheated that time – I created columns for “Jan Quota”, “Jan Tea & Coffee”, “Feb Quota”, “Feb Tea & Coffee”, etc and put an empty row between the months. But glad to find this solution.




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

    Donate with PayPal here:




LEAVE A REPLY

Please enter your comment!
Please enter your name here