Tony commented on the previous post, Chart with a Dual Category Axis, asking whether I’d use a pareto chart for this data. I commented that it almost was a pareto chart, since at least within each category the data is sorted from high to low. Then I got to thinking, if I put my data into a table, and use a pivot table to sort both the main category and sub category by number of defects, I would essentially have a pareto chart.

A pareto chart is a column or bar chart that lists items in decreasing order of their occurrence. A pareto chart may include a line chart series showing the cumulative occurrence of the items. The left-most items are most prevalent, so if your pareto chart shows instances of failure modes, you start fixing the left-most failure modes first.

Here is my data table. The main category data from the first column is duplicated in the second for reasons which will become clear when you try to use the category in two places in the pivot table.

Select the data and insert a pivot table. Put the Main Category and Sub Category fields into the rows area, the Category field into the columns area, and the Defects field (Sum of Defects) into the data area.

This data arrangement looks very much like what I set up manually in the last post. Now you can either make a pivot chart from the data:

which is somewhat inflexible in its formatting. I personally would like to remove some of the white space all around the chart and delete the legend. You can clean it up somewhat by hiding the pivot chart field buttons:

I still like to make a regular chart from my pivot data, as long as the pivot table isn’t going to change its shape too much. To do this, you have to select a blank cell distant from the pivot table when you insert the chart. Then you need to add each series individually and select its data, in Classic Excel either in step 2 of the Chart Wizard or in the Source Data dialog (use the Series tab in both cases), or in Excel 2007 using the Select Data command.

The advantage of the regular chart is that you have full control over all formatting, and over the data used in the chart. The disadvantage is that if the pivot data changes and the pivot table is refreshed or repivoted, the pivot table may cover a different range, and the chart will plot the original range. If the data changes a lot, you will have to (a) update the chart source data links manually, (b) design some clever dynamic range definitions so the links automatically keep pointing to the correct range, or (c) write a VBA procedure to update the chart’s links. This last option is pretty tricky to set up, but saves lots of time and frustration in the long run.

Second 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