Using Pivot Table Data for a Chart with a Dual Category Axis

A reader 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.

 

A B C D
1 Main Category Category Sub Category Defects
2 Mechanical Mechanical Gear 11
3 Mechanical Mechanical Bearing 8
4 Mechanical Mechanical Motor 3
5 Electrical Electrical Switch 19
6 Electrical Electrical Plug 12
7 Electrical Electrical Cord 11
8 Electrical Electrical Fuse 3
9 Electrical Electrical Bulb 2
10 Hydraulic Hydraulic Pump 4
11 Hydraulic Hydraulic Leak 3
12 Hydraulic Hydraulic Seals 1

 

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.

 

Pivot Table

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:

Dual Category Axis Pivot Chart

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:

Pivot Chart with Dual Category Axis

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.

Regular Dual Cat Axis Chart from Pivot Table Data

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

Peltier Tech Chart Utility

Comments

  1. Tony -

    Thanks for the comment. Pivot tables are really very powerful, flexible, and fast. They’ve received an upgrade in Excel 2007, but I haven’t played with the new ones enough yet to say anything profound. Pivot charts are powerful in their own right, but inflexible. They’ve also gotten an upgrade in Excel 2007, and even the ones I made for this post didn’t annoy me as much as I recall from the old days (perhaps the new color palette is more of an improvement than I thought).

    Stay tuned, because I have two more posts coming. One will show how to use dynamic names in the worksheet to update a chart when the pivot table is updated (this works in cases where the number of series does not change), and the next will show how to use VBA for cases when the names won’t do it.

  2. Well said! The only thing I may add is a description of what a Pareto chart is and how it is different than a regular column chart in case any readers aren’t familiar with the term. One common use of a Pareto is in Six Sigma for DPMO (defects per million opportunities).

    I am not a fan of the pivot table charting feature because of the same reasons you stated, but I am a huge pivot table fan! I will create a separate version with a custom chart like you created. The power of a pivot is the ability to refresh it to update the data while keeping the same visualization. If you have to recreate the chart, with all the customization every time, it will be very tedious.

    Great follow up post!

  3. well you can do the chart on getpivotdata information and than you can have complete flexibility

  4. Hi Anon,

    Could you elaborate on that?

    Do you mean make a chart, based on a table, based on GetPivotData? If so, how would that be dynamic (without VBA)?

  5. How can I create 2 pivotCharts that contain different data on one worksheet?

  6. Leila -

    I don’t really understand the question.

    A pivot chart by definition contains all the data from one pivot table.

    You can create the pivot charts (in Excel 2003 & earlier they are added as separate chart sheets) and change their location to objects on the same worksheet.

    The pivot table that a pivot chart uses can be located on any worksheet, and multiple pivot tables can exist on a single worksheet.

  7. hi,

    I wonder how we can add error bars? i tried once but only i could add error bars on first categories,but in second and third categories i could not see any error bars!!!

  8. Hello, great articles on Dynamic Pviot Graphs, are these spreadsheets downloadable anywhere?

  9. Only a few of these tutorials are accompanied by workbooks.

  10. Hi Jon,

    Is it possible to set the Y-axis to min and max value in the chart?

    Thanks.

  11. You can manually set the min and max to whatever you want. Right click the axis and choose Format from the pop up menu.

Subscribe without commenting

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites