Saturday, June 20, 2020

Creating Pareto Chart Using Microsoft Excel : Part I

First of all, what is pareto chart?

Pareto chart is a chart that have both bars and a line graph. Pareto chart analyzes the frequency of problems, causes or defects. It is useful to prioritize the defects or problems in order to create a countermeasure.



Image Source : Metacomet/Wikipedia

Image source : Metacomet/Wikipedia

How to create Pareto chart using Microsoft Excel?

The first part is to collect the data that you need. Organize it in descending order based on the total of causes, problems or defects that occurs.



Then, sum all the causes to find the total value. Next, find the cumulative values of each causes.




C3 value is B1, C4 value is by adding C3 to B4. You may use the calculation provided by Microsoft Excel. For example, in cell C4, use =SUM(C3+B4). For C5, =SUM(C4+B5). Continue until you add all value.

Now, we're going to calculate the cumulative percentage.



To calculate, use Cumm value/Total*100. For example, in D3, it should be =C3/B8*100. Continue for all values. The last value should be 100%. Now, you're ready to create the chart.