Step-by-step tutorial on creating clustered stacked column bar charts (for free)

This tutorial will show you how to create a clustered stacked column bar chart – step-by-step, so there is no way you will get confused. If you like this tutorial and find it useful, have questions or comments, please feel free to leave me a comment! I would love to hear your feedback!

Download here to download: cluster stacked column bar chart Excel template


 
 

Here’s an example of what a clustered stacked column bar chart looks like:

This is the clustered stacked chart.

Laying out the Data

  1. Create a copy of the data table by setting cells to equal the original table
  2. Shift cells to create separate row for each stack
  3. Add separate row for each cluster
  4. Add column headings
  5. Create extra row between the column headings and first row of data
Table cells layout for a clustered stacked table

This is how the cells should layout in the cluster stacked table. The second stack is 1 row down from the first stack.

Creating the Chart

  1. Highlight the copy of the data, and create chart by Insert -> Column Chart -> Stacked Chart

  2. Eliminate all gaps by right click bar chart -> Format Data Series -> Series Options -> set Gap Width to 0%
  3. There should only be half a gap before and after the first and last bar respectively. Right click bottom horizontal axis -> Format axis -> Axis Options -> Position Axis -> On tick marks
  4. Re-order data source to desired order for bar chart (optional)
  5. Fix coloring to desired colors (optional)
  6. Change chart component sizes (optional)

Fixing the Axis

Up till now, we have something that looks like a stacked clustered chart, but need the x axis to have the right category labels, grouping the stack bar charts.

  1. Add a dummy data series. Right click on chart -> Select Data -> Add -> Series values -> Highlight a number of cells based on the number of categories in chart (just 4 in this example)
  2. Set the dummy data series to the “secondary axis”. Layout (Ribbon) -> Select the dummy data series -> Format Selection -> Plot Series On -> Secondary Axis (if you are having trouble selecting the dummy data series you just created, just change the values to something so that it can be selected, and change it back after you are done this step)
  3. Set the Horizontal Axis Label for the dummy data series into the category names. Right click chart -> Select Data -> Select the dummy data series (left pane) -> Click edit (right pane) -> Highlight the category names
  4. Move the category names to the bottom of the chart. First, move the axis on the bottom to the top. Right click the vertical axis on the left -> Format axis -> Axis Options -> Position Axis -> Horizontal axis crosses: -> Maximum axis value
  5. Second, move the category axis to the bottom. Right click the right vertical axis -> Format axis -> Axis Options -> Position Axis -> Horizontal axis crosses: -> Automatic
  6. Delete the dummy data series from the legend
  7. Delete the right vertical axis. Left click and select the right vertical axis -> press “Delete” (keyboard)
  8. Hide the top horizontal axis. First, get rid of tick marks. Right click on the top horizontal axis -> Format Axis -> Axis Options -> Major tick mark type -> None.
    Get rid of the line. Line Color -> No line -> Click Close
  9. Hide the text on the horizontal axis. Left click on text on top horizontal axis -> Change font color to white.
  10. Hide grid lines (optional) Right click on grid line -> Format Gridlines -> Line Color -> No line
  11. Resize various chart components to your liking (optional)

 

VN:F [1.9.15_1155]
Rating: 9.2/10 (76 votes cast)
Step-by-step tutorial on creating clustered stacked column bar charts (for free), 9.2 out of 10 based on 76 ratings
Leave a comment ?

22 Comments.

  1. Sivasubramanian

    Thanks for the information. Really useful:-)

  2. THANK YOU! This is really great and clever!! :smile:

  3. very smart technique

  4. I want to do exactly this but I want the stackings to be the same for each cluster; so in the example you post, United States and Canada would be broken down by the same metrics and color.

    This is for different loan types for a particular lender versus it’s peer group. So under each month, say January 2012, you would have two columns – one for the lender, one for the peer group – and then each column would be stacked by the same metrics and colors. Please advise if you can and thank you very much.

  5. Adding dummy comment doesn’t make sense. Does this not work in Excel 2007?

  6. Excellent advise except on how to eliminate the gaps. You have to right click on a “data series” within the chart not right click the chart. The only way I couldget the “Format Data Series” is when I right clicked on a data series with the chart.

  7. Hi This has been veryhelpful but I am unable to delete the “dummy data” from the legend. Assistance please.

  8. Excellent tutorial, and thanks to Sharon for helping me out with eliminating the gaps! :cool:

  9. Excellent tutorial and cleverly done. One improvement suggestion. In step 9, instead of making the labels white, you can do the following:

    Right click on the top horizontal axis -> Format Axis -> Axis Options -> Axis labels -> None

    This has the benefit of freeing up the space the labels take up and also ensuring that the ‘hidden’ labels don’t appear if the document is printed.

  10. Result of “fixing the axis” no. 3 looked differently: second/category axis did not spread across the full chart but stayed at the left of the chart. Any advice? Thx!

  11. Thanks, very useful.

    I am having problems with step 3 of Fixing the Axis. I am not able to put the secondary horizontal axis labels, which are my categories for the clustered stacked chart.

    Any clue how to do it?

  12. can you add a cumulative line for actual and a separate one for forecast?

  13. Very useful and excellent step-by-step instructions. Great job!

  14. Thank you very much. Simple, elegant solution and clear instructions.

  15. Excellent tutorial!!
    I am trying to add standard error bar in my stacked column chart. Would you please help me how to do that?

  16. Extremely helpful. Thanks

  17. Thanks. This was so useful!!!

  18. Very good, it worked like a charm.

    It is not always super easy to follow (Layout (Ribbon) -> Select the dummy data series -> Format Selection >> this is in the top left section of the Ribbon, where the different parts of the chart can be selected. that was not clear initially and would have been great to add a screenshot of it. Once I found it, I could do without adding extra data in the dummy series.

    It is very important to follow this order, else Excel messes it up.

    One final thing that I am curious about:
    Why do you work with the MAX and the INDEX formula? Instead of MAX, I have just added the numbers myself. Instead of INDEX, I just put a normal reference into each cell to tell Excel where to grab the data from (e.g., =E10).
    Why complicated when I can do it with more ease?
    Maybe there is a reason…

    Thank you in any case, keep going.
    Daniel

  19. On step 3 the Secondary Horizontal axis doesn’t seem to show up. To fix that go to the top buttons in Excel – click Layout tab – Axes – Secondary Horizontal Axis – Show Left to Right Axis

  20. Just I was breaking my head for long time and then came across this tutorial and it was very useful and I could do exactly what I want to do. Thanks

  21. Worked like magic!! Thanks really useful!!

  22. Thanks so much for creating this tutorial. I was very happy with the results, and the directions were clear and easy to follow. Much clearer and easier compared to others that came up the search. Using your method both improved the process and results of my research project. Thanks again!

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>