Analyze Data with Template for Dynamic Pareto Charts

	Bookmark This Page Email This Page Format for Printing Cite This Article Submit an Article Read More Articles
	Related Tools & Articles Deploying a Measurement System: What Does It Take? Pareto Chart Deploying a Measurement System: What Does It Take?
	Discussion Forum "Can anyone tell me how to read or analyze a Pareto chart? What are its benefits? How does it help to sort out defects of proucts by different reasons?" Contribute to this Discussion

Download Products Lean Six Sigma Champion Training Project Selection Process Pack Business Process Management Power Tools

By Poornachandra Upadhya

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

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.

About the Author: Poornachandra Upadhya is a quality manager working for iGate Technologies, based in Bangalore, India. He has experience in operations, training and quality, and received his Master Black Belt certification from Indian Statistical Institute, Bangalore. He can be reached at poornachandra.upadhya@igate.com.

"The Bottom Line" Links

BEST SELLING PRODUCTS (iSixSigma Publications) Six Sigma Black Belt (DMAIC) Training Slides - 2009 Version! The 2009 Six Sigma Black Belt course includes over 40 more slides than the 2008 version. Contents include: 1,220 PowerPo... Certified Lean Six Sigma Black Belt Assessment Exam Interested in assessing your knowledge of Lean Six Sigma? Preparing for certifications? Testing your students and traine... Certified Lean Six Sigma Green Belt Assessment Exam This assessment exam is useful for students interested in assessing their knowledge of Lean Six Sigma on the Green Belt ... Certified Lean Six Sigma Black Belt E-book In 670 pages learn everything within the Lean Six Sigma DMAIC body of knowledge to successfully achieve Black Belt certi... Kaizen Workshop E-book This 150+ page ebook teaches key tools and techniques of Kaizen, as well as real application to enhance learning. Kaizen... Six Sigma Yellow Belt Training Slides - 2009 Version The 2009 Six Sigma Yellow Belt course is comprised of: 503 slidesInstructor notesSlide explanations15 data sets19 suppo... Design For Six Sigma (DFSS) E-Book or Print Need an "encyclopedia" consisting of many of the tools you’ll study? Need a helpful refresher to apply the DFSS process?... Sell Your Products