Here is a very simple solution to a common Excel Charting question when a user wants to graph “Three sets of data criteria on a chart”.
Here is the basis of the question at hand:
“I’m not sure how to plot my data. I have three items I want to factor in and chart on it. Vertical titles are in A3:A5 for types, Horizontal titles are B2:D2 for locations, numbers are B3:D5, this is for January. That looks great and I get the chart to look the way I want. Now I need to add additional months through to December. I don’t know how to do that without copy/paste the current data 12 times, and just add a monthly title. I want continuous data to show all three axis or data; not 12 separate blocks of data. Do you know of an easy way to accomplish this?”
Based on this description, here is how I envision the Excel user charting the data:
The Excel User has charted the data for January with Location in the horizontal axis as input in Columns B – D. Also they have broken down each location by type in rows 3 – 5.
But now because of their data setup, the are not sure how to add more months of data for February – December. They consider this a 3rd set of criteria for the chart and it causes much confusion.
Well there is an easy solution that Excel already provides but it will require you to change the way you layout your data. That easy solution is Multi-Level Category Labels for the horizontal axis.
So lets see how we need to rearrange our data to create this 3 Criteria Chart in Excel.
We will need to create our months and locations in columns A and B. In the chart data range below, you can see that I have January in Column A and then the 4 locations in column B starting at the January label. Next you will see February further down in Column A and also the same locations are repeated in Column B. This is repeated for March or as many months that you need. The cells A3:A5 are all blank so that the first 4 locations are then grouped under January. Likewise the cells below February and March are also blank so that the locations are grouped accordingly.
Then if we put our Types in columns C:E, are chart range is all set for our data.
Now all you have to do is create your chart by highlighting the range of A1:E13 and Excel will do its magic.
You can see the setting that Excel will check for you by double clicking on the Horizontal Axis and then checking out the Axis Options. In Excel 2013, they have buried it under the Labels grouping below the Axis Options.
Multi-level Category Labels are very powerful and you can stack more than 2. So that means when you are creating a Column or Bar Chart, Stacked or Not, you can chart more than 3 sets of criteria. You can keep stacking them and display 4 sets, 5 sets or more of your Criteria in your Excel Chart. I was able to chart 25 levels (way to many to actual display on a chart or graph) so I imagine that the limit is quite high.
Here is a sample chart based on 5 sets of criteria including the multi-level category and the location.
Hopefully this has helped you organize your data so that you can create a chart for multiple criteria breakdowns.
Check out these other posts that show you other examples of Multi-Level Categories on the Horizontal Axis.
Watch the Video Demonstration Here:
Download the Free Sample File Here:
Do you like Multi-Level Category Labels? Do you use them? Are they helpful or a hindrance? How many levels have you stacked in your Multi-Level Categories? Let us know in the comments below.
Best help ever! I have minimal experience with creating graphs and charts. I followed your directions and it worked perfectly. Thank you.
What a great comment. Thanks!
My chart is to show bags issued out of stores and bags used by production. and then i would like to add the difference in the two figures.
below is my data
2016-17 Stores Production diference
NAMFEED PLAIN LARGE 12500 10561 1939
NAMFEED PLAIN STANDARD 88500 80966 7534
I would recommend this setup and chart. See image:
Also, note that to get the Tick Marks to set right, I have a space entered in the cell above “Standard”.
Good Luck. Steve=True
Your site and example data with charts is exactly what I needed!! I needed my 6 data sets to make 3 stacked columns within one excel chart. Once I saw your examples, I made my data match yours and from there everything worked perfectly! Thank you so much!!
Awesome.Thanks for letting me know it helped. Much appreciated.
Hi, I have 3 data points that I am trying to fit in one chart. By month and by location, I have Headcount, # of hires, # of Terminations & Turnover %. Any ideas how to organize the data to get it to display in a useful format? I’ve tried numerous ways and am coming up empty handed. Thanks!
Hi Jennifer, my first question is that your Turnover percentage does not relate to the other 2 data points, so showing that percentage may be an issue in my mind as the % turnover would be terms/total number of employees. So you may create a correlation to the other data points showing and confuse the reader. Next, What do you want to be on the very bottom axis? Months or Locations?
Sir, thanks for you examples on excel chart , i am pretty much closer to what i need for publishing in my research publication , but i am not able to fix two variable at the same position ,while they should look attached but thin walled. Your Month data is my no.of chromosome (about 10 in number). and for each chromosome i have to present data of two individuals, which will have stacked data of 6 values one over the other ,like you have shown type 1 , type 2 etc. But i need thinner columns of two locus attached to each other.
Hi Pallavi, did you get it figured out? You can add more columns. Also, check out this that may help you: https://www.exceldashboardtemplates.com/how-to-easily-create-a-stacked-clustered-column-chart-in-excel/
though it looks simple , it helped me a lot
so glad it helped thanks for the comment