SAP - Advanced Excel Chart

Back to Course

Lesson Description

Lession - #495 Advanced Excel Charts – pareto Chart

Pareto map is extensively used in Statistical Analysis for decision- timber. It represents the Pareto principle, also called the80/20 Rule.
Pareto Principle(80/20 Rule>
Pareto principle, also called the80/20 Rule means that 80 of the results are due to 20 of the causes. For illustration, 80 of the defects can be attributed to the key 20 of the causes. It's 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 the80/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's a Pareto Chart?
A Pareto map is a combination of a Column map and a Line map. The Pareto map shows the Columns in descending order of the Frequencies and the Line depicts the cumulative totals of Categories.

Advantages of Pareto Charts
You can use a Pareto map 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 disfigurement causes and the separate counts are given.
Step 1 kind the table by the column- disfigurement Count in descending order( Largest to Smallest>
Step 2 produce a column Cumulative Count as given below-

This would result in the following table-
Step 3 Sum the column Defect Count.
Step 4 produce a column Cumulative as given below.
Step 5 Format the column Cumulative as Percentage.

You'll use this table to produce a Pareto map.

Creating a Pareto Chart
By creating a Pareto map, you can conclude what are the key causes for the defects. In Excel, you can produce a Pareto map as a combo map of Column map and Line map.
Following are the way to produce Pareto map-

Step 1 Select the columns Defect Causes and Defect Count in the table.
Step 2 Insert a Clustered Column map.
Step 3 As you can see, the columns representing causes are in descending order. Format the map 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.
Step 4 Design the map as follows-

• Click on the map.

• 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 map 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 Map 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 map. Click OK.

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