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. items table 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. formula of items frequency

frequency of items

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

Step 2

Add a cumulative percentage column to the table.

formula for cumulative frequency

cumulative frequency

Display cumulative numbers as percentages. This is the final Pareto table. change format cells to percentage

Step 3 / create Pareto chart

Create a column chart.column chart

Change cumulative frequency data series type to Line: Right-click >> Change Series Chart Type >> Line. line in column chart beginning

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

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

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. pareto table with weight - formula

pareto table with weight 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.

Pareto chart with weight

Add a comment »3 comments to this article

  1. Do you even know what is Pareto? I will answer you:

    Pareto diagram or pareto combines a bar graph and a line graph. The bars show the frequency of each category, sorted by size from the largest to the smallest, and the line graph then shows the cumulative frequency of the categories below.

    Am I crazy or what?

    Reply

  2. Solved! I want Pareto graph into my thesis. It was economical and statistical analyze of big data. Pareto principle make the right results. Great example. Thank you

    Reply

  3. How to create simple Pareto chart in Excel. Nice solution, thank you. I need Pareto Analysis also in Libre Office. Is it the same as in Excel?

    Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress