This tutorial will help you draw and interpret a Pareto plot in Excel using the XLSTAT statistical software.
Dataset to generate a Pareto plot
An Excel sheet containing both the data and the results for use in this tutorial can be downloaded by clicking here.
The data are from [Pyzdek Th. (2003), The six sigma Hanbook Revised and expanded, McGraw Hill, New York] and correspond to arrival inspections of peaches of super market during 1 month.
A Pareto chart draws its name from an Italian economist, but J. M. Juran is credited with being the first to apply it to industrial problems.
The causes that should be investigated (e. g., nonconforming items) are listed and percentages assigned to each one so that the total is 100 %. The percentages are then used to construct the diagram that is essentially a bar or pie chart. Pareto analysis uses the ranking causes to determine which of them should be pursued first.
Setting up a a Pareto plot
Once XLSTAT is activated, select the XLSTAT / SPC / Pareto plots command.
The Pareto dialog box will appear. Then select the data on the Excel sheet.
(Note: There are several ways of selecting data with XLSTAT - for further information, please check the section on selecting data.)
In this example, the data start from the first row, so it is quicker and easier to use columns selection. This explains why the letters corresponding to the columns are displayed in the selection boxes.
In the Options tab, we activate Descriptive statistics and Charts.
In the Output tab, we activate all options.
In the Charts tab, we activate all options.
The computations begin once you have clicked on OK. You are asked to confirm the number of rows and columns (this message can be bypassed by un-selecting the Ask for selections confirmation in the XLSTAT options panel).
Interpreting a Pareto plot
The first table displays the descriptive statistics of the defect data. There are the number of observations, the number of missing values, the sum of the weights, the number of the categories, the mode, and the frequency of the mode. Then for each of the categories, the name, the frequency, the relative frequency, and the cumulated relative frequency is displayed.
The following chart is the Pareto chart using bars. The categories are sorted down.
We see, that there are 3 defect causes that sum up together to over 95 % of the whole defects: rotten, bruised and undersized. These causes should be followed up during continuous improvement programs.
At last a pie chart of the relative frequencies is displayed and let us come to the same conclusions.
Watch this video to see how this tutorial is done.