Creating A Pareto Chart In Excel

Creating a Pareto Diagram Assume you have the following information in two columns of an Excel spreadsheet; the first column header is “Call Reason”; the second column header is “Number Of Calls”. Your objective is to convert this data into a Pareto diagram using the Excel features described below:

Call Reason contract issues call quality credit request ringtones problem text messaging repair concerns wireless web access lost phone dropped calls billing issues Sample Size

Number Of Calls 6 60 15 23 9 27 15 10 90 45 300

1. First sort your data from highest count to lowest count: (Excel > Data > Sort > Descending): highlight both columns, and sort by the # calls column, descending.

2. In the next step, we will create a percentage and cumulative percentage column that will be used to build the Pareto chart. a. For the % column, in the first cell, create the formula: =(B2/300)*100; drag the lower right-hand corner of that cell to duplicate the formula in the other cells. Then you can highlight the cells, and select from the menu “Format”, then Cells, then Number, then decimal places. Select 1 for the decimal place.

Creating a Pareto Diagram b. For the Cum % column: in cell D2, type in 30. Then in cell D3, create a formula: =(D2+C3); then drag the lower right-hand corner of the cell to duplicate this formula in the other cells.

3. Use the Ctrl key, and highlight the Category, the Percentage column, and the Cumulative Percentage columns (but NOT the frequency column). 4. Use the Chart Wizard to generate a Combination Bar Chart with both left and right axes, also called a “Line column on two axes” chart. After clicking on the Chart Wizard, go to the “Custom Types” tab, and select the “line column on two axes” chart.

Creating a Pareto Diagram 5. Add a title and generate the chart. You can slant the labels by double clicking on them, and on the “Format Axis” menu that pops up, select the “Alignment” tab. Drag the text line to a 30 degree angle, and click OK. Your chart is now complete.

Note that the height of each bar references the left axis, whereas the percentage line references the right axis. Your Pareto chart is now complete – congratulations !