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.
By far the biggest change in Office 2007 is the complete redesign of the user interface. After a decade or more, the familiar menubar and toolbars are gone, replaced by a large monolithic Ribbon splayed across the top of the application window. The user’s first reaction upon seeing this wonderful new interface is “Wow!” This is followed soon after by panic, as the user desperately tries to figure out where all the old controls have gone. They all are still there, and eventually most are found. Some are pretty obvious, and the Ribbon was designed to make them so, but others stubbornly stay hidden. The “Copy Picture” command in “Classic Excel” (97 through 2003) was somewhat obscure, requiring the user to hold Shift while selecting the Edit menu. This was kept obscure; it took me, a seasoned Excel user, a month to find it, cleverly buried under the Paste dropdown button.
