BLOG – PRODUCTIVITY TOOLS AND MORE…

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

Overview

This tutorial explains how to do a manual Pareto Analysis in Excel in 5 simple steps. A Pareto Analysis is particularly useful to focus on what really matters as the Pareto principle states that, for many events, roughly 80% of the effects come from 20% of the causes.

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. 

A Pareto Analysis in Excel uses the 80/20 rule approach to find out what matters
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”

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.

Best Microsoft Excel Guide Books – Top 3 for Data Analysis

These 3 Best Microsoft Excel Guide Books provide Excel users with Powerful Data & Business Analysis techniques that go beyond Pareto Analysis and Pareto Charts.

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 powerful 20/80 Rule to your life. This article shows...

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

This tutorial explains how to do a manual Pareto Analysis in Excel in 5 simple steps. A Pareto Analysis is particularly useful to focus on what really matters as the Pareto principle states that, for many events, roughly 80% of the effects come from 20% of the causes.

The Powerful 80/20 Principle: The Secret of Achieving More with Less

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

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.

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 by doing less is even better. Some say it is an easily actionable insight for better productivity. Dropping the...

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 the Pareto Law:The 80/20 Rule The 80 20 Rule states that you get 80 percent of the value out of something from 20...

Pareto Analysis Excel Add-In

Buying the Pareto Analysis Excel Add-In is a risk-free experience. If you are not happy with it, you simply get your money back. No questions asked.

Execute Better.  Execute Faster. Automate any Excel Pareto Analysis.
This Easy Pareto Analysis Excel Add-In makes my life easier

ENJOY 60% OFF by subscribing to our newsletter :)

 

Strictly No Spam. Unsubscribe Anytime.

You have successfully subscribed! Thank you!

Pin It on Pinterest

Share This