How to Draw Pareto Charts of Text Data

Often, you will need to summarize text data to be able to draw a pareto chart. What do you do if you have data that looks like this?

To summarize this data with the QI Macros, just click on the column heading (Scrap Description) and choose:

• Pareto Chart if you have QI Macros 2011 or later
• PivotTable Wizard from the QI Macros Data Transformation toolbar if you have QI Macros 2010 or earlier

XL97-2003

XL2007-2010

The QI Macros PivotTable Wizard will summarize the text data:

Now, just select the labels and numbers and draw the pareto chart:

Its simple, but be careful, counting text fields may not yield the most insightful pareto charts. In this case, what's most important, the number of times each problem occurs or total scrap?

PivotTable of Scrap

Jay would like you to learn PivotTables, which he considers the golden key to million dollar Money Belt improvement projects. If we expand our view of the data, you'll see that it includes actual scrap weights:

If we select the description and scrap weights headings and use the PivotTable Wizard to summarize the data, we get the sum of scrap by description:

This yields a more insightful pareto chart:

In this pareto, we discover that surface cracks, which was ninth when counted is actually first in causing scrap. If our goal is to reduce scrap, not the frequency of problems, this is the chart to use. The scrap description of "Tracking" which was first when counted is actually tenth and a very small volume compared to problems 1-9.

As you can see, it's easy to use the QI Macros PivotTable Wizard and Pareto Chart macro to turn text into pareto charts. What insights can you glean from your data using these two tools.