While working in environments where costly software is not easily accessible across the organization, an Excel-based Pareto chart can come in handy. A dynamic Pareto chart template can be a practical aid to practitioners in this situation. By following the steps described here, practitioners can create a Pareto chart that can be updated with a click of a button, provided they input the data labels and their respective frequencies.
This simple chart can be created in six stages. Practitioners need not be Excel wizards; the template is designed so that they may directly input the formulas provided. Once practitioners are comfortable with all the steps, they may wish to modify the prototype further. (Note: Excel 2003 was used to create the graphs, but it should not be much of a problem to replicate this in other versions of Excel.)
Open a blank worksheet and save as “Pareto.” In the sheet, create a table with a column of items and the frequency of their occurrence (Figure 1).
Building the Template
This simple chart can be created in six stages. Practitioners need not be Excel wizards; the template is designed so that they may directly input the formulas provided. Once practitioners are comfortable with all the steps, they may wish to modify the prototype further. (Note: Excel 2003 was used to create the graphs, but it should not be much of a problem to replicate this in other versions of Excel.)
1. Create Table
Open a blank worksheet and save as “Pareto.” In the sheet, create a table with a column of items and the frequency of their occurrence (Figure 1).
Figure 1: Items and Frequency in Table
The table has been filled with some nonsensical data for the sake of example. Cells E1 and F1 are deliberately left blank.
2. Input Formulas
Fill cells with the following information:
◈ In B27, enter the formula
=Sum(B2:B26)
◈ In D2, enter the formula
=B2/$B$27
Copy D2 and paste the contents in D3 to D26.
◈ In E2, enter the formula
=D2
Copy E2 and paste the contents in E3 to E26.
◈ In F2, enter the formula
=F1+E2
Copy F2 and paste the contents in cells F3 to F26.
◈ In C2, enter the formula
=IF(B2=””,””,F2)
Copy C2 and paste the contents in C3 to C26.
At this point, the table should look like Figure 2.
Figure 2: Table after Entering Formulas
3. Create Pareto Chart
To transfer the table data into a chart, follow these steps:
◈ Select cells A1 to C12.
◈ Go to Insert -> Chart
◈ In the chart dialogue box, select the Custom Types tab. Choose the chart type Line – Column on 2 Axes. Click Finish.
4. Input Formulas into Chart
To transfer the formulas to the chart, follow these steps:
◈ Click any blank cell in the Pareto sheet. Go to Insert ? Name ? Define.
◈ Enter the name “DataLabel” (Figure 3)
◈ In the Refers to box, input the formula
=OFFSET(Pareto!$A$2,,,COUNTA(Pareto!$A$2:$A$26),1)
◈ Click OK
Figure 3: Define Name Dialogue Box
◈ Click any blank cell in the Pareto sheet. Go to Insert -> Name -> Define.
◈ Enter the name “Frequency.”
◈ In the Refers to box, input the formula
=OFFSET(DataLabel,0,1)
◈ Click OK.
◈ Click any blank cell in the Pareto sheet. Go to Insert -> Name -> Define.
◈ Enter the name “Cumulative.”
◈ In the Refers to box, input the formula
=OFFSET(DataLabel,0,2)
◈ Click OK.
◈ Right-click on the chart, just within the edge. Select Source Data. Under the Series tab, click Frequency.
◈ In the Category (X) axis labels field, input the formula
=Pareto.xls!DataLabel
◈ In the Values field, input the formula
=Pareto.xls!Frequency (Figure 4).
◈ Click Cumulative in the series field. In the Values field, enter the formula
=’Pareto.xls’!Cumulative
◈ Click OK.
Figure 4: Source Data Dialogue Box
5. Create Macro to Update Pareto
To easily update the Pareto, follow these steps:
◈ Go to Tools ? Macro ? Record New Macro
◈ Type “Pareto” in the Macro name field.
◈ In the space for Shortcut key, type “P”
◈ Click OK.
◈ Select the range A1 to F26.
◈ Go to Data ? Sort
◈ In the Sort by drop down list, choose Column B. Click the Descending radio button.
◈ Click OK.
◈ Click Stop recording on the macro menu.
The dynamic Pareto template is now ready. Practitioners may input different label names and their respective frequencies. To generate a Pareto chart, simply hit Shift + Control + P.
6. Modify
The chart can be further modified by going to View ? Toolbars ? Forms. From the resulting toolbox, choose the Command button. Drag the button onto the sheet and size it as appropriate. When releasing the button, a dialogue box should appear, asking about assigning a macro. Choose Pareto from the list. Rename the command button Create Pareto.
After hiding Columns D, E, F and row 27, the final Pareto should look like Figure 5.
Figure 5: Completed Dynamic Pareto
Whenever the input fields are updated, practitioners can simply hit the command button, and voila! The Pareto chart is generated.
Posts
0 comments:
Post a Comment