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