Create Pareto Chart In Excel

excelsirji Excel Dashboarding Tips, Excel Data Tips

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:

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

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:

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

Last Point in Column D will show 100% as shown below:

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

This will insert a blank Chart on your screen where we will be creating Pareto Chart.
Right Click on blank chart and “Select 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

Special Note About Pareto Chart:
Pareto Chart user 2 y-axis and one x-axis. Therefore, we will be adding two y-axis on the chart

Now you will see the “Select Data” window again. So here you need to click “Add” again:

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

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”

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”