Pareto Chart

 

A Pareto chart combines a column chart and a line graph. The Pareto principle states that, for many events, roughly 80% of the effects come from 20% of the causes. In this example, we will see that 80% of the complaints come from 2 out of 10 (20%) complaint types.

Pareto Chart Data

Advertisement

1. First, sort your data in descending order. To achieve this, click a number, on the Data tab, click ZA.

Sort in Descending Order

2. Calculate the cumulative count. Enter the formula shown below into cell C5 and drag the formula down.

Calculate Cumulative Count

3. Calculate the cumulative %. Enter the formula shown below into cell D4 and drag the formula down.

Calculate Cumulative Percentage

Note: Cell C13 contains the total number of complaints. When we drag this formula down, the absolute reference ($C$13) stays the same, while the relative reference (C4) changes to C5, C6, C7, etc.

4. Select the data in column A, B and D. To achieve this, hold down CTRL and select each range.

Select Data

5. On the Insert tab, click Column and select Clustered Column.

Insert Clustered Column Chart

6. Right click on the red bars (cumulative %) and click Change Series Chart Type...

Change Series Chart Type

Advertisement

7. Select Line with Markers.

Line With Markers

8. Right click on the red line and click Format Data Series...

Format Data Series

9. Select Secondary Axis.

Plot Series on Secondary Axis

10. Right click the percentages on the chart, click Format Axis, set the Maximum to 100 and click Close.

Format Axis

Result:

Pareto Chart

Conclusion: the Pareto chart shows that 80% of the complaints come from 20% of the complaint types (Overpriced and Small portions). In other words: the Pareto principle applies.

Advertisement

Like this free tutorial? Please share, pin, tweet or +1 this page.

Go back to charts, Go to the right >>
Go to Top: Pareto Chart    |    Go to Next Example: Group Pivot Table Items