BLOG – PRODUCTIVITY TOOLS AND MORE…

How to do a Pareto Analysis in Excel in 5 simple steps – Easy tutorial

Aug 30, 2020 | Microsoft Excel Tutorial

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 effect of each cause
  3. Sort causes by decreasing effects
  4. Calculate the cumulative effect for each cause
  5. Filter out causes 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 are those that 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 reasons, 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 however important 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:

How to do a Pareto Analysis in Excel in 5 simple steps - Easy tutorial 1
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”.

Excel Pareto Analysis - Step 1 - Calculate the total effect
STEP 2 – Calculate the relative effect for each cause (ie the ratio cause’s 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 “$” symbol is called an absolute reference . An absolute reference allows you to drag the formula down to 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 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 interchangbly with the word “Effect”.

Excel Pareto Analysis - Step 2 - Calculate the relative impact of each cause
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.

Excel Pareto Analysis - Step 3 - Sort causes by decreasing effects

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.

How to do a Pareto Analysis in Excel in 5 simple steps - Easy tutorial 2
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”:

Excel Pareto Analysis - Step 4 - Calculate the cumulative impact of each cause

 

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”:

How to do a Pareto Analysis in Excel in 5 simple steps - Easy tutorial 3
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”.

Excel Pareto Analysis - Step 5 - Filter out causes whose cumulative impact is above 80%

 

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

How to Make a Simple Pareto Analysis in Excel in 5 steps

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 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.

Pareto Analysis in Excel: additional filters

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. 

0 Comments

Trackbacks/Pingbacks

  1. Create a Pareto Chart in Excel in 2 steps - Easy Tutorial - […] Pareto Analysis can be done manually as explained in this Pareto Analysis Excel tutorial.  To automate a Pareto Analysis…
  2. The Powerful Pareto Analysis or 20/80 Rule in Excel: FAQ - One-click Excel Pareto Analysis Add-In - […] Manual Excel Pareto Analysis […]
  3. Why Your Best Time-management Tool is Useless? 1 explanation - […] techniques. Using a proven prioritization process like the Eisenhower Method or running a simple Excel Pareto Analysis of the…
  4. Best Microsoft Excel Guide Books – Top 3 for Data Analysis - […] Excel tutorials, articles and resources related to the Pareto Analysis and Pareto Chart topics, we encourage you to check…

Create a Pareto Chart in Excel in 2 steps – Easy Tutorial

The tutorial shows how to create a Pareto Chart in Excel in 2 simple steps after explaining the basics of the Pareto Principle or 20 80 rule.

How to build a Pareto Chart in Google Sheets in 2 Easy Steps

This easy step-by-step tutorial shows how to build a Pareto Chart in Google Sheets. Download now the Free Google Sheets Pareto Chart template for immediate use.

Time Management: 1 Simple Eisenhower Matrix to Get it Right

The Eisenhower Matrix or Urgent-Important Matrix is a highly effective time management tool that uses 4 quadrants to prioritize tasks and increase productivity

Optimize Easily your Well-being with the 80/20 rule Approach

Happiness, the feeling of positivity, really is the foundation of productivity. - Miguel McKelvey Everyone deserves to have a happy and fulfilled life. Take control now of your well-being by applying the simple yet...

3 Tricks To Turn The Shiny Object Syndrome To Your Advantage

The shiny object syndrome is bad for productivity. Focus is key. Execution is everything. How do you stay focus and execute with all these new ideas in mind?

Why Your Best Time management Tool is Useless? 1 explanation

The Best Time Management Tools provide optimal task planning. That’s a great start, but that’s not enough. Learn here how to make your ambitions come true.

The Powerful Pareto Analysis Approach and 20/80 Rule: FAQ

All about the Pareto Principle, Pareto Analysis, 80/20 Rule, Pareto Law, and the Law of the Vital Few. Free Microsoft Excel & Google Sheets Tutorials & Templates

Work Smarter – Get Help Right Now From an Excel VBA Expert

No time to learn VBA for your Excel project? Get it done: Ask a Microsoft Excel VBA Expert. Live Excel Help, Online Excel Assistance, Overnight Project Delivery

Pareto Efficiency / Pareto Optimality: 1 Perfect Allocation

Pareto Efficiency / Pareto Optimality is the highest efficiency level resulting from an optimal allocation where any change to it would make something worse off

Murphy’s Law: 1 Gloomy View On How Things Usually Turn Out

Murphy’s Law is the adage that “Anything that can go wrong will go wrong.” People give Murphy’s Law relevance when things go awry as a reason to why it happened

Laborit’s Law / Law of the Least Effort: 1 clear explanation

Laborit’s Law or the Law of Least Effort suggests that humans prefer to carry out simple tasks that give immediate satisfaction to avoid stress or inconvenience

Pin It on Pinterest