Nested Pareto Chart in Excel With QI Macros
My friend Jack Revelle asked me if QI Macros could do “nested Pareto Charts”. Surprisingly, I hadn’t heard that term before, but I thought I knew what he was asking about—drilling down into the “big bar” of a Pareto chart to determine if there is a Pareto inside of the big bar. He explained what he wanted and I realized I’ve been doing it for decades without the label “nested”.
Nested Pareto Charts with QI Macros
This most commonly occurs when analyzing data using PivotTables, because it’s easy to drill down into the data. Here’s an example using QI Macros Test Data/pivottable.xlsx. First, select the Line and Defect headings:
Then use QI Macros PivotTable Wizard to summarize the data.
If we switch the Line and Defect fields in the PivotTable, we get a table that's easier to analyze.
Then select line1-3 and grand total of defects and use QI Macros to draw the first level of Pareto chart:
Since Line 3 is the “big bar” select the type of defect and number of defects for Line 3 and use QI Macros to draw the second level (i.e., nested) Pareto Chart:
On Line 3, Folded Flaps account for 39.5% of total packaging defects. This could become the head of the fishbone for root cause analysis.
Alternate Nested Pareto Chart Development
We could also work it the other way, starting with Type of Defect.
Select the type of defect and Grand Total:
Use QI Macros to create the top level Pareto Chart:
Folded Flaps is the “big bar” of this Pareto chart, closely followed by bent/damaged flaps and carton will not open. Select Folded Flaps and Line1-3:
Then use QI Macros to create the second level (i.e., nested) Pareto chart:
Line 3 accounts for 79% of Folded Flap Defects. We can use this as the problem statement for the fishbone diagram.
We could also have selected most of the data in the PivotTable (excluding the Grand Total row at the bottom to create multiple charts showing nested and top level Pareto.
Use QI Macros to create Pareto charts of all selected columns:
Line 1 and 2 both have problems with Bent/Damaged Flaps and Carton will not open. Both of these “big bars” could be the head of a fishbone diagram. Line 1 might work on Bent/Damaged while Line 2 works on Carton will not open.
Line 3’s problems would show up in the second two graphs:
The Nested Pareto
That’s the essence of a “nested” Pareto chart. Draw the high level chart and then take the data for the “big bar” and drill down to create lower levels of Pareto charts. If you have data for three or even four levels, keep going. When you narrow your focus using nested Pareto charts it makes it a lot easier to find the root cause.