a Pareto Chart in Excel 2013 isn’t a simple press of a button, as one might think. It isn’t terribly difficult, but there is some arrangement of the data needed before the charting can begin. This step-by-step guide will help you through it.

Step One : Defect Code Identification

You’ll need three columns for now, one as a counter, one with your defect codes and one for the number of occurrences of each type of defect. Like so:

The counter, by the way, is totally optional – I’m using it here to help make some of the sorting clear. Feel free to leave it out if you understand the nuances of sorting columns in Excel.

Step Two – Order by Occurrence; Descending

Arrange the defect codes in descending order of numerical Occurrence – Column C in our case.

Be sure to select all columns prior to the actual sort, when asked (as above).  Now our chart looks like this. The “Index” column should be all out of sorts, now:

Step Three – Add a column for cumulative occurrence

Basically a formula to add the cell to the one above it, getting larger all down the column – like in the capture below:

Hit ENTER, then – to duplicate the formula for the cells below it, grab the corner of cell D3 and drag the box to the corner of cell D10, as shown:

Your numbers should look like the image below, if it doesn’t then check your formulae.

• D2 should be “=C2” [no quotes please!]
• D3 should be “=D2+C3”
• D4 s/b “=D3+C4”
• D5 s/b “=D4+C5”
• D6 s/b “=D5+C6”
• D7 s/b “=D6+C7”
• D8 s/b “=D7+C8”
• D9 s/b “=D8+C9”
• D10 s/b “=D9+C10

Step Four – Calculate Totals, add Percentage

Calculate total of numbers shown in the Occurrence column and add a column for Percentage.

First, add a sum to column C; Occurrence

Then add a formula to column E that uses the total you just made as shown below:

Make sure your totals match (D10 should be the same as C11)

Go ahead and format that Percentage column to be a percentage with two decimal places, like so:

NOW – The numbers should appear as below (if you’re using this example as a first run – which is not a bad idea, right?)

We’re ready to make a proper Pareto Chart

Step Five- Select Columns

This may be a little tricky, so follow closely. First, using the mouse, select B1 to C10 (two columns), THEN – Without Clicking Anywhere Else – hold down the CTRL key AND using the mouse, click E1 to E10… You should have two selected areas now that look like this:

With those areas still selected click “Insert” / “Insert Combo Chart” then the centered chart type pictured – it is called “Clustered Column – Line on Secondary Axis”. Navigate to it as pictured below:

Wow. We’re almost done – should look something like this:

Step Six – Clean Up

We need to adjust that axis on the right and add a title, and whatever else you’d like to do to pretty this up.

To change the axis, right click in the body of that section of the chart and select “Format Axis”,

 then change the number (1.2 in this example) to 1.0:

Et Voilà! You’re done! Once you’ve done a few it actually becomes quite easy. We ended up with something like this here:

Now you’re ready to incorporate it into your next management meeting, and use it to get the resources you need to fix the vital few, leaving the trivial many for another day.

To review the concept and uses behind the Pareto Chart tool, we covered that in last week’s Toolsday.

Thanks for following along, and if you’ve got any strength left, go forth – and calibrate thyself.

Sal

P.S. I hope you’ve found this post helpful, can I ask you to look at this offer? I’ve used Audible for over 10 years – it really is a wonderful resource.

Thank you.

The Pareto Chart is a classic – it has history, and it has applicability. There are a few of these basic tools used by those who analyze a business, they are for the most part very easy to understand. But as simple as they are, they can be quite powerful.

We’ve talked about the disciplined problem solving approach called 5 Whys; that’s one of them. Today we will talk about Pareto Charts.

Che Cosa è Pareto Chart?

You may even have heard of The Pareto principle, or what is more commonly known as the 80–20 rule, or “The vital few, and the trivial many”. Meaning that, in many cases, roughly 80% of the effects come from 20% of the causes.  Math comes into the scene with something called the power law of distribution – but let’s just keep that … over here – for now.

The oft’ cited Joseph Juran (1904 – 2008) – the quality management evangelist (did you know his middle name is Moses?) is credited with suggesting the principle and naming it after Italian economist Vilfredo Pareto (1848–1923). Vilfredo noticed in the early 1900’s that 80% of the land in Italy was owned by 20% of the population – and he went on to his garden, finding that 20% of the pea pods contained 80% of the peas.

Turns out that there are examples in business, economics and civilization in general,  as well as in the natural world. An excellent example is used as the basis for one of my favorite reads, The 4-Hour Workweek: Escape 9-5, Live Anywhere, and Join the New Rich by Tim Ferriss. He recommends focusing attention on those 20% of customers who contribute 80% of the income. And the extension of this is a recommendation to refuse to do business with those 20% of customers who cause the most trouble and take up the most time.

Here we will focus on the Pareto principle’s application in quality management. Or, the marvel of prioritizing targets of improvement, called the Pareto chart.

In the Pareto Chart, there are both bars and a line graph. Individual values are represented in descending order by bars, and the cumulative total is represented by a line above the bars. Like this:

The bars are typically the various contributors to a problem. Larger bars represent more prevalent causes, and the line represents the cumulative effect on the whole problem for each bar; combined.

Let’s Build One

To build an example of our own, we start with the data. We’ll pretend we have some unit failures and we’ve investigated the various failure modes, and how many instance of these we find:

Pretty simple example, and maybe intuitively you know where your problem children are, but lets see how it looks in a pareto.

From this, it is very easy to see that Connectors are 35% of all problems, and if we were to correct both Connectors and Solder issues, we’d fix 70% of all of our current unit failures.

The other use for this type of graphical representation of “the big hitters” is that it makes it clear, when seeking buy-in, for example, where resources should be allocated.

Look at these slides for a few cycles – they’ll give you a bit more data on how to interpret the Pareto chart (you can click on the right edge to go to the next slide, hover to pause).

Strategically, once you’ve solved those two big hitters, you can run more data and make another Pareto – until only the small stuff remains. And nobody should sweat the small stuff.

So, there you have it. The Pareto chart – separating the vital few, from the trivial many.

One more thing, you may be inspired to attack an issue; grab some data and move it all into Microsoft Excel… I am sorry to report that Excel doesn’t have a “Make me a Pareto” button. There are apps (we used to call them “programs” – we were very old-fashioned) that will create this for you, and you will likely pay for the privilege, too.

But no worries – I can help you out. Next week I will show you step-by-step how to create a Pareto chart in Excel 2013.

Meanwhile, here is a hint for the bold:

See you next week, my friends, for “How to Make a Pareto Chart in Excel 2013” and the next Tool Tuesday!

Thank you, go forth and calibrate thyself.
Sal