Create Pareto Chart In Excel


1

What is Pareto Chart?

Pareto principle was introduced by Italian Economist Vilfredo Pareto. He stated that 80% of the effects are caused by 20% of the causes. So if we closely monitor and solve 20% of the causes. It will significantly improves the affected area and you will see good results. 

For Example: 80% of the revenue gets generated by 20% of the clients

While doing research Mr. Pareto found that 20% of land in Italy was occupied by 80% of the population. He carried out this survey to other areas and got same results

This rule is also well known as 80/20 rule across the globe and is mostly being used to find the causes which is impacting business or producing defective products/services

Hence Pareto chart is a graphical representation of Pareto Principle

When to use Pareto Chart in your study?

Pareto Chart studies the frequency distribution and advise you the most impacted/affected areas. So you should use Pareto Chart when:

  • While analyzing defects/problems data, you find too many causes and want to focus most significant areas
  • While analyzing broad categories and you want to focus only on few causes which may improve the metric significantlly
  • When you have data frequency distribution and you want to analyze and focus on main contributing causes
  • In your project managements templates or dashboard making to summarize the data
  • Analyzing the Surveys and finding pain areas

How to create Pareto Chart in Excel?

Here are few steps to collect data before you start preparing your Pareto Chart in Excel:

  • Decided which data you want to study
  • It is always good, if you can group or categorize the data labels
  • Collect data frequency/numbers for each above categorized data labels
  • Sort your data in excel from Largest to Smallest as shown below:
Pareto Chart

Here I collated the Complaints data from one of the Courier Company survey and will create Pareto Chart for them. Lets follow the steps now:

  • Calculate cumulative frequency for each data point.

    In order to do this, add a column for Cumulative Frequency as shown below and link the first cell in this column with the first data point as shown (The Largest Number):
Pareto Chart Data
  • Second Row should be total of Last CF + Respective Data Point. You need to copy the same formula or drag the same till last data point. Please look at below image:
Pareto Chart Analysis
  • Last Row in Column C as per below image shows the total of Column B. Refer to below image:
  • Now Calculate CF% in another Column. Here it is column D which is dividing the

    = Cumulative Frequency / Total of Data Points
Cumulative Frequency
  • Last Point in Column D will show 100% as shown below:
Pareto Chart Analysis

Steps to Create Pareto Chart:

  • Click on any blank cell in Excel where you want to create the chart

    Select "Insert" option from menu bar and select 2-D Clustered Column Chart
Clustered Column Chart
  • This will insert a blank Chart on your screen where we will be creating Pareto Chart.

    Right Click on blank chart and "Select Data":
Pareto Chart Data
  • Now you will see below window on your screen. Click "Add"

  • Here we will be adding "y axis" entries with this step as shown in below image:

    Series Type: "Complaint"

    Series Values: This is data range which is currently "B2:B10" in displayed data and as this data is on Sheet1. So this will show like "=Sheet1!$B$2:$B$10" (Absolute Range)

    Once you complete above steps. Click "OK" and follow next step
  • Now you will see the "Select Data" window again. So here you need to click "Add" again:
Pareto Chart
  • Here we will be adding second "y axis" which is CF% with the same steps followed above. So once you click Add, enter the values as mentioned below:

    Series Type: "CF%"

    Series Values: This is CF% which was calculated in Column D which is currently "D2:D10" in displayed data and as this data is on Sheet1. So this will show like "=Sheet1!$B$2:$B$10" (Absolute Range)

    Once you complete above steps. Click "OK" and follow next step
Pareto Chart Analysis
  • Now we will be adding "x axis" which will denotes the complaints type at bottom. So that user can interpret the results for each complaint. So click on "Edit"
Adding X-Axis Pareto Chart Analysis
  • Below window will appear and then you need to select the "x-axis" range as below:

    Axis Label Range: Complaint Types which are currently in range "A2:A10" on Sheet1. So this will show like "=Sheet1!$A$2:$A$10" (Absolute Range)

    Once you complete above steps. Click "OK"
  • Now "Select Data" chart window will look like below and then click "Ok"
Pareto Analysis Y axis
  • Right Click on the chart and select "Change Chart Type" as shown below:
  • Below window will appear and then you need to follow steps as shown in the image in sequence:
Pareto Chart Samples
  • Here is your pareto chart ready:
Graphical Display Pareto Chart

So here your Pareto chart is ready. This will help you to showcase the major pain areas or impacted areas in visual display

Now if you want to make this chart more appealing and visual. Then you can do this by learning tricks about formatting charts.

Hope you liked this article. Please comment below for any questions and for your feedback about this tutorial.

Follow us by  Subscribe Us option for new updates.

Happy reading 🙂

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
Rieko Yamada Recent comment authors
newest oldest most voted
Rieko Yamada
Guest
Rieko Yamada

I really liked this article. Well drafted. Thanks for the help 🙂

Excel VLOOKUP
DATA ANALYSIS TRICKS
1
What is VLOOKUP in Excel?

An ultimate guide for basic user to understand Excel Vlookup function. VLOOKUP is a vertical lookup which helps the user to extract the values from other columns (leftmost) basis on matching column string.

Find Duplicate
DATA ANALYSIS TRICKS
Find Duplicates in Excel

Hope you read the post “Remove Duplicates in Excel”. Now I am going to explain how you can find these duplicates. There are multiple methods available to Find and Remove duplicates. So you may watch my detailed video on “Complete Guide for Handling Duplicates” Here I will be explaining one …

DATA ANALYSIS TRICKS
Create Dynamic Data Validation List – Offset Function Method

When I started my career in working with excel dashboards, I always used to face most common challenge in “Data Validation” technique where I want a smart data validation to avoid all blank cells and keep adding and deleting the values from drop down dynamically. Here we want to talk …

error

Like the Article? Please spread the word :)

Facebook
Facebook
YouTube
YouTube
Pinterest
Pinterest
LinkedIn