Archive for the ‘Knowledge’ Category

Pareto Charts – How to do pareto analysis using Excel?

April 26, 2012 Leave a comment

A Pareto chart or pareto graph displays the importance of various factors in decreasing order in columns along with cumulative importance in a line. Pareto charts are often used in quality control to display most common reasons for failure, customer complaints or product defects.

The principle behind pareto charts is called as pareto principle or more commonly the 80-20 rule. According to wikipedia,

The Pareto principle (also known as the 80-20 rule,[1] the law of the vital few, and the principle of factor sparsity) states that, for many events, roughly 80% of the effects come from 20% of the causes.

The pareto chart is a great way to do the pareto analysis. Today, we will learn how to use excel to make a pareto chart.

See an example pareto chart of visits to this website:

pareto chart example

(Please note that in this example, the 80/20 rule does not hold as I have chosen very small sample of data. In reality, the 80/20 principle applies to my website as well)

Making a Pareto Chart in Excel

In order to make the pareto chart in excel, first you must have the data ready. Once we have the values for each cause, we can easily calculate cumulative percentages using excel formulas. We will also require a dummy series to display the “cutoff %” in the Pareto chart.

I have arranged the data in this format. You can choose any format that works for you.

Pareto analysis - data

Once you have the data ready, making the pareto chart is a simple 5 step process.

1. Make a column chart using cause importance data

Make a column chart using cause importance data
In our case, we select the first 2 columns in the above table and then make a new column chart.

2. Add the cumulative %s to the Pareto Chart as a line

Add the cumulative %s to the Pareto Chart as a line
Select the third column, press ctrl+c (copy). Now select the chart and press ctrl+v (paste). Excel will add another column series to the chart. Just select it and change the series chart type to “line chart”. Learn more about combining 2 different chart types in excel combo charts.

3. Move the cumulative %s line to secondary axis

Move the cumulative %s line to secondary axischange to secondary axis

Select the line chart, go to “format data series” (you can also press ctrl+1) and change the axis for this chart series from “primary” to “secondary”.

4. Add the cut-off % to the pareto chart

Add the cut-off % to the pareto chart
Select the fourth column in our data table, copy and paste it in the chart. This should ideally be pasted as a new line chart. If not, follow step 2 for this as well.

5. Finally, adjust formatting to make the final pareto chart

Finally, adjust formatting to make the final pareto chart
Now, our basic pareto chart is ready. We should adjust the chart formatting to make it more presentable. Once you are done, the final output will be something like above chart

When to use Pareto Chart?

Pareto charts can be used,

  • During quality control to analyze the causes of defects and failures
  • When you want to focus your resources on few important items from a large list of possibles
  • To tell the story that attacking problem A might be better than solving problem C, D and F

Pareto charts and pareto analysis has great practical uses for almost anyone in a managerial role.


Pareto’s Principle – The 80-20 Rule

April 26, 2012 Leave a comment

How the 80/20 rule can help you be more effective:


In 1906, Italian economist Vilfredo Pareto created a mathematical formula to describe the unequal distribution of wealth in his country, observing that twenty percent of the people owned eighty percent of the wealth. In the late 1940s, Dr. Joseph M. Juran inaccurately attributed the 80/20 Rule to Pareto, calling it Pareto’s Principle. While it may be misnamed, Pareto’s Principle or Pareto’s Law as it is sometimes called, can be a very effective tool to help you manage effectively.

Where It Came From

After Pareto made his observation and created his formula, many others observed similar phenomena in their own areas of expertise. Quality Management pioneer, Dr. Joseph Juran, working in the US in the 1930s and 40s recognized a universal principle he called the “vital few and trivial many” and reduced it to writing. In an early work, a lack of precision on Juran’s part made it appear that he was applying Pareto’s observations about economics to a broader body of work. The name Pareto’s Principle stuck, probably because it sounded better than Juran’s Principle.

As a result, Dr. Juran’s observation of the “vital few and trivial many”, the principle that 20 percent of something always are responsible for 80 percent of the results, became known as Pareto’s Principle or the 80/20 Rule.

What It Means

The 80/20 Rule means that in anything a few (20 percent) are vital and many(80 percent) are trivial. In Pareto’s case it meant 20 percent of the people owned 80 percent of the wealth. In Juran’s initial work he identified 20 percent of the defects causing 80 percent of the problems. Project Managers know that 20 percent of the work (the first 10 percent and the last 10 percent) consume 80 percent of your time and resources. You can apply the 80/20 Rule to almost anything, from the science of management to the physical world.

You know 20 percent of your stock takes up 80 percent of your warehouse space and that 80 percent of your stock comes from 20 percent of your suppliers. Also 80 percent of your sales will come from 20 percent of your sales staff. 20 percent of your staff will cause 80 percent of your problems, but another 20 percent of your staff will provide 80 percent of your production. It works both ways.

How It Can Help You

The value of the Pareto Principle for a manager is that it reminds you to focus on the 20 percent that matters. Of the things you do during your day, only 20 percent really matter. Those 20 percent produce 80 percent of your results. Identify and focus on those things. When the fire drills of the day begin to sap your time, remind yourself of the 20 percent you need to focus on. If something in the schedule has to slip, if something isn’t going to get done, make sure it’s not part of that 20 percent.

There is a management theory floating around at the moment that proposes to interpret Pareto’s Principle in such a way as to produce what is called Superstar Management. The theory’s supporters claim that since 20 percent of your people produce 80 percent of your results you should focus your limited time on managing only that 20 percent, the superstars. The theory is flawed, as we are discussing here because it overlooks the fact that 80 percent of your time should be spent doing what is really important. Helping the good become better is a better use of your time than helping the great become terrific. Apply the Pareto Principle to all you do, but use it wisely.

Manage This Issue

Pareto’s Principle, the 80/20 Rule, should serve as a daily reminder to focus 80 percent of your time and energy on the 20 percent of you work that is really important. Don’t just “work smart”, work smart on the right things.

By Ismail.

Categories: Knowledge