Creating a clustered-stacked column chart seems like a very complicated job. However, once you understand the data structure of the graph. It's easier than you think and can be done within 15 minutes!
Clustered-stacked column graph is actually a stacked column graph with no gap width, and dividing series by using blank columns. Please take a look at the picture below.
And now the data structure should look like this: First and the last column of data consist of a dummy series with 0 data, this is required due to design of excel charting for this type and need be done before plotting the graph. For the data, each group of data must be appeared in different columns even when they belong to the same category, so we can plot them to different series with different colors. Also, data between each category will be separated by blank columns.
You should now have a better understanding of the graph. Let's get started.
• When we have the data structure built up. Select all data (excluding the "blue" labels which are on top
of the categories since they are for reference only)
• Go to "Insert", and select stacked column chart
• Now format the graph, first by moving the legend anywhere as you see fit, and delete "Dummy"
labels
• Select the horizontal axis and press Ctrl + 1, at the Format Axis menu, type in value "3" for options
of "Interval between tickmarks" and "Interval between labels", then choose axis labels to "None" and
position axis to "On tick marks". By doing these steps we can adjust the interval between labels and
position axis to show properly, and get rid of misaligned axis labels so we can manually create them
later. If you happen to be creating the chart with 3 series (or anything in an odd number) for each
year, there's no need to remove the labels, simply move them to the center column for each series
will do nicely
• Back to the graph, select any of the chart columns and press Ctrl + 1 to open the Format Data Series
menu, and choose 100% Overlap and 0% Gap Width in Series Options
• Insert a text box with the chart selected and manually create the horizontal axis label for the first
category, then Use Ctrl + shift and drag to duplicate and add labels for each category
It's done!
