Advanced Excel - Pareto Chart


Advertisements

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 −

Pareto Chart

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.

Preparing Pareto Chart Data

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

Sort Table

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

Create Column Cumulative Count

This would result in the following table −

Column Cumulative Count Result

Step 3 − Sum the column Defect Count.

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

Create Column Cumulative

Step 5 − Format the column Cumulative % as Percentage.

Format Column Cumulative

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.

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.

Pareto Chart shown

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.
Design Pareto Chart

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.

Select data in Cumulative

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

Click OK in Select Data

Step 8 − Click the DESIGN tab on the Ribbon.

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

Click Change Chart Type

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.
Change Chart Type Dialog Box

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

Pareto Chart
Advertisements