iSixSigma

Step-by-Step Guide to Building Dynamic Pareto Charts

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.

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

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.
Handpicked Content:   Call Center Quality: Satisfaction Over Quantity

At this point, the table should look like Figure 2.

Figure 2: Table after Entering Formulas

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

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

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.
Handpicked Content:   Using Six Sigma for Assessing Employee Satisfaction

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

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.

Comments 8

  1. Mariyappan

    These kind of teaching is very helpfull for us to learn more things about the quality process. …thanks sir……

    0
  2. rpaxton

    Can anyone help me? I am using Excel 2007. When it asks to key in the X axis and go to the series tab, 07 doesn’t have the series tab any more. Where do I find this?
    I appreciate any help.

    0
  3. J Mac

    When I attempted to put the formulas into the series section, the system kept sending an error message that stated that “the Reference is not valid. Reference must be to an open worksheet.”

    0
  4. Vamsi Krishna K

    at micosoft site pareto template for excel is available, free to download. I did same for my project.

    0
  5. John Mathew

    @Vamsi Krishna I have downloaded Pareto chart from office website, but how can I integrate this template to office excel 2010?

    0
  6. Jon

    Here is another nice example of a dynamic Pareto chart in Excel that I came across – http://trumpexcel.com/2015/02/dynamic-pareto-chart-in-excel/

    0

Leave a Reply