Fixing Your Excel Chart When the Multi-Level Category Label Option is Missing.

I am so happy to help my fans, and I saw a recent comment on an older posting of mine and I thought I could help.  The Excel user was having problem getting the chart solution to work with their data.  You can check it out here:

Case-study-solution-mom-needing-help-on-science-fair-graphscharts

 

Essentially, the chart that the user was looking for is to create graph that has both a main category label and sub-category labels.  Like this:image

This is a special feature in Excel Charts, but ONLY if you set up your data in a certain way.  Here is what the user was said:





“Hi, I would like to know whether the Multi-Level Category Labels are available for the Mac 2011 version of Excel.
I’ve been trying to do something similar to the second option you showed in this video:
http://www.youtube.com/watch?v=FuSI0XDJynY

But I can’t get the Multi-level category option to show up; it just reverts provides the regular max/min/interval axis options.

Do you know whether it’s possible to get Multi-Level category labels on Excel for Mac? How would I do that/How is it different?

Thank you so much for your tutorials! They are so helpful.” –MacUser

 



So he was having a problem with the getting the Multi-level Category Labels to work.  When he right clicked on the horizontal axis and selected Format Axis, he was seeing this:SNAGHTML484866

instead of this:SNAGHTML48e9a4

Notice that the Multi-level Category Label check box was no longer an option.  Based on his description, I determined that the problem was that he wasn’t using text for both the Main Category and Sub-Categories.  But as you will see in the video and the tutorial below, you won’t have to use just text.  Check it out below.

 

The Problem





I created a chart in Excel and can’t find the Multi-Level Category Label Option.  Why is it Missing and how do I get it back?

 

Testing the Solution

I believe that the issue is that the user had numbers in their sub-category.  But lets test out each combination and see what we get.

 



1) Text in Main Category and Text in Sub-Category

If we put text in both the main and sub-categories, it works perfectly.

image

 

2) Text in Main Category and Number in Sub-Category





If we put text in the main category, but put any kind of number (numbers and dates) in the sub-category, then it doesn’t work Sad smile.image

What Excel is doing is that it thinks the sub-category is just another series.  This is not a deal killer as you can stub in text instead of the numbers and then after you make your chart, just copy and paste the number data back into the chart and problem easily solved.

 

3) Number in Main Category and Number in Sub-Category

Number Number doesn’t work either as Excel thinks all three columns are data series and not category labels.



image

Like above, this is not a deal killer if you stub in text data instead of the numbers, then create your chart and then copy/paste the original dates or numbers back into your data series.  As you can see here, I am creating a chart with text in the main and sub-categories.image

then if I copy and paste the date and numbers from the chart series range that didn’t work image

to the one that did work, it will work fine as an easy fix as you see here:image

 





4) Number in Main Category and Number in Sub-Category

This is the REAL KEY to using Multi-Level Category Labels in Excel Charts.  The trick is to always make sure that the column of data next to the first data series should be text.  If you do that, then when you highlight the data series and insert a chart, Excel will do it all for you.

image

 

5) Bonus time – An alternate way yet again!



So if you don’t want to fake your data and copy/paste like I describe above, here is the better way to make your chart using your original data set.  It is almost like the Excel 2003 wizard.

a) Highlight only your data series (C1:C7)image

b) Insert 2-D Column, Line or Area Chart.  It will look like this:image

c) Select the Chart and go to the Design Ribbon and press the Select Data button.  And from the Select Data Source dialog box, press the Horizontal (Category) Axis Labels “Edit” button.

SNAGHTML61d347

Then highlight Main Category and and Sub-Category labels, like this.

image

Don’t let Excel do everything for you Smile sometimes you have to take the reigns.

 





Check out the quick video tutorial here:

 

You can download a Sample File here:

Excel-Chart-Multi-Level-Category-Label-Options-Missing-Sample.xlsx



 

Steve=True





8 COMMENTS

  1. There is another way of fixing a chart with Multi-Level Category Labels: Delete the column headers from the Main Category and Sub Category and then re-chart the data. It appears that the column header is causing Excel to confuse the label data for series data.

  2. Can this multi-level organisation be replicated within the Legend? So if the y-axis is a proportion (i.e. 0 – 1) and the x-axis is a time variable such as age, then what I want to be able to do is draw a graph that permits the comparison of ten groups within the same graph. However, five of the groups are Male and five are Female, so I would ideally like to have the Legend reflect that without haveing to engage in redundant labelling.

    • Hi Jamie,

      I am not sure exactly what you mean. You can delete any extra legend entries. Just select your chart, select the legend and then select the legend entry to delete. Then press your delete key.

  3. I was trying to use this option for time data. I had one column for the date and another for time. It worked properly up to a point: I couldn’t select the time intervals. As a result instead of, say, every 6 hours, it chose 6 hours and 50 minutes. Ugly!
    I tried changing both the “interval between tick marks” and “interval between labels”. Neither seemed to have effect.

    • Hi Gerald, I don’t think you can mix Multi-level Category Labels and Time Series. Once you go Multi-Level, you are grouping in a text type format, not time based. Good luck.

  4. Hi Steve,

    Is it possible to nest another level into this? For example say you were looking at sales in 2016 vs 2015 for each of those items. Could you do Category (Fruit/Veggie), Product (Orange, Bananna, etc) and then two columns within each of those (2015 and 2016)?

    Thanks!

LEAVE A REPLY

Please enter your comment!
Please enter your name here