# 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
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 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.