Create Pareto Chart In Excel
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
- 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"
- Now "Select Data" chart window will look like below and then click "Ok"
- 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:
- Here is your pareto chart ready:
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
I really liked this article. Well drafted. Thanks for the help 🙂