Custom Charts in Excel:: Comparison Stacked Column Chart

When we need to compare data points from two time periods side by side, we normally use a column chart with two series (for the two time periods) as below:

YOY Comparison

But what if we want to see the comparison of categorised data? I have data for two years, let’s take 2012 & 2013 for simplicity. The data points show the number of employees that were hired in each department. I want to see a month by month comparison of each department, and the total as well. It cannot be done using standard charts in Excel. i.e., we can’t have two (or more) stacked columns side by side like below:

Comparison Stack Chart

However, there is a work around. To get your chart to look like this, you just need to pay attention to how you arrange your data. Here is how it can be done –

1. keep the departments as series
2. same months of both years should be kept next to each other
3. there should be a blank row after each month

This is how your data should look like when ready:

Data – Comparison Stack Chart

Now simply plot a stacked column chart and format series to reduce gap to ‘No Gap‘. The chart is now ready! You can download the workbook Comparison Column Stacked Chart.

Comparison Stack Chart

Previously in Custom Charts:

Custom Charts in Excel :: Heatmap Inspiration

Custom Charts in Excel :: Thermometer Chart

Custom Charts in Excel :: How to Reuse Without Recreating it Time and Again