Pareto Analysis in Excel – Tutorial Summary

To do a Pareto Analysis in Excel in 5 simple steps, execute the following:

1. Calculate the total effect
2. Calculate the relative impact of each cause
3. Sort causes by decreasing effects
4. Calculate the cumulative effect for each cause
5. Filter out grounds when the cumulative effect is above 80%

The Pareto Analysis is completed after applying the filter in STEP 5 that excludes the least important causes. The remaining causes after filtering are those that generate roughly 80% of the total effect. As a result, these remaining causes deserve the most attention.

Pareto Analysis in Excel – Tutorial in 5 Steps

In this Excel tutorial, a Pareto Analysis is performed to highlight the products that generate most of the sales among a list of 1000 products. For clarity, the entire list of products is not visible in the Excel screenshots below (some rows are hidden). When doing a Pareto Analysis in Excel, it is essential that no cause (here, the product’s reference) is omitted. To do so, unhide all relevant rows, if any. After following the Pareto Analysis 5-step process below, a similar distribution may be observed:

STEP 1 – Calculate the total effect

Calculate the total effect of the measure you are interested in by summing output cells’ values. In our example, we are interested in knowing how each product impacts the “Total Sales”.  The total effect of all products is the sum of the products’ sales “Total Sales”.

STEP 2 – Calculate the relative effect for each cause (ie the ratio cause’s the corresponding effect / total effect)

Calculate the relative effect of each cause by dividing its corresponding effect by the total effect calculated in STEP 1.

In our example, it means dividing the corresponding “Product Sales” by the “Total Sales” for each product. When typing the formula for the first relative effect, ensure that you have no “$” symbol for the cause’s effect (here “C8” for the first product), but you do have a “$” symbol for the total effect’s cell reference (here $C$5).  An Excel cell reference with the “$” symbol is called an absolute reference. An absolute reference allows you to drag the formula down to the bottom of your data set and keep this reference unchanged. This is exactly what we want in this case for the total effect. Dragging down the first formula you entered to the bottom of the Excel data set is the fastest solution to replicate the relative effect formula for all causes. To do so, select the first formula you entered at the top of the Excel data set and drag it to the bottom. For clarification purposes, please note that in this Excel Pareto Analysis Tutorial, the word “Impact” is used interchangeably with the word “Effect”.

STEP 3 –  Sort causes by decreasing effects

Firstly, add filters on the relevant area. To do so select the whole area. Then use the shortcut “Ctrl+Maj+l” or select “Data> Filter” in the ribbon.

Secondly,  sort the impact of single effects from the most important to the least important one. To do so,  select “Sort Largest to Smallest” on the drop-down arrow.

STEP 4 – Calculate the cumulative effect for each cause

Calculate the cumulative effect for each cause by adding its relative effect (calculated in STEP 2) to the prior except for the first one which does NOT have any prior. In our example, the product generating the highest sales has its  “Product Sales Relative Impact” equals to the “Product Sales Cumulative Impact”:

In our example – for each product except the first one having the highest sales – the “Product Sales Cumulative Impact” is equal to the “Product Sales Relative Impact” of the product to which is added the prior “Product Sales Cumulative Impact”:

STEP 5 – Filter out causes when the cumulative effect is above 80%

First, extend the filters to the column created in the prior step by clicking twice on “Filter” in the ribbon.

Secondly, click on the filter arrow and select “Number filter > Less Than or Equal To”.

Thirdly, enter “80%” (or any other target you may find relevant in your specific context) and click enter.

After applying the filter,  the Pareto Analysis is completed: the remaining inputs (“causes”) are those that drive roughly 80%  of the total output (“total effect”). In our example, after applying the filter, the remaining 200 products  (20% of the causes) make up 80% of the “Total Sales” (80% of the total effect). We can now focus our commercial efforts on these products.

FACULTATIVE STEP –  Add filtered counts and sums to play around with data and different target thresholds

Filtered counts (for the quantity) and filtered sums (for the sales) and their respective  %  value can be added. It helps assess different results arising from a different target threshold  (ex: 90% instead of 80% as defined in STEP 5). Remember, that the 20/80 Pareto distribution is a general tendency but not a systematic observation.

For the quantity after filters, since they are defined by the number of references, use the Excel function “=SUBTOTAL(3, “range of products reference”). For the sales after filters, since they are already numerical values, use the Excel function “=SUBTOTAL(9, “range of products sales”). For more details, you can download the Pareto Analysis Excel Template below.

Pareto Analysis in Excel – Free Templates

You can download the free template used as a Pareto example  in this Pareto Analysis tutorial:

  >> Download the Free Excel Pareto Analysis Template <<

You can download the free template provided by Microsoft Office that combines a Pareto Analysis in a table with a Pareto Chart:

>> Download the Free Cost Analysis with Pareto Chart Template <<

Pareto Analysis in Excel – Save Time by Automating the Pareto Analysis Process

Excel users who have to perform a Pareto Analysis on a recurring basis can save time and automate any Excel Pareto Analysis in just one-click with our simple yet powerful Excel Pareto Analysis Add-In:

 >> Automate any Pareto Analysis in Excel – Watch the DEMO here <<

Pareto Analysis In Excel  – Direct Pareto Analysis in Excel or  Pareto Chart? What is the best?

In some circumstances, a Pareto chart may be scattered with too many pieces of information and may not yield clear visualizable and actionable insights.  It happens when the data set is large and contains numerous items (“causes”) to analyze.

In such a case, we recommend you to crunch the data only with a direct Pareto Analysis like exposed in the tutorial above.  To automate a Pareto Analysis in just one-click like shown in the DEMO section of this website,  you can use an Excel Pareto Analysis Add-In. An Excel Add-in is particularly useful for Excel users who have to do Pareto Analysis in Excel on a frequent basis. The Excel Pareto Analysis Add-In also helps to save time when the data-set to analyze is defined with active filters in Excel. These filters can stay in place when using the Add-in hence avoiding additional time-consuming steps of data preparation.

“A good business strategy is an operational execution driven by a Pareto Analysis”

Conclusion and Suggestions

For more details about the Pareto Analysis underlying theories, please refer to our Frequently Asked Questions section. Microsoft Excel users who frequently do manual Pareto Analysis can automate any Excel Pareto Analysis in just one-click with our Excel Pareto Analysis Add-In.  Please refer to the DEMO section of this website for more information about automating Pareto Analysis in Excel.