Pareto Analysis With Excel

Pareto Analysis is commonly used in reporting and everybody seems to like it. It is probably because most people understand it. That’s why I decided to make a tutorial about how to make Pareto Analysis with excel.

Pareto Analyses is built on the Pareto Principle which states “80% of meaningful results are originated from 20% of sources”. This idea suggests that it is a good practice to focus your resources on these critical 20% to improve your results with grater efficiency.

This principle can be implemented in a lot of areas (here are some examples):

• Business: 20% of your customers makes 80% of your revenue
• Quality: 20% of causes results 80% of defects
• Economy: 20% of population holds 80% of capital

For more detail you can check this page. I personally believe that though 20/80 ratio is not necessarily absolute, this approach is good to see distribution of  causes of your results and make decisions.

Here is how to make a a quick Pareto Analysis with excel (customer value example):

First thing you need is to arrange your data. Make a table of your customers with their sale revenue. Then sort the list for revenue to get a list with revenues listed from bigger to smaller.

Now, in order to locate our 20% important customers we need a cumulative percentage. Take a sum of revenue column at the bottom. Than into the cell next to first revenue, input this formula (=SUM($C$5:C5)/$C$15             assuming Cell C5 holds revenue for cust 1 and cell C15 holds total). Copy this formula downwards and format these cells as percentage to get your cumulative percentages.

Now you can clearly see that while first 3 customers pays you 80% of your total revenue, remaining 7 customers pays you remaining 20% revenue.  So you can focus more on making these top 3 customers happy for keeping most of your revenue steady (then perhaps even improve it).

Pareto Chart:

Best way to visualize your Pareto Analysis is naturally  to use a Pareto Chart. This chart will show how your customers placed in terms of revenue and which are the most important ones in Pareto perspective.

Pareto Chart is a combined chart of two series, one represented as column, other as line. There is also a 3rd series which marks 80% level on the chart. So add a new column on the right side of our table which has 80% as value in all cells.

Insert a chart and select customers as horizontal axis. Sales Revenue as column (primary axis), cumulative percentage and limit as line (secondary axis). If you are struggling at this point here is how to do it.

Rest of it is all formatting. At the end, you will have a chart like the one above. One can see the same information from this chart as the table we made. But of course it is much more presentable.

If you like this tutorial, you can download the example workbook and practice with it.