BLOG – PRODUCTIVITY TOOLS AND MORE…

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

Jun 4, 2023 | 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 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”.

Excel Pareto Analysis - Step 1 - Calculate the total effect
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”.

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.

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

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

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…

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

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.

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.

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

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.

1 Easy and Smart Way of Getting Things Done

Getting things done is a prerequisite to productivity. Ray Dalio explains how to enhance productivity with the 20/80 Rule in his brilliant post. Achieving more by doing less is even better. Some say it is an easily...

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?

The Powerful 80/20 Rule | Pareto Law | Pareto Rule by Ray Dalio

The famous American hedge fund billionaire Ray Dalio made this brilliant post about the 20/80 Rule, which is also sometimes called the Pareto Rule or Pareto Law:The 80/20 Rule The 80 20 Rule states that you get 80...

Wellness 101: Pareto Your Time & Energy To Be Your Best-Self

Pareto your time and energy so you’re always the best of yourself. Mood, Motivation, and Ability vary across the day: Pareto your time for the best outcome.

The Powerful 80/20 Principle: How to Achieve More with Less

The Powerful 80/20 Principle: The Secret of Achieving More with Less. A new perspective on the Pareto Principle.

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 Analysis & Productivity Tools

Pin It on Pinterest