Chart with a Dual Category Axis

Comment from Tony
Time: Tuesday, April 22, 2008, 4:16 pm

I agree that the top chart is the best layout for this data. The line chart leads me to think that time is on the x-axis or there is a relationship between the series, which is not accurate. Also, nice use of color!

One point; the y-axis scale is different in the bar chart (5) versus column chart (2). I am leaning towards the preference of the y-axis scale being two.

Would you suggest a pareto chart for this data?

Nice tip!

Comment from Robert Martim
Time: Saturday, May 10, 2008, 1:20 pm

Hi Jon,

“Funtastic” chart! It is a pitty I am color-blind and cannot appreciate the “nice use of color” that Tony mentions above. Nevertheless, the result is very clean and very easy to read.

The column chart gives a perfect picture of the data in a clea, clean and concise way!

Thanks for sharing this!

Rob

Comment from Mick Coleman
Time: Friday, June 12, 2009, 6:45 am

Jon,

is there any way of separately formating the x-axis ?
E.g. different font colours for Mechanical or Engineering (especially if you wanted to minimise the number of formating lines in the graph) ?

Comment from Shirlene
Time: Friday, June 12, 2009, 11:10 am

I have been fighting a problem with this category layout in Excel 2007. Jon, I know you are not happy with 2007 but I wonder if you know of a work-around. When doing the multi-level, or dual, category X-axis, it works great in XL2003. It also works great in XL2007…up to 100 rows of data. As soon as the 101st row of data is added, the group category (your Electrical, Mechanical, Hydraulic above) removes every other group.

Do you have any ideas on how to fix this?

Thanks,
Shirlene

Comment from Shirlene
Time: Tuesday, June 16, 2009, 5:56 am

Thanks for the reply. That’s what I suspected but thought that you might have found a work-around for it. I’ve tried and haven’t been successful. My user’s chart contains all 50 states as the outside level and 5 categories per state on the second level. This chart is not printed and this is what her boss wants so she is stuck doing it this way.

If you do find or hear of a work-around, please let me know. I’d really appreciate it and so would my user.

Thanks,
Shirlene

Comment from Shirlene
Time: Tuesday, June 16, 2009, 9:40 am

I agree but this is what the user wants. And, sadly enough, it worked fine in XL2003.

Thanks for your help, anyway, Jon. I appreciate your comments.

Shirlene

Comment from Simon
Time: Thursday, September 24, 2009, 10:28 am

Hi Jon,
Thanks again for your blog, I keep coming back to it to learn how to do stuff I want to do, or just to get new idea.

Before my main question, here’s a suggestion : Would it be possible to make a “printer friendly ersion” of the page? I like to print some of your pages because I dont have internet on my work computer and I dont need the menus on the left and other stuff.. Thanks :)

Main question:
I am trying to make a dual-axis very similar to yours, but I want to display two numbers for each category. For example, Mechanical-Bearing would have ‘failure rate last year’ and ‘failure rate today’. The problem I have is if I want to display the legend, it shows these 2 variables as many times as I have categories.

any idea?
Thanks
Simon

Comment from Stuart West
Time: Thursday, October 8, 2009, 10:51 am

Hi Jon,

I’m having a problem with a chart like the one at the top of the page. For chart formatting reasons, the top and bottom cells in each subcategory group have been left empty (so in your example, B2 would be empty, Gear, Bearing and Motor would be in B3 to B5, B6 would be empty and so on). This works great except for the last empty cell in the table, which Excel treats as a new category instead of a subcategory. Can you suggest any way around this?

Comment from Stuart West
Time: Friday, October 9, 2009, 4:39 am

Hi Jon,

Thanks for the response. It’s probably easiest if I show an example.

The last row of the table contains only blank cells, but always shows up on the chart as a new category.

Comment from Stuart West
Time: Friday, October 9, 2009, 7:50 am

Thanks! Somehow pressing delete wasn’t helping, but putting a space character in B31 sorted out the problem. That had completely stumped everyone in the office, so thanks again.

Comment from Tyler
Time: Tuesday, January 12, 2010, 4:02 pm

Hi Jon,

Thanks for this great site, it’s very helpful!

My question is this: I use multi-category charts in Excel 2007 on a regular basis. In many of mine, I have a large number of categories and each has only two sub-categories. In some cases the category names are long and the sub-categories are not, and I run into an issue where the category names do not wrap, resulting in overlapping letters with the next category. What puzzles me is that some of my charts DO wrap properly. I can’t find any setting for this. Do you know how I can cause my category names to wrap?

Thanks in advance,
-Tyler

Comment from Tyler
Time: Tuesday, January 12, 2010, 5:02 pm

Jon,

After more tinkering, I found that Excel wraps the categories if it has fewer than 14 of them in a single chart. If I add a 14th or beyond, the categories stop wrapping. Not sure if this is by design or a bug, but in any case I decided the simplest solution is to create a second chart and divide the categories across the two. This works for my purposes. Still a puzzling case though.

-Tyler

Comment from Tim Sorrell
Time: Wednesday, October 5, 2011, 5:24 pm

Jon… Using Excel 2007 and “mysteriously” my horizontal axis labels began wrapping character by character and now come out vertical. If I “stretch” the chart way out to the right, the labels begin to wrap more and more correctly. These charts were all working fine (wrapping correctly) two months ago when I last used them. Now all my charts are behaving in this strange fashion. It’s as if excel thinks there is some sort of “border” around each horizontal axis label, and when the labels get somewhat close to each other, it begins to wrap… and in my case, they wrap character by character. I have produced a .PDF file of the strange output. Could you take a look at the output and tell me if this is known bug that has been recently introduced into excel 2007? If can take a peek, How do I send the .PDF to you. Thanks very much for your help. My email is timsorrell@comcast.net.

Comment from frank
Time: Sunday, October 23, 2011, 10:40 am

finally solved my problem, searched the net for it (>12 hrs) , was told by friend that Jon Peltier is th best in excel take a look at his site, and finally found how to do this multi level category labels (or dual category axis)
needed that very much….
THANKS!!!

Comment from John Passmore
Time: Monday, March 19, 2012, 11:48 am

Jon

Have been trying to create your Defects chart in Access 2003 using VBA from a similar table as a prototype for something else I want to do later.

Not sure how Access interprets the various spaces you describe to let Excel know that there are to be Categories and sub-Categories. The trick is obviously to create the correct strings in the .SetData chDimCategories and chDimValues for each series so that they will be correctly interpreted as Cats and subCats.

Wonder if you could kindly provide me with any pointers please?

Comment from John passmore
Time: Wednesday, April 18, 2012, 4:43 am

Many thanks for responding Jon,

Have now had a fair bit of success in rendering different types of charts in Access subforms from data in tables using vba to address OWC (10 and 11) chartspace objects, methods and properties. Basically have to get approriate sets of tab-separated strings of category names and values from the tables to pass to the .Set Data chDimCategories and chDimValues for each Series added.

Regarding your Defects chart, I haven’t been able to discover the correct way to pass info to chDimCategories about my requirements for true Categories and subCategories. I had to simulate your mix of cats/subs with a list of of names where the subcats were simply at the same level as the cats but offset (right-just) to appear as a hierarchy – a bit of a fudge!

I want to progress to a chart similar to the Defects chart where cats might be months and the subcats, a repeated set of names – such as income, expenses and balance, for one or more accounts.

Wonder if you can kindly provide a lead on what the sequence of names should look like in the string of cats passed into .SetData chDimCategories such that OWC will recognise a hierarchy of cats, sub-cats (and even sub-sub-cats?) – have noticed somewhere that there maybe some limit on the numbers of repeated names allowed.

Many thanks – John

Comment from Henry
Time: Tuesday, April 24, 2012, 1:42 am

hi Jon,

Thanks for the great site. I have a situation where I wish to plot with more than just 2 categories of label… say

Tier 1 = Mechanical, Electrical, and Hydraulic
Tier 2 = as per your charts
Tier 3 = Country

Is this possible to do using fairly standard excel charts, or the only way is to adopt your “Individually Formatted Dual Category Labels” method? (The latter doesn’t give me the divider lines in the label).

Many thanks
Henry

Comment from Henry
Time: Wednesday, April 25, 2012, 7:11 pm

Hi Jon,

Thanks for your reply. I tried with that, but it didn’t work at first, so I thought I was doing something wrong, but now it’s all fine. Thanks heaps for your help.

Regards
Henry