In a previous article, I created a step-by-step guide on how to create a clustered stacked column bar chart from scratch. For the sake convenience, click here to download the Cluster Stack Chart Template. The template will come in handy. This tutorial also shows you how to customize to fit your exact needs.
Terminology
Here’s a few keywords used in this tutorial to describe different parts of the chart.
- Cluster – a group of columns
- Column – one entire vertical column made of stacks
- Stack – the vertical bars that “stack” together up on top of another
How to Add Another Cluster into the Chart?
As an example, in the bar chart template file, if you want to add an additional column cluster geographic region into the chart, here’s how you do it:
- Insert row to into input table (anywhere from row 5 to 7)
- Type in the correct data for the new row
- Highlight and copy rows 20 to 22
- Right click on entire row 23, click “Insert Copied Cells”
- Your spreadsheet should look like this:
- The grey “Chart Data Table” from row 10 to 23 should have exact same data as the orange “Input Table” above, except for the special spacing.
- The new row’s data should be in the grey table
- In row A of the grey table, the biggest number should be in the last row. The biggest number equal to the number of rows in the orange table.
- The bar chart should have the new cluster in it.
Troubleshooting
If the bar chart does not show the new column cluster, check if the chart is selecting all the rows in the grey table. If that still doesn’t work, feel free to post ask a question in the comments section!
How to Add Another Stack into the Chart?
As an example, in the bar chart template file, if you want to add an additional stack representing a new type of expenses into the chart, here’s how you do it:
- Insert an addition column on the tables between columns B to G. Highlight an entire column. Right click on the column heading -> Click “Insert Copied Cells”
- Make sure columns have the same # of stacks. That means if you added a stack for the left column, the right column should have an extra stack too. For example, in here I am adding “Forecast Overhead Cost”, so I should also add a “Actual Overhead Cost”
- Insert the correct heading and data accordingly.
- Make sure the rows in Chart Data Table are laid out correctly. The new forecast stack added in should have its numbers lined up in the same row as other forecast stacks. The new actual stack should be in the same row as other actual stacks.
- Add the new data series into the chart.
- Left click on the chart -> Design tab -> Click “Select Data”
- In the “Select Data Source” pop-up window -> Click “Add” -> Click on the “Series Name” textbox -> Select the the heading cell
- Click on the “Series Values” textbox -> Select the new column added in the “Chart Data” table, starting from the first empty cell, down to the last one.
- Click OK -> OK
- Make the newly added series visible.
- Select the newly added series. Left click on the chart -> Layout tab -> expand the top left drop down menu by clicking on the arrow -> select the newly added series -> Click “Format Selection” also on the top left
- In the “Format Data Series” window -> Select “Primary Axis” -> Click “Close”
- In the “Design” tab -> Click “Change Chart Type” -> Click “Stacked Column” -> OK
- Eliminate and shrink the gap between columns. Click on the newly added stack -> Right click -> Click “Format Data Series” -> On the left “Series Options” -> Select “No Gap”
- Fix the column horizontal spacing, as there is excessive space on the left and right sides of the bars.
- Select the primary horizontal axis. Layout tab -> “Axis” -> “Primary Horizontal Axis” -> “More Primary Horizontal Axis Options”
- In the section “Position Axis” (Bottom of pop-up window) -> “On tick marks” -> Close
- Repeat prior steps for each new column
How do you create a chart from different or multiple sheets? e.g sheet1 figure 2000; sheet figure 3000; sheet3 figure 1000 etc.without this data copied on the same sheet as the chart.
Sorry, I am not sure how you can do that, but you can actually dynamically link to cells in different sheets. This means you can grt all the values in different sheets into one, and create a chart that way. Since these are links, if a value changed in the original sheet, the chart will update too.
Hope this helps.
I have tried to download the template file but it opens a zip with a bunch of XML files that won’t open in Excel 07. Can you provide some help with this?
How do you add another column to the cluster?
Thank you for the template! Very helpful.
Hi, how do you add column to the template? The horizontal axis title will be crappy. 🙁
Thanks for the feature request. I will try to add it in the future.
Thank you for the very clear instructions. It was very helpful!
Hi! I downloaded the Cluster Stack Chart Template zip file, but can’t for the life of me figure out what to do with it… When I unzipped it, I don’t see anything that looks like an Excel sheet. Is it possible to just email the Excel sheet unzipped? Thank you!
How do you add a line chart to a cluster series. I have revenue as a cluster defining the different types of revenue but I want to put just the line of expenses through there is only one type. I have tried changing chart type while sitting on expenses but it is not working
Thanks very very much! You made me look like a star in the eyes of Chief Operating Officer. I was able to prepare beautiful charts with your trick. Thanks again!
Great template and instructions, however I need to change the Y axis to a log scale and I’m losing data series. It happens even with your template: change the axis, and you lose “Forecast Misc Cost.”
Any ideas on how to solve this please?
Hi I am trying to add in error bars with custom values into this chart, when I customise the error bars I lose the error bars for all but the left most stack. If you had any solutions to this it would be hugely appreciated.
I am super impressed with this tutorial to create what I have done so far, well done on the super clear communication!
Doesn’t want to work when your X axis is a date series e.g. Mmm-yy
When I changed the X axis to text it worked. Not very elegant. Any solutions?
how do you stack a column with components of another item without them adding together? Said another way, if sales is the main component in the column stack how do you have cost component imbed? Say sales is 100, cost of sales 35 and payroll 25; when I stack the height of the column is 160 but I want it to be 100.
Mike
I need to have a line graph that shows percentages – is it possible to do that? When I try to insert one and then make it a secondary axis, it skews the entire graph. Please help.