Create Pareto Chart In Excel

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:

Pareto Chart - ExcelSirJi

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 by ExcelSirJi

  • 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 - ExcelSirJi

  • Last Row in Column C as per below image shows the total of Column B. Refer to below image:

Excel Tutorial

  • Now Calculate CF% in another Column. Here it is column D which is dividing the

    = Cumulative Frequency / Total of Data Points

Cumulative Frequency - ExcelSirJi

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

Pareto Chart Analysis - ExcelSirJi

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

  • 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 - ExcelSirJi

  • Now you will see below window on your screen. Click Add”

advanced excel tutorial

  • 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
learn excel online free

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

Pareto Chart - ExcelSirJi

  • 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 - ExcelSirJi

  • 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 - ExcelSirJi

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

best online excel courses

  • Now “Select Data” chart window will look like below and then click “Ok”

Pareto Analysis Y axis - ExcelSirJi

  • Right Click on the chart and select “Change Chart Type” as shown below:

Learn Excel Macros

  • Below window will appear and then you need to follow steps as shown in the image in sequence:

Pareto Chart Samples - ExcelSirJi

  • Here is your pareto chart ready:

Graphical Display Pareto Chart - ExcelSirJi

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 🙂

Author: was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.
  • Rieko Yamada says:

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Are you a Data Enthusiast?

Join us for a ride on your data and automate your stuffs