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:
Laying out the Data
- Create a copy of the data table by setting cells to equal the original table
- Shift cells to create separate row for each stack
- Add separate row for each cluster
- Add column headings
- Create extra row between the column headings and first row of data
Creating the Chart
- Highlight the copy of the data, and create chart by Insert -> Column Chart -> Stacked Chart
- Eliminate all gaps by right click bar chart -> Format Data Series -> Series Options -> set Gap Width to 0%
- 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
- Re-order data source to desired order for bar chart (optional)
- Fix coloring to desired colors (optional)
- 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.
- 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)
- 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)
- 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
- 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
- Second, move the category axis to the bottom. Right click the right vertical axis -> Format axis -> Axis Options -> Position Axis -> Horizontal axis crosses: -> Automatic
- Delete the dummy data series from the legend
- Delete the right vertical axis. Left click and select the right vertical axis -> press “Delete” (keyboard)
- 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
- Hide the text on the horizontal axis. Left click on text on top horizontal axis -> Change font color to white.
- Hide grid lines (optional) Right click on grid line -> Format Gridlines -> Line Color -> No line
- Resize various chart components to your liking (optional)