The Pareto principle states that 80% of the problems are cause by 20% of the causes. This is a useful concept for Analyst and Six Sigma Black Belts attempting to identify defects or problems that need to be concentrated on. I think of it as, what do I have to fix, to get the biggest bang for my buck (or efforts). The Pareto chart quickly shows what should be concentrated on.
To create a Pareto chart in Excel:
- The first column contains the categories of your data
- The second column contains the number of times that category occurred
- The third column contains the percentage of each category to the whole, in the example below the formula in c2 would be =b2/sum(b2:b5).
- The fourth column is the cumulative percentages of each category. The formula in d2 in the example below is =c2. The formula in d3 is =c3+d2, then drag to the last row of data. This column should end with 100%.
The chart is created by using the “line-column on 2 axis” chart in the custom types section of the chart menu. Sort your data by occurrences descending. The occurrences are one series and the cumulative % is the other series. The bars are the # of occurrences, the line is the cumulative %.
You can quickly tell from looking at the Pareto that the machine breaking and employee shortages make up almost 70% of the “occurrences”.