Create Pareto Chart in Excel!!!

Watch the video - How to create Pareto in Excel!

Before we start first we should understand what Pareto Chart is.

Pareto Chart is based on the Pareto principle (also known as the 80/20 rule), which is a well-known concept in project management.

According to this principle, 80% of the problems are due to 20% of the issues.
If we control these 20% of the issues, our 80% of the problems will be reduced/removed. .

Let say, we have below Error data table:

We have to follow below steps to create Pareto Chart:

Step 1. Sort the data as per Error Count (Largest to Smallest)

Step 2. Add a new column 'Cumulative %' & Put a function mentioned below

Function = "=SUM($D$3:D3)/SUM($D$3:$D$9)"

Change the format, so that table looks good & related

Step 3. Change Column 'Cumulative %' format to '% Format'

Step 4. Select entire table & insert a 2 Column Chart

Your Chart Will Look Like This

Step 5. Now, Right click on any of the bar

Step 6. Now, Check on 'Secondary Axis' for Cumulative % and change the Chart Type to Line graph as in below image

Step 7. Your Chart is ready, see below

Pareto Chart

How to Interpret this Pareto Chart in Excel

Create a straight line at 80%

Manager should focus on the errors which are before the intersection of Straight line with Cumulative % line.

For example, targeting the first 4 errors types would automatically take care of ~80% of the Problems.

Do you guys use Pareto Chart in excel?

If so, please share thoughts how you have used.

---------------------------------------------------------------------------------------------------------------------------------------------------------------

Download the file: ParetoChart.xlsx

Thanks for learning, will come soon with new post.