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.
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.
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).
Add a cumulative percentage column to the table.
Display cumulative numbers as percentages. This is the final Pareto table.
Step 3 / create Pareto 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.
Items can have different importance. So we have to calculate with weight of item. Let’s use the previous table.
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.