How to create a Pareto Analysis Chart in Excel – 80/20 Rule or Pareto Principle

How to create a Pareto Analysis Chart in Excel – 80/20 Rule or Pareto Principle


The Pareto principle states that, for many
events, roughly 80% of the effects come from 20% of the causes. In this lecture, we will learn how to create
a pareto chart, which combines a column chart and a line graph. Our data has 2 columns, item category and
sales. It’s very important to put sales in descending
order to create a pareto chart. Click one of the entries in column B and from
Home tab, find Sort and Filter button and click. We always want to sort largest to shorter. We also want a total at the bottom of sales. Go to B9 and click Auto Sums, Enter, and the
total is ready. Okay, now we must create a new column to calculate
the cumulative total. Type in Cumulative and slide over the first
cell C2. For refreshments, the entry is the same, so
type=B2. But for the other entries, we want to put
in the previous cumulative plus the latest entry. Type in=C2+B3 and enter. Double click at the corner of cell C2 and
see all the cumulative cells. We have to create another one column called
percent. It will calculate the cumulative entry divided
by total. Go to D2 and type in equal C2 divided by B9. Because we want to use B9 over and over again,
we have to make it an absolute address. So press F4 and Enter. Slide the cell down. Finally, press the percent style button to
format them as a percent. We are ready to create our chart. We don’t want to include cumulative column
so we highlight only these data. Go to Insert tab and let’s try recommended
charts first. The first chart is exactly what we want, Clustered
column line on secondary axis. Press OK. It’s better to add a label to line so we
can see the percent exactly. Click on line and from Design tab, go to Add
chart elements, and select data labels and below. The conclusion is that the first 3 item categories
(refreshments, spirits, and mineral water) represents almost 80% of our total sales.

Author:

2 thoughts on “How to create a Pareto Analysis Chart in Excel – 80/20 Rule or Pareto Principle”

Leave a Reply

Your email address will not be published. Required fields are marked *