Pareto Chart and Analysis In Microsoft Excel

pbnIn predicting the required salesmen to achieve a sales target, we used Regression in Excel, in our previous example. Using regression analysis, we found that you need a certain number of salesmen to achieve sales targets. But even after hiring all of that workforce, you still haven’t achieved your target. Why? Who is the culprit?

We are going to find out that culprit or culprits. You can decide later what to do with them.

What is Pareto Analysis?

The Pareto analysis states that your 80% problem is caused by 20% of factors. Or we may say that by working on only 20% of cases you can solve your 80% of problems. Fascinating, right? The method is also famous as the 80/20 rule. It is named after its inventor, Vilfredo Pareto.

Scenario

You hired staff and expected to get higher sales of your soft drinks but you were disappointed. Indeed you are disappointed but not in the mood of quitting the business. You are a fighter. You decide to track down the problem. For that, you conducted a survey.

You asked these questions to the public:

  1. Will they buy your soft drink or not?
  2. If yes, then why?

If not then why not?

After your survey is done, you’ve got these stats:

Will Buy Observations
Yes 253
No 247
Total 500

Out of 500, 253 people said yes, they will buy your soft drink and 247 people said they won’t buy it for several reasons. Our focus will be on these 247 observations.

Based on the data, we found out 14 main reasons for:

Why these possible customers will not buy your soft drink?

mf

Method for Pareto Analysis

Step 1: Sort data in descending order in excel.

To sort data, select the data range (don’t select Total Cell) and hit  ALT, A, S, S one by one (keyboard shortcut for sorting). You can use the mouse to do so from the data tab. But I prefer the keyboard only.

jl

In ‘Sort By’ dropdown menu, select ‘No. of Observations’

On the ‘Sort On’ dropdown menu, select ‘Values’

On the ‘Order’ dropdown menu, select ‘Largest to Smallest’

Hit Ok.

The table is now arranged in descending order.
hk

Step 2: Find Cumulative Frequency

Finding it is easier than speaking its name. It’s just additions. You add your next number to the sum of your previous numbers.

For example, if you have 1,2,4,5,8 as your data then your cumulative frequency will be 1, 1+2, (1+2)+4, ((1+2)+4)+5, (((1+2)+4)+5) +8. The cumulative frequency for 1,2,4,5,8 is  1, 3, 7, 12, 20. Easy, isn’t it?

Let’s do the same in our data. The first value will be the same, 70. Next 70+50, that is 120. The next 120+45, that is 165 and so on.

The last frequency will be equal to the total number of observations.

In cell D3, write

=C3

Since there is no number before that we just get that value.

In cell D4, write

=D3+C4

In cell D5, write

=D4+C5. Do this for all observations.

yh

Result:

ghj

Step 3: Find Cumulative%. This one is easy peasy too. Just divide the cumulative frequency by the total number of observations.

In the cell of E4, write

=D3/$C$17

Here I have locked the C17 as it contains a total number of observations and I don’t want it to change while copying the formula in the cells below.

uj

Finally, you will have a table like this.

yhr

Step 4. Give Code Names to Reasons (Optional).

Now, let’s give code names to reasons because these long reasons will not look good on the chart.

ujt

Step 5: Plot a Chart:

Pareto Chart in Excel 2016:

If you just want to plot a Pareto Diagram in Excel 2016. In excel 2016 you do not need to do all of the above procedures. Just select your un/sorted data and goto

Insert -> Recommended Charts -> All Charts -> Histogram -> Pareto Chart
tg

gryghghhAnd it is ready.

tgy

Pareto Chart In Excel 2013 and Excel 2010 and Older Versions

Let’s plot pareto chart with an example

Select your data from the Reason Code for Cumulative%.

Go to Insert -> Line -> Line with Markers
try

You will have a chart that looks like this. This is not what we want. So let’s modify it.

rry

Step 5. Remove Non-Required Data.

Here we only need No. of Observations and Cumulative%, which means we need to get rid of cumulative frequency from this chart.

Right click on the chart and click on “Select Data”.

ty4

“Select Data Source” window will be displayed.
hj

Select “Cumulative Frequency” and Click on the remove button. Now, this is what you have.

eyh

It still doesn’t look like what we want.

Step 6. Make Cumulative% a secondary axis.

Right click on the Cumulative% line. Click on the Format Data Series option. You will have a window opened in front of you.

tt

Select the Secondary Axis radio button and hit it close.

Now we have this chart. We just need to convert the “Number of Observations” line graph into a bar graph.
dsa

Step 7. Change No. of Observation into the Bar graph.

Right click on the “No. of observation” and click on the “Change Series Chart Type” option.

hh

Select “Clustered Column” and hit “OK” Button.

Now, this is what we wanted. This is a classic Pareto Chart.
wg

Step 8: Add Data Labels.

Right Click on the Cumulative% line and click on “Add Data Labels”.

gtw

And it’s done. This is the Pareto Graph for our data.

From this chart, we can say that 67% of problems are due to Res 1, Res 2, and Res 3. And they are People who Don’t Know About The Product, Don’t Drink In Winters and Unavailable In Area.

Hence, if you can eliminate these 3 problems, you will recover 67% of your customers.

In this session, we learned how to do the Pareto Analysis easily. I tried to explain each and everything with examples. I hope it was resourceful for you. If you missed your query, feel free to ask in the comments section. I am quite active here.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube