Pareto Analysis in Excel (part 1)

Pareto principle is formulated as: 80% of the consequences (eg profit or number of rejects) comes from 20% of causes. It can be used in different sectors of human activity (eg, economy, quality of production). We will construct a graph that will show us the major causes.

Example 1

We are looking for the most common item. There are items A, B, C, D, E, F. Items can represent errors, accidents, cases,… Following table presents a list of items for the selected period. This is only a small part of the table. The original table has range A1:B93.

Step 1

Create a table with the frequency of items (frequency of occurrence). You can use COUNTIF function.

If it is necessary, sort the table by frequency (from largest to smallest).

Step 2

Add a cumulative percentage column to the table.

Display cumulative numbers as percentages. This is the final Pareto table.

Step 3 / create Pareto chart

Create a column chart.

Change cumulative frequency data series type to Line: Right-click >> Change Series Chart Type >> Line.

Set series option to Secondary Axis: Right-click >> Format Data Series >> Secondary Axis. The line you will see is sometimes called Lorenz curve.

Add data labels to the cumulative frequency line: Right-click >> Add Data Labels.

Example 2

Items can have different importance. So we have to calculate with weight of item. Let’s use the previous table.

Step 1

Create a table with the frequency of items (frequency of occurrence) and add weight of items.

Other procedures are the same as in the previous example.

Step 2 / create Pareto chart

Create chart from the table. Select columns: Item name, frequency x weight and cumulative frequency.