Advanced Excel - Pareto Chart

Pareto chart is widely used in Statistical Analysis for decision-making. It represents the Pareto principle, also called the 80/20 Rule.

Pareto Principle (80/20 Rule)

Pareto principle, also called the 80/20 Rule means that 80% of the results are due to 20% of the causes. For example, 80% of the defects can be attributed to the key 20% of the causes. It is also termed as vital few and trivial many.

Vilfredo Pareto conducted surveys and observed that 80% of income in most of the countries went to 20% of the population.

Examples of Pareto Principle (80/20 Rule)

The Pareto principle or the 80/20 Rule can be applied to various scenarios −

• 80% of customer complaints arise from 20% of your supplies.
• 80% of schedule delays result from 20% of the key causes.
• 80% of a company profit can be attributed to 20% of its products.
• 80% of a company revenues are produced by 20% of the employees.
• 80% of the system problems are caused by 20% of causes of defects.

What is a Pareto Chart?

A Pareto chart is a combination of a Column chart and a Line chart. The Pareto chart shows the Columns in descending order of the Frequencies and the Line depicts the cumulative totals of Categories.

A Pareto chart will be as shown below −

Advantages of Pareto Charts

You can use a Pareto chart for the following −

• To analyze data about the frequency of problems in a process.
• To identify the significant causes for problems in a process.
• To identify the significant areas of defects in a product.
• To understand the significant bottlenecks in a process pipeline.
• To identify the largest issues being faced by a team or an organization.
• To know the top few reasons for employee attrition.
• To identify the topmost products that result in high profit.
• To decide on the significant improvements that increase the value of a company.

Preparation of Data

Consider the following data, where the defect causes and the respective counts are given.

Step 1 − Sort the table by the column - Defect Count in descending order (Largest to Smallest).

Step 2 − Create a column Cumulative Count as given below −

This would result in the following table −

Step 3 − Sum the column Defect Count.

Step 4 − Create a column Cumulative % as given below.

Step 5 − Format the column Cumulative % as Percentage.

You will use this table to create a Pareto chart.

Creating a Pareto Chart

By creating a Pareto chart, you can conclude what are the key causes for the defects. In Excel, you can create a Pareto chart as a combo chart of Column chart and Line chart.

Following are the steps to create Pareto chart −

Step 1 − Select the columns Defect Causes and Defect Count in the table.

Step 2 − Insert a Clustered Column chart.

Step 3 − As you can see, the columns representing causes are in descending order. Format the chart as follows.

• Right click on the Columns and click on Format Data Series.
• Click SERIES OPTIONS in the Format Data Series pane.
• Change the Gap Width to 0 under SERIES OPTIONS.
• Right click on the Columns and select Outline.
• Select a dark color and a Weight to make the border conspicuous.

Your chart will be as shown below.

Step 4 − Design the chart as follows.

• Click on the chart.
• Click the DESIGN tab on the Ribbon.
• Click Select Data in the Data group. The Select Data Source dialog box appears.
• Click the Add button.

The Edit Series dialog box appears.

Step 5 − Click on the cell – Cumulative % for Series name.

Step 6 − Select the data in Cumulative % column for Series values. Click OK.

Step 7 − Click OK in the Select Data Source dialog box. Your chart will be as shown below.

Step 8 − Click the DESIGN tab on the Ribbon.

Step 9 − Click Change Chart Type in the Type group.

Step 10 − Change Chart Type dialog box appears.

• Click the All Charts tab.
• Click the Combo button.
• Select Clustered Column for Defect Count and Line for Cumulative %.
• Check the box – Secondary Axis for Line chart. Click OK.

As you can observe, 80% of the defects are due to two causes.