How to create Pareto Chart using a spreadsheet (Microsoft Excel)?

Six Sigma White Belt: Learn Six Sigma & Grow Your Potential Six Sigma White Belt: Understanding the Seven Basic Tools of Quality
6 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$49.99
List Price:  $69.99
You save:  $20
€46.38
List Price:  €64.94
You save:  €18.55
£39.69
List Price:  £55.57
You save:  £15.88
CA$68.04
List Price:  CA$95.27
You save:  CA$27.22
A$77.05
List Price:  A$107.88
You save:  A$30.82
S$67.53
List Price:  S$94.55
You save:  S$27.01
HK$391.10
List Price:  HK$547.57
You save:  HK$156.47
CHF 45.30
List Price:  CHF 63.42
You save:  CHF 18.12
NOK kr542.56
List Price:  NOK kr759.63
You save:  NOK kr217.06
DKK kr345.97
List Price:  DKK kr484.38
You save:  DKK kr138.41
NZ$83.89
List Price:  NZ$117.46
You save:  NZ$33.56
د.إ183.57
List Price:  د.إ257.02
You save:  د.إ73.44
৳5,487.49
List Price:  ৳7,682.93
You save:  ৳2,195.43
₹4,169.34
List Price:  ₹5,837.41
You save:  ₹1,668.07
RM236.60
List Price:  RM331.26
You save:  RM94.66
₦70,914.31
List Price:  ₦99,285.71
You save:  ₦28,371.40
₨13,903.63
List Price:  ₨19,466.20
You save:  ₨5,562.56
฿1,823.66
List Price:  ฿2,553.28
You save:  ฿729.61
₺1,615.90
List Price:  ₺2,262.39
You save:  ₺646.49
B$249.58
List Price:  B$349.43
You save:  B$99.85
R954.17
List Price:  R1,335.92
You save:  R381.74
Лв90.31
List Price:  Лв126.45
You save:  Лв36.13
₩67,592.09
List Price:  ₩94,634.34
You save:  ₩27,042.24
₪184.30
List Price:  ₪258.03
You save:  ₪73.73
₱2,816.67
List Price:  ₱3,943.57
You save:  ₱1,126.89
¥7,571.47
List Price:  ¥10,600.67
You save:  ¥3,029.19
MX$829.61
List Price:  MX$1,161.53
You save:  MX$331.91
QR182.13
List Price:  QR255
You save:  QR72.86
P685.38
List Price:  P959.59
You save:  P274.21
KSh6,573.68
List Price:  KSh9,203.68
You save:  KSh2,630
E£2,364.52
List Price:  E£3,310.52
You save:  E£946
ብር2,840.03
List Price:  ብር3,976.27
You save:  ብር1,136.24
Kz41,802.18
List Price:  Kz58,526.41
You save:  Kz16,724.22
CLP$48,984.20
List Price:  CLP$68,581.80
You save:  CLP$19,597.60
CN¥361.44
List Price:  CN¥506.04
You save:  CN¥144.60
RD$2,960.45
List Price:  RD$4,144.87
You save:  RD$1,184.41
DA6,744.74
List Price:  DA9,443.17
You save:  DA2,698.43
FJ$113.05
List Price:  FJ$158.28
You save:  FJ$45.23
Q389.89
List Price:  Q545.87
You save:  Q155.98
GY$10,460.71
List Price:  GY$14,645.83
You save:  GY$4,185.12
ISK kr6,972.60
List Price:  ISK kr9,762.20
You save:  ISK kr2,789.60
DH506.54
List Price:  DH709.20
You save:  DH202.66
L881.58
List Price:  L1,234.28
You save:  L352.70
ден2,850.77
List Price:  ден3,991.30
You save:  ден1,140.53
MOP$402.90
List Price:  MOP$564.10
You save:  MOP$161.19
N$946.63
List Price:  N$1,325.35
You save:  N$378.72
C$1,840.39
List Price:  C$2,576.69
You save:  C$736.30
रु6,667.15
List Price:  रु9,334.54
You save:  रु2,667.39
S/185.40
List Price:  S/259.58
You save:  S/74.17
K191.34
List Price:  K267.89
You save:  K76.55
SAR187.48
List Price:  SAR262.49
You save:  SAR75
ZK1,261.25
List Price:  ZK1,765.85
You save:  ZK504.60
L230.69
List Price:  L322.98
You save:  L92.29
Kč1,175.26
List Price:  Kč1,645.46
You save:  Kč470.19
Ft18,362.67
List Price:  Ft25,709.21
You save:  Ft7,346.54
SEK kr535.59
List Price:  SEK kr749.87
You save:  SEK kr214.28
ARS$42,879.12
List Price:  ARS$60,034.20
You save:  ARS$17,155.08
Bs345.48
List Price:  Bs483.70
You save:  Bs138.22
COP$193,772.46
List Price:  COP$271,296.95
You save:  COP$77,524.48
₡25,413.63
List Price:  ₡35,581.11
You save:  ₡10,167.48
L1,234.27
List Price:  L1,728.07
You save:  L493.80
₲368,865.97
List Price:  ₲516,441.87
You save:  ₲147,575.90
$U1,890.76
List Price:  $U2,647.22
You save:  $U756.45
zł200.34
List Price:  zł280.50
You save:  zł80.15
Already have an account? Log In

Transcript

Creating a Pareto chart in Excel is an easy thing to be done, it is not really difficult. The first step is to align the data in this format. Here we have the error types in a type one two error type nine, and we have the number of errors. Then the next step is to arrange the data in descending order for arranging the data, you have to just sort the data from largest to smallest. Once you have done that, then the next step is to identify the total of all the errors. You can take the total by clicking on the AutoSum the next step is to take the percentage is to calculate the percentage contribution of each error type.

You go ahead and do that by dividing the number of errors in type of type one by the total number of errors, putting them into a percentage format. Similarly, we can get for type two, and so on. We quickly get this done. Why don't you do it for all of the error times? Then we move to the next step. For all of the nights, you calculate the cumulative percentage, cumulative percentage is rather simple to calculate.

To calculate the cumulative percentage for type one is much is very easy. Just select the first 25% cumulative percentage for type two is a total of what we have as type one plus the type two. So, it will be 25% less 22% cumulative percentage for type three will be a total of 47% plus 18% four will be 66% plus 11% type drive 77% plus 9% type six is 86% plus 5%. Type seven is 91% 4%. Likewise by eight is 95% plus 3%. Type nine will be 98% was 2%.

At the end, you should always get 100% then your calculations are correct. Now it is time for drawing the graph in order to draw the graph and just ensuring that all the formulas are replaced by values. After that we don't need the percentage contribution column so, we could delete that. And then, we select the entire data. Do not select the total which is 1360. Just select from error types 200% Go to Insert, click on column and then use the First table there, use the first chart where you would see the number of errors and cumulative percentage.

And you would see this chart. What you would need to do is identify this percentage the cumulative percentage percentage and change its axis. So you go to Format Data Series, click on secondary axis, you will find that a red colored bar chart has appeared with a secondary axis. The next step is to convert this secondary axes into a line chart. You'll do that by right clicking, and then clicking on. After you right click, select Change series chart type and then select line chart.

Once you do that, legend at the bottom and then Right click on the bar chart so that you reduce the gap between the bars that you're almost close to be done. Give a good color. And then right click on the line chart to add the data labels. You added the data labels, put them at the top so that they are visible. This is your query to chart. It's that easy.

What you're able to see this Pareto chart is that type one, type two, type three and type four are those four error types, which are contributing to 77% of errors. So if this process focuses on improving just these things, For error types, they would be able to get at least up to 77% of improvements. Thank you

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.