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.

Example:
http://ceh.servehttp.com/upload/Classeur1.xls

(Here, I would like all the ‘failure rate this year’ to be a color, the ‘failure rate last year’ to be another and to have only 2 options in the legend, not 2*3..

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:

http://farm4.static.flickr.com/3440/3994464489_5698777cc9_o.jpg

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