In this Tutorial we will learn how to create Pareto Chart in Excel
What is Pareto chart?
Pareto charts are a combination of a line graph and a bar graph. Bar Graph indicates the value and Line Graph indicates the cumulative percentage. With the help of line graph one can identify the significant influencing factors. This Chart in named after Pareto Principle, which suggests that 80% of problems can be traced to as few as 20% of root causes.
We will use the Pareto chart to measure the company’s monthly expense. So that we can identify what are the 20 % factors contributes major (80%) of the expenses.
Data used for creation of Excel Pareto chart is shown below. This data is about monthly expenses of a company
| Expenses | Amount |
|---|---|
| Business Insurance | 2000 |
| Electricity Bill | 7000 |
| Employee Salary | 130000 |
| Floor Maintenance | 5000 |
| Floor Rent | 60000 |
| Miscellaneous Expenses | 4000 |
| Security Expenses | 3500 |
| Server maintenance | 8500 |
| Software Licence Renewal | 11000 |
| Travel & Entertainment | 3500 |
Pareto Chart in Excel Step 1:
Sort your data from largest to smallest amount
Pareto Chart in Excel Step 2a:
Create a cumulative amount column. To start with cumulative amount cell C2=B2
Step 2b: Each amount builds on the one before it as shown below i.e. C3=C2 B3. Then drag till the end.
So the resultant table will be
Pareto Chart in Excel Step 3:
Next, create a Cumulative Percentage column, Enter D2 as “=C2/$C$11” as shown below
Drag till the end so the final table will be
Pareto Chart in Excel Step 4:
Select the data click insert tab -> recommended chart, click on the chart as shown below
So the resultant chart will be
Pareto Chart in Excel Step 5:
Right click on any of the bars and click Change Series Chart Type.
Under % of cumulative amount choose line with markers. Click on Secondary Axis tick box
So the chart will be
Pareto Chart in Excel Step 6:
Right click on the red series bar and delete it. So the final chart will be
We have successfully created Pareto chart in Excel.
Conclusion: As per Pareto Principle, in our example Employee Salary and Floor Rent alone contributes to 80% of the total expenses as shown above in the green line chart.
