Compare Annual Data in Excel Clustered Stacked Chart

How can you create a chart from annual or monthly data, and make the results easy to understand? For example, this table shows meat production in the UK, per season, over 2 years, in thousands of tonnes. See how to arrange your data to create an Excel Clustered Stacked Chart — like a clustered column chart, but with stacked columns, instead of normal ones..

SeasonChart01

An Excel clustered stacked chart could show the fluctuations in production for each meat type. Did production change much from 2002 to 2003? For all meat types? Are there seasonal differences in production?

Here is an example of an Excel clustered stacked chart, based on the sample data shown above. We’ll see how to create a chart like this, but first we’ll look at the two types of charts it’s based on — clustered columns, and stacked columns.

SeasonChart09

Create a Clustered Column Chart

To start, I’ll create a Column chart, using the first 2-D Column chart type, which is a Clustered Column.

SeasonChart02

This chart is a bit crowded, but lets me compare the meat types within each year/season, or follow one type across all the year/seasons.

SeasonChart03

For my presentation I want to focus on the meat types, rather than the seasons, so I’ll switch the rows and columns.

SeasonChart13

This makes the meat types the categories along the x-axis, and the seasons are now series names in the legend.

SeasonChart06

Create a Stacked Column Chart

The chart is better, but I’d like to compare one season’s production to another, or compare the 2 years, so I’ll try a Stacked Column chart instead.

SeasonChart04

The Stacked Column chart is less crowded, and lets me compare the total production for each meat type. If I squint, I can almost see the sections for each season in the Lamb stack.

SeasonChart05

Arrange Data for Excel Clustered Stacked Chart

The Stacked Column chart is close to what I want, but it doesn’t highlight the years and the seasons. I’d like to keep the Stacked Column chart, but with the years clustered, or the seasons clustered. Unfortunately, there isn’t an Excel clustered stacked chart type, so I’ll have to create my own.

The key to creating a Clustered Stacked chart is to arrange the data with:

  • blank rows where you want columns separated
  • data for different columns is on separate rows.

In my data I’ll put the 2002 and 2003 data in separate rows, with a blank row after each meat type.I also added a blank row before the first meat type, to create space at the left in the chart.

The new data arrangement looks like this:

SeasonChart08

Create an Excel Clustered Stacked Chart

I created a Stacked Column chart from this data, then changed the Gap Width to zero, and adjusted the series, to include the first and last blank rows. When it was finished, the Excel Clustered Stacked chart looked like this:

Excel Clustered Stacked Chart

The stack on the left of each pair is 2002 and the stack on the right is 2003. Now it’s clear that poultry production went up slightly in 2003, and the “Other” category went down.

Cluster By Season

To compare the meat types per season, I could change the data arrangement. Instead of four columns of 2002 data, then four columns of 2003 data, I could arrange the data by season, as shown below.

The first two columns have the Winter data, and the other seasons follow.

SeasonChart10

With this data arrangement, the Excel Clustered Stacked Chart  has 4 stacks in each cluster – one for each season.

SeasonChart11

With this arrangement, you can see that for all meat types, production is highest in the fall.

Excel Clustered Stacked Chart Utility

If you don’t have the time or patience to arrange your data and create your own Clustered Stacked Column chart, Jon Peltier has an Excel Chart Utility.

Reasonably priced, the utility will quickly pay for itself, because you won’t have to waste your time fiddling with data arrangements and chart settings. Just select your data, click the Cluster Stack Chart button on Excel’s Ribbon, select a couple of options, and click OK.

Instead of a long, painful process, the chart is created in a few seconds. A new sheet is inserted in your workbook, with data linked to your original data, and a shiny, new Clustered Stack Chart.

Peltier Tech Charts for Excel 3.0

Save

Save

You may also like...

46 Responses

  1. SapphireCate says:

    I found this post after 30 minutes of desperate googling. THANK YOU!!

  2. SapphireCate, you’re welcome! Glad you found it.

  3. courtney says:

    Debra,
    Thanks for posting this. I was helping a research student who was having difficulty creating paired stacked bars. Your tip on leaving blank cells worked like a charm.

  4. Courtney, you’re welcome! Thanks for letting me know that it helped.

  5. sam says:

    how to add sums, of all ckusters on each column of a STACKED chart ????

  6. excelbub says:

    Works great until you switch the Y-axis to Logarithmic which makes some of the series vanish.

  7. daveg says:

    Wow. Thanks! I was even able to follow your example and I’m an excel n00b. Thanks for taking the time to put your tip online!

  8. Thanks daveg, glad the instructions helped you!

  9. Steve says:

    You’re a life-saver – been after something like this for ages! Thank you!

  10. Kevin says:

    Thanks for this how-to. Other links from Google were useless.

  11. Thanks Steve and Kevin, glad the instructions helped you.

  12. Carlene says:

    Hey Debra – thanks so much for posting this. I just got asked how to do this by a user, a quick Google search and I found this right away. Nice easy instructions, this worked a treat with my user’s data and they are very happy.

  13. Carlene, you’re welcome! And thanks for letting me know that it helped you and made your users happy.

  14. Tammy says:

    THANK YOU! I was so close it on my own and your instructions told me exactly what I needed to do.

  15. You’re welcome Tammy! Glad the instructions helped you solve the problem.

  16. Sandy says:

    Thanks for posting. I got it.

  17. Manish Khurana says:

    Hi,

    I tried these steps but surprisingly it doesn’t stack up as desired after creating those spaces as explained in ‘Create a Clustered Stacked Chart’ section.

    Could you please help?

    Many thanks in advance!

  18. Grateful says:

    This has caused me way more excitement and happiness than any SpreadSheet ever should.

    Thanks

  19. Janet says:

    OMG this was so helpful. I’ve been trying to figure out how to do this for years. Thanks for the great tips.

  20. Teddy says:

    Your tips were incredibly easy and on the point … got me the results I wanted in 10 minutes. THANKS!

  21. Elena says:

    THANK YOU! This was EXTREMELY helpful, and the instructions were very clear – along with the visual aid. Great tips with very clear explanation.

  22. MP says:

    Thanks VERY much for this extremely helpful post!You saved me.

  23. Tammy M. says:

    Thank you so much for the great tips! Your instructions and examples were amazingly clear and saved me from hours of frustration. If I get accepted to this conference, you are going in the acknowledgments!

  24. Tom says:

    Great information, too bad Microsoft takes a step backwards every upgrade. You kids will not remember all the prior versions of excel, before wizards, I do. In those days you could figure it out, excel was intuitive. Now you have to rely on lame “HELP”. Thanks, tips like this tell me some people are still thinking for themselves – good work.

  25. Silvia says:

    This is exactly what I was looking for! THANK YOU so much!!

  26. Silvia says:

    PS – what worked for me was seeing the data structure layout – I did not even need to read the instructions. A picture is worth a 1000 words :) Thank you again!

    I also need to give credit to Peltier site – it came up in my search, and then I knew to add ‘cluster’ to my search (I had used parallel before) – once adding that, voila!

    I only have a handful of data points, which I’m compiling from various sources – hence it was not complicated to arrange it in the required format. But I totally see the value of the Peltier add-in if one has massive data processing needs

  27. Manaswita says:

    Thanks…..it was really helpful…

  28. Vinay M says:

    Hi thanks, this was really useful and simple to understand.

  29. Pritha says:

    THANK YOU!!!!!! Very helpful

  30. MissMers says:

    This was very helpful as I had been wanting to create this type of graph for a variety of reasons in my work. Unfortunately, after closely following these instructions numerous times, I could not figure out why the graphs weren’t working. I finally determined that the X-axis did not like a date format! Once I changed my X-axis to a “text format”, the columns separated as desired. Success at last!

  31. aNNaBeLLe says:

    Thanks for posting this!!!
    And MissMers, thank you for mentioning the issue with the date format in the x-axis. I couldn’t figure out why the stacked clusters weren’t working for me. That fixed it!

  32. Monalisa Chakraborty says:

    Glad that I found this instruction. I have to prepare room utilization stacked chart by each physician. Any advice. Will a stacked chart work.

  33. Christian H says:

    Thanks for this post, worked perfectly. Thanks to everyone who posts this kind of thing especially those of you out there that post VBA, I’ve taught myself VBA and can now do everything I want just from internet forums. God bless the internet and kinf people.

  34. Lelala says:

    Thanks, you actually saved my life!!!

  35. Heather says:

    Thank you so much! Almost everything is working perfectly, but I can’t figure out how to set the Gap Width to zero. So there is a space between the columns I would like to be clustered together. I’m working on Excel 2007 on a Mac. Any help is much appreciated.

  36. Heather says:

    Nevermind! I just found it. Thanks again! This is awesome!

  37. Heather says:

    Now I have another question. Even though I have a blank row above the first row of my actual data, there is no space created to the left of the first column. Suggestions? Thanks again!

  38. Jon says:

    This was very helpful. It is 2013 and this is still a tried and true solution!
    Cheers

  39. Yo says:

    many thanks dude

  40. Matt says:

    This was extremely helpful. Thanks a million!

  41. Nina says:

    Big thanks for posting thesу tips, especially the ones about cluster stacked chart – you saved me during a very hectic & nervous morning!!!

  42. Paula says:

    Thank you very much for your posting; I was trying to plot a set of deviations from average and was struggling with the lack of clustering tools. Great trick to get the cluster charts right!

  43. Watee Taksana says:

    for 2 weeks before I learn it call cluster….

    I need to learn more in English

  44. Tonya says:

    Thank you so much for this post…. I see it was originally posted in 2009 and its still making the rounds which i take to mean its a good one.
    it did exactly what i was looking for. I’m just having an issue like some people above, trying to get my columns to group together?

    I have 4 products, 2 years, 12 months. I would like the 2 years to group together by month… so Jan14 – Jan15 – Feb14 – Feb15 – Mar14 …etc… side by side (checked – worked great with the instructions) and then the product stacked (another check mark, whoo hoo)

    Now all i need to do is put Jan14Jan15 side by side with no gap…
    I changed the axis to text but it didn’t do anything.
    Any suggestions?

  45. Lauren says:

    You’re a genius! The tip about leaving a blank row is just fantastic! Thank you!!!!!!!!

Leave a Reply to Tammy Cancel reply

Your email address will not be published. Required fields are marked *