Custom Charts in Excel:: Comparison Stacked Column Chart

Posted: May 21, 2013 by MaxFraudy in Excel
Tags: , ,

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

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

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:

ata - Comparison Stack Chart

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

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

  1. Harindra says:

    Very nice concept.. can you post about the waterfall chart?

  2. Just wanted to say, I found several pages trying to explain this method, but yours made it beautifully simple to understand and implement. Thanks

  3. James says:

    Perfect Solution. Thanks for including the excel file to download. One additional point I discovered, the month’s must be text formatted or else excel plots them in date order rather than side by side comparison as desired.

Share your thoughts/feedback

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s