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:

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

• Chart with a Dual Category Axis
• Using Pivot Table Data for a Chart with a Dual Category Axis
• Dynamic Chart using Pivot Table and Range Names
• Dynamic Chart using Pivot Table and VBA