Creating Pareto Chart Using Microsoft Excel : Part II

Now to create the chart. If you arrived at this post and have no idea why you're suddenly creating a chart, you may check the previous post on accumulating data first.

To continue, we're selecting the data for the chart. 

Choose B3:B7 and D3:D7 (Highlight B3:B7, press Ctrl, hightlight D3:D7). Then, choose Insert->Chart->Combo. Choose Series 2 as Secondary Axis. 

Now you have the Pareto chart. Customize by adding labels, values and changing the horizontal range.

Now that your chart is complete, you may begin analyzing the highest defects, problems or causes to plan the countermeasure.

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

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.

<<Proceed to the second part on creating the chart.>>