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%
    creating the chart 2creating the chart 2a
  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
    creating the chart 3creating the chart 3a
  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)fixing the axis 2
  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
    fixing the axis 3fixing the axis 3b
  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
    fixing the axis 4afixing the axis 4b
  5. Second, move the category axis to the bottom. Right click the right vertical axis -> Format axis -> Axis Options -> Position Axis -> Horizontal axis crosses: -> Automaticfixing the axis 5b
  6. Delete the dummy data series from the legend
    creating the chart 6
  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)

 

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

  1. Carson says:

    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.

  2. Sharon says:

    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.

  3. Brian says:

    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.

  4. Maike says:

    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!

  5. Jorge says:

    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?

  6. Daniel says:

    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

  7. BRIAN says:

    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

  8. AL says:

    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!

  9. Cybodz says:

    Brilliant – but somewhere between 3 and 4 I can’t get the Horizontal labels to appear at the top. I’ve got my numbers at the bottom left and right – but when I edit the horizontal it won’t appear at the top?

    What am I doing wrong?

  10. Robin Q says:

    The template you have created for the clustered stacked bar chart is great! I have one problem I wonder if you can help me with. I copied and pasted the chart I created with your template into PowerPoint which worked fine. Traditionally, I can copy a PowerPoint slide, edit the data in EXCEL, and then have another version within the same presentation, but with different variables/data. With this template, all duplicate slides are linked to the same EXCEL sheet and so all slides conform to the same data at all times (if I change one, it changes them all). Is there a way around this without having separate EXCEL sheets for each slide’s bar graph? Many thanks! RQ

  11. unlikeKansas says:

    Thanks for the tutorial. I managed to work my way through this last year with Excel 2010, but now that we have moved to Excel 2013, the charting methods have all changed, so the tutorial might need an amendment or addenda.

  12. Sarah says:

    Fantastic! This was one of several articles I attempted to use to achieve this task and yours was the one that was successful for me. Clear, easy to follow, and thorough. Thanks SO much for taking the time to put this together for others!

  13. Diane Elmes says:

    Really clever approach, but not working for me I am afraid. It is in the dummy range, I follow the steps and the categories all cluster together to the left of the axis.

    I have 10 stacks, 2 per year, and everything works up to but not including the dummy data set creation. Everytime the dummy data set, just gathers to the left of the chart, does not space out in the same way as the primary data set.

    8 of 10.

  14. Mike says:

    I’m having a challenge making this work for the solution I want and not certain it can be done. The data is as follows
    sales 100
    cogs 30
    payroll 25
    occupancy 10
    G&A 15
    I need the column to show 100 as the highest point and the other components are imbedded within as relational. Has anyone done this? Perhaps I’m overlooking something but when I use the methodology the column height becomes 100+30+25+10+15=180.
    Can anyone shed some light?
    Mike

  15. Rob says:

    🙂
    Thanks for the assist. I was having trouble with a complex stack bar chart. The tutorial was excellent help, well written and easy to follow presentation.

  16. Luke says:

    I have found that if you need a secondary axis this method does not work since you plot the dummy series against the secondary axis 🙁 I may be wrong but other than this it is a great tutorial thank you!

  17. Luke says:

    To solve this problem I did not create a dummy series, so as to not mess with the series that is already using the secondary vertical axis.

    I have three stacked columns the third of which uses the secondary vertical axis. I used the original horizontal category label but deleted all but one of the 1’s and all but one of the 2’s. I changed the 1 to the first month and the 2 to the second month.

    Lastly I right-clicked the primary horizontal axis (now a series of the form: {“”,”March”,””,””,”April”,””}) and set the interval between tick marks to 3.

    No need for dummy axis in this case where there is an odd number of stacked graphs for each category.

    Cheers.

  18. Lucy says:

    I want to make a clustered and overlapping column bar in Excel. I’ve searched all over and can’t find any way of doing it. I have 7 bacterial strains, each exposed to 7 compounds. They change their resistance to the compounds (wt and mutant) which is what I want to show with the overlap. So I want groups of 7 strains, each of them with two overlapped values, for 7 compounds. Any ideas?

    thanks!

  19. Donna says:

    HELP! I want to show a stacked column with three different sets of values for each month for two different companies. IE July to June – Company A stacked column (3 sets of values) and in another stacked column (3 sets of values same description as Company A but different amounts)also for July Company B. Hope this makes sense.

Leave a Reply

Your email address will not be published. Required fields are marked *

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