Pareto Analysis in Excel Purpose of the Assignment 1. 2. 3. 4.
To complete a Pareto analysis in Excel. To sort data in Excel. To set up cumulative sums in Excel. To develop a professionally formatted chart with a secondary axis.
Creating a Data Table Pareto analysis is used to analyze the types of complaints or defects related to a process. We are given (1) types of complaints or defects, and (2) the number of complaints or defects of each type. Create a data table like the one below:
Sorting Data in Office 2007 We want to sort the complaints, from highest to lowest number of complaints. 1. Highlight cells A4:B12, which contain the complaint types and the number of complaints.
2. Click the Sort & Filter editing tool in the upper right hand corner in the tool bar. On the dropdown menu, select the Custom Sort option. 1
3. A dialog box will appear. In the Sort by dropdown menu, select Number of Complaints. In the Sort on dropdown menu, select Values. In the Order dropdown menu, select Largest to Smallest.
4. Click OK. The dialog box should disappear, and the data should be sorted from largest to smallest number of complaints.
1. In cell B13, compute the Total Number of Complaints in cells B4:B12. 2. Compute the percentage of complaints of each type. The formula for cell C4 is =B4/$B$13. Copy that formula to cells C5:C12. 3. As a check, compute the total percentage for all complaints in cell C13. It should be 100%. 4. Highlight the cells in Column C. On the ribbon, click Format and select Format cells. Highlight Percentage and change the number of decimal places to 1. Click OK. 5. For the first type of complaint, the cumulative percentage equals the percentage. The formula for cell D4 is =C4. 6. For the second and subsequent types of complaints, the easiest way to compute cumulative percentage is (cumulative % for preceding type) + (% for this type). The formula for cell D5 is = D4 + C5 . Copy this formula to cells C6:C12 7. If the cells in column D are not formatted as percentages, change the number formats to Percentage as described above. Your final spreadsheet should be similar to the one below:
Making a Chart with Number of Complaints and Cumulative Percentage of Complaints
We will plot the number of complaints as a bar graph on the primary (left-hand) axis. We will plot the cumulative percentage of complaints as a line graph on the secondary (right-hand axis).
1. The complaint labels in column A are too long to use as data labels in a chart. Click on a cell in column B of the data table. Then right-click. Select Insert from the menu, and then click the radio button next to Entire Column. 2. In the new column B, type brief abbreviations (usually 3-4 letters) for each type of complaint. On the Home tab of the ribbon, click Format and then AutoFit Column Width. The new data table is shown below.
3. Highlight the cells containing the number of complaints, percentage of complaints, and cumulative percentage of complaints. In the spreadsheet above, you would highlight cells C4:E12. 4. Click the Insert tab on the ribbon. Then click Column, and click on the first (leftmost) 2D chart, as highlighted in the portion of the Column menu shown below.
5. A chart should appear. Drag the chart to the place on the spreadsheet where you want it.
6. We need to delete Series 2 (percentages) from the chart. Click a blank space on the chart. Then right-click, and click Select Data. The Select Data Source dialog box will appear, as shown on the 4
next page. Highlight Series 2 and then click Remove. Series 2 will disappear from the dialog box and the chart.
7. We need data labels for Series 1 and Series 3. In the dialog box, highlight Series 1 and then click Edit. The Edit Series dialog box will appear. Type the desired name for Series 1 into the box and click OK.
8. The Select Data Source dialog box will reappear. Change the name for Series 3, as described above
9. We will use the right-hand side of the Select Data Source dialog box to change the data labels on the horizontal axis of the chart. Under Horizontal (Category) Axis Labels, click Edit. When the Axis Labels dialog box appears, highlight the cells that contain the axis labels and click OK.
Note: This chart appears in Sheet 3 of a sample spreadsheet. In most cases, there would be only one worksheet in a workbook, and the sheet number would not appear. 10. When the Select Data Source dialog box reappears, click OK to finalize your changes. Your chart should be similar to the one below:
11. The number of complaints varies from 2 to 60, while the cumulative percentage varies from 30 to 100% (0.30 to 1). Since the cumulative percentages are much smaller than 60, they are hard to see. We will add a secondary (right-hand axis) for the cumulative percentages. Click on the Cum. % series in the chart. Then click the Chart Tools menu at the top of the screen. Next, click the Format tab.
If the name of the series that you want to graph on the right-hand axis is not displayed in the Current Selection group on the left side of the screen, click on the series in the chart and try again. 6
12. Click Format Selection. When the Format Selection dialog box is displayed, click the Secondary Axis radio button. Then click Close. 13. We now have a new problem. The Cumulative % bar is covering up some of the Complaints bars. This problem can be solved by displaying the Cumulative % series as a line graph. Click on one of the Cumulative % bars to select the series. Then right click, and select Change Series Chart Type. In the Change Chart Type dialog box, click on Line. The default line type is fine. Click OK
The current chart is shown below:
14. We now have a good graphical display of the data. We need to add at title to the chart, and a title for each axis. To add the titles, click on the chart and then on the Format tab of the Chart Tools menu. Use the Chart Title and Axis Titles menus in the Labels group to add titles. The completed chart is shown on the next page.