Subscribe!

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:

  1. The first column contains the categories of your data
  2. The second column contains the number of times that category occurred
  3. 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). 
  4. 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:
pareto chart
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:

  1. Scrolling Excel Charts – Scroll Bar Graphs There are many times when you may have a lot...
  2. Images and Pictures in Charts – Excel Graphs Quick an easy method of making your Excel graphs more...
  3. Automatic Updating “Dynamic” Charts in Excel One of the most time consuming parts of producing a...

This entry was posted on Thursday, November 20th, 2008 at 10:13 am and is filed under Business Analyst, Excel Hints. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “How to Create a Pareto Chart in Excel”

  1. Jon Peltier on November 20th, 2008 at 12:51 pm

    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.

Leave a Reply

You must be logged in to post a comment.