Create combination stacked / clustered charts in Excel

I was asked how to do this recently by a colleague, so as usual I decided to turn it into a blog!

A lot of the people I work with don’t have access to statistical software like JMP and minitab and have to rely on Excel.  For many, this limits their ability to show complex information in charts.  For example, say you want to create a combination stacked clustered bar chart like the one shown below:


Unfortunately it is not very simple in Excel.

How can you do it?  Well, let’s imagine that your have the data as shown below:


The first thing you need to do is to separate the data into lines with spaces in between.  To get a chart arranged as above we need to make the data look like this…  (I will explain columns F and G later).


Then we need to select B1:E14 and click on the insert tab, then select the small drop down arrow below column in the Charts section and then choose the second 2-D Column chart type.


You can then format your data series so that they look more coherent.  Left click on the first data series you wish to change then press Ctrl+1 to bring up the format data series dialog box.  Click on Fill, then solid fill and change the colour.  Then click on the next series and repeat until all of the series are correct.


Once the chart looks like the second one above, click on the series options tab.  In the gap width section, move the slider until it shows no gap or 0%.  The graph will update autolatically and you can press close.


We now need to adjust the axes.  This is where columns F and G are important.  First select cells G1:G5 then with these selected, hold down the CTRL key and then select cells F1:F5 and press CTRL+C to copy.


Left click on the graph and then click on the home tab.  Left click on the small down arrow below paste and choose paste special.


Ensure the options shown below are selected, then click on OK.


The chart will update as shown below.


Temporarily put a value in cell G2 e.g. 200, so that you can see the value in the chart.  Then right click on the value in the chart and select “Change Series Chart Type”.


Then choose the first of the line chart options and click on OK.


Change cell G1 back to blank.  Then click on the layout tab and ensure that the series “Axis” is shown in the current selection box.  If not select it using the drop down arrow.


Press CTRL+1 again to see the format series menu box and select secondary axis.  This will add a vertical axis on the right hand side of the chart.  Press Close.


Then click on the small drop down arrow of the Axes button in the Axes section.  Click on Secondary Horizontal Axis and Show Left to Right Axis.


This will add a second axis above the chart.


We now need to move the top axis to the bottom.  To do this, left click on the left vertical axis and click CLTRL+1.  Select the last check box in the Axis Options tab so that the Horizontal xis crosses at the maximum axis value.


Then click on the Right vertical axis and ensure that the Horizontal axis check box is showing automatic.

Then click on the top axis.  In the Axis options tab, at the bottom in the Position Axis section, choose the “On tick marks” check box.  this will adjust the graph so that there is only a half space before and after the columns.


Select the right hand side vertical axis, which is scaled from 0 to 1, and delete it.

Format the top horizontal axis, which is scaled from 1 to 13, and format it so it has no tick marks or tick labels, and no line type.

Finally, double click on the the Axis legend entry.  Press Delete.  Left click on the Axis line and / or markers and then format the Axis series to be invisible (no marker, no line).  Then press close.  You can also move the legend to the bottom of the chart etc if you want.

Stacked / Clustered Chart

49 thoughts on “Create combination stacked / clustered charts in Excel

  1. OK- this works!

    I am amazed you unraveled the necessary steps to provide a very common and useful view of data.

    It was not as ‘easy’ as I had hoped, but excel still lacks some of the sophistication, out of the box, that we need to visualize our data.

    Your instructions were very detailed and accurate, so thank you for putting this together!

  2. Very good instructions. Thanks!!!
    Is there a way to add a line ? In my case I have a 3 different savings types(stackings) that I am showing for Projected & Actual (side by side columns) ; I also need to run a line that indicates what the target was/is. I figured out how to add a “Target” column. When I select the Target column/select change series/select line with markers, I get just the markers…no line. Any ideas? Thanks

    1. I am responding to my own comment/question. I figured out how to add a Target line. To add one, simply fill in values under “Axis” as shown above. The difference is; I will call it “Target” instead of “Axis” and keep it in the legend.

  3. I am creating a clustered and stacked chart, but do not want the values to add. Rather I want the value of one to be part of the value of the other, for example 9 superheroes out of a total of 25 possible superheroes. I would greatly appreciate your advice!

  4. Alesandra: This was excellent!!! You actually explained each step in a way that could be followed, unlike a few others that I had tried. Thanks so much.

  5. thanks for this🙂

    do you know how to transform this 2D charts into 3D?
    i tried to make it into 3D but the labels were gone.

  6. This helped me a lot(trying to do that for so long…)!!! Thanks from France! Now I see why it was good to learn english after all^^

  7. Thanks a lot Alesandra, this is very helpful. I need to add a line series to this chart though, and I’m having a hard time since it contains dates as X and numbers in the same order of magnitude as in the first two series. My chart has nine series instead of four like yours but it’s not what causes the challenge. I’m using both horizontal axes so I’m out of options it seems. Any idea?

  8. Hi Alesandra, THANK YOU so, so much for this information! It’s been almost an impossible task to figure this out with so little information on this topic online. I also have the same question as Jase above, I’m trying to add two line series but having a hard time doing so, since the lines are a projection for 2015 and my two stacked columns represent 2014 and 2015 revenue.

  9. Hi,

    Thanks for a really useful post. I went through 3 other posts on the same topic before having complete success with this one. A few points which I hope will help:

    – In your screenshot showing the data with spaces added, there should be an empty row between rows 4 and 5.

    – You spoke about the “On tick marks” check box. It is actually a radio button.(Really small point but it might cause some confusion.


  10. Excellent write up – thank you so much for your thorough explanations and screen capture images. Outstanding!!

  11. Fantastic step-by-step explanation – thanks so much, the College loved seeing out data laid out like this!!

  12. Hi Alesandra,

    Am looking out to add two cell values in one Clustered chart. For ex: Total is 700, off which 250 as Passed. I need to represent this in one single bar within 700 as total & 250 should be within the part of 700.
    How to add or club within one bar? When I tried, the bar depicted to a total of 700+250=950 & after 700, 250 represented above with a different color.

  13. Hi Alesandra,

    I worked through the step-by-step instructions and was able to replicate your results using 2 years.

    I tried it with 5 years with no success.

    I tried again with 3 years in the hopes of getting three stacked columns but it takes the additional information and stacks it on the first 2 columns.

    Is this possible?

    Thank you

    1. The reason this type of chart works is because Excel tries to graph every cell in the data ranges provided, but when the data is blank it leaves it out. If you look at the first row of data provided (402, “”, 453,””), you will notice the two valid numbers are elements of the first stacked column. The second row comprises the elements in the second stacked column (“”,400,””,400), and the third row comprises the elements in the third column (“But the third row is blank”, you might say. Exactly! The third column is the blank space between the first set of stacked columns and the second set).

      If you wish each column to have three years (like 2011, 2012, 2013), then add two more columns after 2012 and continue the staggered pattern on the rows. If you wish there to be three stacked columns in each grouping, you have to add a row between each blank row that I mentioned before, and add the data in the new row.

      For example, I used this tutorial to make a grouping of one stacked column (a target) and a stacked column with four components (to reach the target). The first few rows looked like:

      (Year 0, Target, “”, “”, “”, “”)
      (“”, “”, Component1, Component2, Component3, Component4)
      (Year 1, Target, “”, “”, “”, “”)
      (“”, “”, Component1, Component2, Component3, Component4)

      Or you could graph a group of three stacked columns by adding another row:
      (Year 0, Target, “”, “”, “”, “”)
      (“”, “”, Component1, Component2, “”, “”)
      (“”, “”, “”, “”, Component3, Component4)
      (Year 1, Target, “”, “”, “”, “”)
      (“”, “”, Component1, Component2, “”, “”)
      (“”, “”, “”, “”, Component3, Component4)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s