Through the appropriate arrangement of your source data, you can give your chart a dual category axis. This approach works with chart types that have a “Category” type category (X) axis, that is, line charts, column charts, and bar charts. The chart below shows defect rates in several different components, which are grouped into a smaller number of categories.
The data arrangement is shown in the following table:
There are three main categories: Mechanical, Electrical, and Hydraulic. These categories are listed in the first column, and also in the first row. The second column shows the sub-categories, for example, Gear, Bearing, and Motor under Mechanical. The cells filled with yellow should be completely blank. The first two cells in the top row are blank to instruct Excel to use the first row as series names and the first two columns as category labels. The blank cells under each main category in the first column tells Excel that each main category applies to several subcategories. I used separate columns for the three categories so that each is plotted in its own series, making it easier to format each major category separately.
To make the chart, select this entire data range and insert a stacked column, stacked bar, or unstacked line chart. (Note: you could use clustered column or bar charts, but then you would have to format each to use an overlap of 100% between series.)
The column chart type is probably the best selection (see top of page), as long as there are not too many categories and subcategories. The line chart (above) gives more emphasis to an illusory relationship between points by connecting them with lines. The bar chart (below) works almost as well as the column chart, but Excel provides no way to orient the major categories horizontally.
This can be extended beyond two levels to three or more. I’ve used it in practice with up to five levels. There is a risk that too many levels will clutter the chart.
Jon Peltier says
Tony – I included the line and horizontal bar versions of the chart almost as an afterthought, so I didn’t notice the change in the Y axis scale of the bar chart. I agree that it looks better, more precise maybe, using an axis tick spacing of 2, so I made the change and uploaded the new chart.
If you notice, this is almost a pareto chart, at least within each category the data is sorted from high to low. You could set up an Excel pivot table (I feel my next post coming on) that would sort both outer and inner categories by number of occurrences.
Tony says
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!
Robert Martim says
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
Mick Coleman says
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) ?
Jon Peltier says
Excel doesn’t allow for individually formatted axis tick labels. You can fake it using dummy series, as shown in Individually Formatted Category Axis Labels. For a dual set of labels it requires two dummy series and some cleverly laid out text labels.
Shirlene says
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
Jon Peltier says
Shirlene –
This is what they call a “known issue” in Excel 2007. I have not tried to find a workaround for this, since in general more than 100 categories make an axis unreadable. I think the 100 category limit plays havoc with single lavel axis labels too.
Shirlene says
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
Jon Peltier says
So there are 250 categories? I can’t think of a good way to do that!
Shirlene says
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
Jon Peltier says
Shirlene –
It just occurred to me that Excel 2007 has a problem if there are more than (I think) 99 category labels. Above this number, you no longer have any control over how the labels are displayed.
You might try to use a alternate approach, Individually Formatted Dual Category Labels, which uses data label on invisible series instead of the built-in axis labels. Excel will put all of the labels wherever they go, no matter whether they overlap. So it will get around Excel 2007’s problems with too many category labels.
Jon Peltier says
Hi Simon –
If you don’t want different formatting for the different main categories (Mechanical etc.), then move the ‘this year’ data from columns E and G into column C, and the ‘last year’ data from F and H into D, and only use columns C and D for your Y values.
Also thanks for the printer friendly suggestion. I have certainly considered this option, but haven’t gotten around to figuring out how to implement it. One of these days.
Simon says
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
Stuart West says
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?
Jon Peltier says
Stuart –
Where is the last empty cell in my table (corresponding to yours)? Do you mean A12? Make sure the cell is really empty, and doesn’t contain for example a stray space character.
Stuart West says
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.
Jon Peltier says
Stuart –
I see. In both Excel 2003 SP3 and 2007 SP2, if row 31 is completely blank, or if cell B31 contains a space character, the last item is a subcategory. If A31 contains a space character or a formula that returns “”, Excel recognizes it as a non blank cell in column A and gives it its own category. Select cell A31 and press Delete, and it should fix the axis labels.
Stuart West says
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.
Tyler says
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
Tyler says
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
Tim Sorrell says
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 [email protected].
Jon Peltier says
Tim –
I don’t know of a specific issue caused by a recent update to Excel 2007, but I’ve heard of several inconvenient problems. I have no suggestions. Labels (in fact, charts in general) are not handled well in Excel 2007. I avoid 2007 as much as possible, using 2003 for my own important work and 2010 for most of my outward-facing work.
frank says
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!!!
John Passmore says
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?
Jon Peltier says
John –
I don’t know about charting in Access. But in Excel, you can’t pass an array as a delimited string and have the dual axis come out right. You need to use a worksheet range.
John passmore says
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
Jon Peltier says
John –
As I stated in my previous comment, it is not possible to pass anything other than a worksheet range that will produce a category axis with categories and subcategories. You cannot pass a string that will do this, nor can you pass a 2D array. At least I haven’t been clever enough to find a way.
I’ve only dabbled with OWC, but my suspicion is that they also will not accept a string or array to produce a multi-level category axis.
Henry says
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
Jon Peltier says
Henry –
You can have more than two tiers. In my example, I have two columns of labels. For three tiers, you would use three columns of labels, arranged using the same logic.
Henry says
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
Lewis says
Hi Jon,
I love the blog. You’ve been a huge help to me over the years but I’m stuck on this multi-level axis charting problem which you may be able to help with.
I am plotting daily data in a line chart. If I just use single level labels I can group the dates by month (just format axis mmm-yy and set interval to 1 month). However, if I take it a step further to split out the years onto a second level I can no longer group by month. The interval then makes me see repeated months within each year bucket and there are no longer the same options in the Axis Options dialog. Is there a workaround?
Thanks!
Cheryl says
I”m trying to do the dual category horizonal axis, but the B Column in Excel automatically assumes that colum is the data, not a second category name, I’ve tried to delete the title but it doesn’t work. How can I tell it that the second column is still category names? Thanks.
Jon Peltier says
Did you delete the title after creating the chart? It only helps before creating the chart. Once the categories are set, you need to actually change the source data using the dialog.
Right click the chart, choose “Select Data”, then click the Edit button for Horizontal (Category) Axis Labels, and make sure the selection includes column B. Then delete the series that uses column B.
Cheryl says
Thanks Jon.
I realized that I had to create the charts in Excel and then copy and paste (with link) into my word document. I’ve been in the habit of creating charts right in Word because I usually use the simpler variety. Got it working now!
Anonymous says
Thanks very much for this article – it was very helpful.
Lieke says
Hi Jon,
Thanks a lot for this useful explanation. I am working in excel 2010 now and I am able to make the chart I want to this way. I only have 1 problem: when I try to add error bars with specific value, this only works for 1 category for me (the first one). In the other categories, the error bars do not appear in the chart. Do you maybe have a solution for this problem?
Jon Peltier says
Lieke –
Upload your worksheet to Dropbox or similar service, then reply here with the link.
Lieke says
Hi Jon,
This is the link to the file:
https://dl.dropboxusercontent.com/u/45724370/dual%20axis.xlsx
Danielle says
Hiya, I have created a number of graphs with 2 multi level categories on my x axis, however the problem I am having is some of the labels are overlapping and unfortunately becoming too tricky read. Is there any guidance you can give me on this matter please? It is the level closest to the x axis title that is the problem by the way, the level closest to the graph seems to have sorted itself out by placing the text below, whereas the other label is keeping them all horizontal. Thanks in advance.
Alex says
Hi Jon,
Great article – helped a lot already, thank you!
Just one open question: In your very last picture, is it somehow possible to align the “main label” text Mechanical/Electrical/Hydraulic horizontally as well?
All the best,
Alex
Ryan P. says
Great post, Jon–thanks! In your first chart, I’m playing with trying to get no gap between the bars in each main category, but a gap between the categories (so Mechanical would all be together, then a gap, then Electrical, and so on). Even when I think I’m formatting just one of the main category series, it applies the change to all the bars. Any suggestions? Thanks again!
Jon Peltier says
Ryan –
All columns in the chart have the same gap width. You can insert a row into the source data, with no values to plot, which will provide a gap between groups of columns. But this will mess up the appearance of the dual label axis.
Ryan P. says
Thanks for the quick reply, Jon! I had actually thought of that after reading another one of your posts, and it turned out to work very well. You’re right–the labels get a little out of whack since the main category label bleeds into that blank space, but for the data/labels we had, it actually wan’t very noticeable (things wrapped nicely, and it looked pretty good). I appreciate it!
Aaron says
Does anyone know how to do this with powerpoint?
Cheryl says
Hi Aaron,
You could create it in Excel, and then copy and paste into powerpoint…I had trouble trying to do it directly in Word, so I’m guessing it’s the same with powerpoint.
Hope that helps!
Jon Peltier says
No big deal to do directly in PowerPoint, if you don’t want to do it in Excel and paste the chart into PowerPoint.
Insert a chart. PowerPoint displays a worksheet with some dummy data in front of the chart.
Replace the dummy data with your own data. Note that you can’t have empty cells in the top row. Cell A1 contains ” ” ( one space character) and cell B1 contains ” ” (two space characters). Resize the highlighted range so it includes all rows and columns of your data.
Adjust the shaded blue range so it includes just the Y values (columns C-D-E in this example).
Select just one series, and adjust the shaded purple range so it includes all columns of the X labels (A and B here).
Format the series so the Overlap is 100% and Gap Width is not so wide (I used 75% here).
Cheryl says
Thanks, Jon.
Great to know how to do it directly in PPT or Word.
Alina says
Hi Jon!
Thank you for the useful blog!
I have the same problem as Lieke had:
when I try to add error bars with specific value, this only works for 1 category for me (the first one). In the other categories, the error bars do not appear in the chart. What was the solution to that problem?
Thanks!
Jon Peltier says
Just as the columns use the full range of cells for their values, including blanks (darker blue and orange fills below), so must the error bar custom values use the full range of cells, including blanks (lighter blue and orange fills). Lieke had only used G6:G9 for these values, which lined up with the blanks in the series data in E2:E5. Using G2:G9 fixed the problem.
Naveed Saeed says
Thanks a million,
Although it wasnt being done auto in powerpoint so i used manual editing of formula but work done successfully.
Emanuele Uboldi says
The solution proposed at https://peltiertech.com/chart-with-a-dual-category-axis/#comment-628536 does no longer work.
Do you know is there is any workaround to create a native powerpoint chart with multiple category headers?
Jon Peltier says
I just tested this technique, and it does still work, even in the latest release of Microsoft 365.
Which part of this technique causes you trouble? Is it the resizing of the colored highlights in the worksheet? I would suggest making a few dummy charts in Excel first to practice, since it’s trickier in PowerPoint.
Emanuele Uboldi says
so odd, it does not work on my computer – it might be though it’s linked to a corporate setting/version used. Thanks for the quick turnaround!
Jon Peltier says
I don’t think there is any version or setting issue (including IT interventions) that would prevent it from working.
I will note that working with the data range in PowerPoint is trickier than in Excel. The first time I tried it while testing, I broke the chart and had to start over.
Jon Peltier says
Emanuele sent a video of their attempts, and I was able to see the problem.
To resize the data range, select the whole chart, then drag the highlights in the data sheet to adjust the Y values (the X values and series names will also adjust during this step).
To expand the X values (category labels) to multiple columns, select just one series first (any series, and the change will apply to all series).
Ari says
Hi Jon,
I’m reaching out to seek your advice and see if what i am doing to create multi-category dynamic charts is possible or if there is a different way of doing this?
I posted a question on reddit earlier today before i stumbled upon your website. If you get a chance, could you please shed your thoughts? Very much appreciate your insights, i have been banging my head on the desk trying to figure this one out.
The link to my question is here. If you prefer, i could re-post the entire contents in here as a comment, pls let me know.
https://www.reddit.com/r/excel/comments/1dj7as9/dynamic_charts_with_multiplecategories_issue_with/
Jon Peltier says
Ari –
I’ve responded to your reddit post. I don’t know why your chart didn’t work, because it worked for me using your formula. I adjusted your example a bit, creating a Table from the input data range and slightly modifying your formula.