The Pareto Principle, also known as the 80-20 rule, states that for many events, approximately 80% of the effects come from 20% of the causes. For example, 80% of your revenue comes from 20% of your customers.
In the example below, taken from a fictitious car hire company, you can see that approximately 80% of the complaints are coming from just 3 of the “complaint types” (late delivery of vehicles, incorrect invoices and unclean vehicles). By better-managing these 3 complaint types the company will reduce or remove 80% of the complaints.
Setting Up the Data
To create the above Pareto Chart in Excel 2016 (for Windows), first of all set up the underlying data. The complaint categories, displayed across the horizontal axis of the chart, should be listed down a column, one per cell (A3:A9 in this example).
The numbers to be plotted should be listed down the next column (B3:B9). In the above screenshot, the numbers represent the number of complaints against each complaint type.
The numbers in column C are for information only, i.e. they are not required for the chart. They indicate the percentage of the total for each type of complaint.
Creating the Chart
- Select the cells that contain the horizontal axis labels and the cells that contain the numbers to be used (i.e. A3:B9)
- Select the Insert tab on the Ribbon
- Click the Insert Statistic Chart button
- Click the Pareto Chart button
Although there are no markers on the line element of the Pareto Chart, if there were, they would indicate the percentage of the total for each item (in this example, this matches the figures in column C, although as I said previously, these figures are not required to exist in the spreadsheet).
In the screenshot at the top of this tutorial, there is a manually-drawn (using the Shape Tools) horizontal dotted line at the 80% mark and another manually-drawn vertical line where the Pareto line meets this line. This is not part of the Pareto Chart but is simply to indicate visually where the 80% occurs.