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