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%.
Example:
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”.
Joseph Harris
ExcelYogi.com
Analyst Help
Related posts:
- Scrolling Excel Charts - Scroll Bar Graphs There are many times when you may have a lot...
- Images and Pictures in Charts - Excel Graphs Quick an easy method of making your Excel graphs more...
- Automatic Updating “Dynamic” Charts in Excel One of the most time consuming parts of producing a...
You can simplify your data range so that only three columns are needed. Column C will have cumulative percentage. In C2 in your example, enter this formula:
=SUM($B$2:$B2)/SUM($B$2:$B$5)
and drag it down. Now you can make the chart with a single selected range, or even with a single cell in the range selected.