How to Plot Pareto Chart in Excel ( with example):-
How to Plot Pareto Chart in Excel ( with example), step by step guide and illustration with example is given below , just follow to prepare the Pareto chart in excel.
History and Definition:
A Pareto Chart named after the Italian Economist Vilfredo Pareto. It is a type of chart which contain both bars and line graph, where the individual values are represented in bar graph in descending order (largest to smallest value) and cumulative percentage is represented in the line graph.
Purpose of Pareto Chart:
The purpose of Pareto Chart is to indicate the Contributions among the set of data.
E.g , let we have six types of defects that we would like to know the most cumulative contributions among them those are contributing the 90% , in such scenario we have to plot the Pareto Chart to know the 90% contribution by the help of line Chart , we simply cover the 90% level of line Chart , those are coming under the line graph will represent the 90% contribution . so simply Pareto Chart is helping here to identify the contribution.
Understanding the Pareto Chart principle (The 80/20 rule):
The Pareto principle also know as the 80/20 rule derived from the Italian Economist Vilfredo,
The principle is understood as –
20% of the input creates 80% of the results
80 % of the effects come from 20% of the causes.
In the above Pareto Chart , we can see the cumulative% in line graph , According to the Pareto Chart principle 80/20 rule, the 80% cumulative in line graph is filling under the low hardness , it means BH, Damage, SH and Low hardness defers are coving the 80% of contribution over total types of defects. And those 80% contribution was due to the 20% of caused.
Advantages of Pareto Chart:
1. To optimise the production
2. Reduces the Rejections
3. Reduce the COPQ/COQ
4. Improve the quality
5. Improve the performance of product
6. Improve the customer satisfaction
7. Reduce the rework cost.
How to Plot Pareto Chart in Excel ( with example):
Let we are having six types of defects as BH(Blow Hole) , Damage, SH(Shrinkage), Low hardness , Crk( Crack), pin hole and total rejection quantity is 199. So now we have to arrange / sort the rejection Quality value in descending order (largest to smallest)
Then we have to calculate the cumulative rejection quantity and cumulative % as per the below step ( from step 1 to 3)
As describe in above, Rejection quantity should sort in descending order(Largest to smallest), then you have to calculate the cumulative rejection quantity i.e C2=B2, C3=B3+C2, respectively other value need to calculate and finally Cumulative %, D2=C2/B8x100, for getting the D3 onward value calculation, we have to press “F4” after selecting the cursor button before “B8” in above formula then press the enter, now drag.
Now you have to select defects, rejection quantity and cumulative % column as per the above , and then go to the insert then column and select the bar chart . Simply follow the steps(From S-4.1 to S-4.5) given in above.
After selecting the bar chart , just follow the step(select the “insert” in excel sheet then line and finally select the “Line” graph) then as described in below ( convert the red colour bar into the line Chart )
After the conversation of secondary red colour bar into line Chart , the Pareto Chart will ready to use as
In the above Pareto chart, you can see the bar represents the defect’s Rejection quantity and line represent the contribution of cumulative percentage.