How to Create Pareto chart in Excel

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

ExpensesAmount
Business Insurance2000
Electricity Bill7000
Employee Salary130000
Floor Maintenance5000
Floor Rent60000
Miscellaneous Expenses4000
Security Expenses3500
Server maintenance8500
Software Licence Renewal11000
Travel & Entertainment3500

Pareto Chart in Excel Step 1:

Sort your data from largest to smallest amount

pareto chart in excel 1

Pareto Chart in Excel Step 2a:

Create a cumulative amount column. To start with cumulative amount cell C2=B2

pareto chart in excel 2

Step 2b: Each amount builds on the one before it as shown below i.e. C3=C2 B3. Then drag till the end.

pareto chart in excel 3

So the resultant table will be

pareto chart in excel 4

Pareto Chart in Excel Step 3:

Next, create a Cumulative Percentage column, Enter D2 as “=C2/$C$11” as shown below

pareto chart in excel 5

Drag till the end so the final table will be

pareto chart in excel 6

Pareto Chart in Excel Step 4:

Select the data click insert tab -> recommended chart, click on the chart as shown below

pareto chart in excel 7

So the resultant chart will be

pareto chart in excel 8

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

pareto chart in excel 9

So the chart will be

pareto chart in excel 10

Pareto Chart in Excel Step 6:

Right click on the red series bar and delete it. So the final chart will be

pareto chart in excel 11

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.

previous small pareto chart in excel                                                                                                                previous small pareto chart in excel