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.
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.
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