Introduction
You must have heard about the 80/20 rule, and it is quite popular, isn't it? The 80/20 rule was given by Vilfredo Pareto and is also called the 'Pareto Principle.' According to the Pareto principle, roughly 80% of consequences come from 20% of causes (the "vital few") for many outcomes.
Pareto chart is an application of the Pareto principle, which is widely used in statistical analysis of decision making. In this article, we will learn about the Pareto chart and the representation of data by Pareto chart in excel.
Pareto Chart
A Pareto chart is a hybrid of a column and a line chart. The columns in the Pareto chart represent the 'Frequencies' in descending order, while the Line represents the cumulative totals of 'Categories.'
Preparation of Data
Consider the following data, where the restaurant complaints and the respective counts are given.
Step 1: First, we will sort the table by the column- count in descending order(Largest to smallest). You can get the sort option in the Home tab; you can also search for sort in 'help' for sort.
The sorted table will look like this:
Step 2: Create a new column cumulative count. It will contain cumulative frequency counts. To calculate the cumulative frequency, select the topmost cell to apply the cumulative frequency formula. You can also do this as for the first row, copy the data as it is since it will be the same. For the next row, write(=) and then select the cell above it and left to it( it will look something like =C2+B3), then press enter.
Then drag the cell by clicking the '+' icon, which appears on the hovering bottom right corner of the cell. The cumulative count for all the rows will be updated for the whole column. This would result in the following table:
Step 3: Sum the column count. It will be the same as the last value of the cumulative count column(here 558).
Step 4: Create a new column Cumulative%. Apply the formula for cumulative % on the first cell, then drag the cell down by hovering over the '+' to fill all the values of the column.
The table will look as:
Now the table is ready, and we will use this table to create a Pareto chart.
Creating a Pareto Chart
By creating a Pareto chart of the above data, we can conclude the major complaints that are causing problems. Follow the steps to create a Pareto chart:
Step 1: Select the columns complaint type and count in the table.
Step 2: On the Insert tab, click on the charts group, then select the Histogram symbol.
Step 3: Click Pareto.
The result will look similar to that given below(this Pareto chart will be a combination of line and column chart):
Step 4: Enter the title for the chart by clicking the title and then modifying it.
Step 5: Click the + button on the right side of the chart and click the checkbox next to Data Labels or any other chart element you want to include.
Result
The Pareto chart for restaurant complaints has been created. Here the orange Pareto line shows that 80% of the complaints come from 2 complaint types (Wait time and Food quality). In other words: the Pareto principle applies.
569+ registered 
