Chart with a Dual Category Axis
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
Through appropriate arrangement of your source data, you can give your chart a dual category axis. This approach works with chart types that have an “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:
| A | B | C | D | E | |
| 1 | Mechanical | Electrical | Hydraulic | ||
| 2 | Mechanical | Gear | 11 | ||
| 3 | Bearing | 8 | |||
| 4 | Motor | 3 | |||
| 5 | Electrical | Switch | 19 | ||
| 6 | Plug | 12 | |||
| 7 | Cord | 11 | |||
| 8 | Fuse | 3 | |||
| 9 | Bulb | 2 | |||
| 10 | Hydraulic | Pump | 4 | ||
| 11 | Leak | 3 | |||
| 12 | Seals | 1 |
There are three 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.
First in a series
Posted: Tuesday, April 22nd, 2008 under Charting Principles, Data techniques, Example Charts, Formatting.
Comments: 3
Comments
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 Jon Peltier
Time: Tuesday, April 22, 2008, 5:11 pm
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.
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
Write a comment