The tutorial explains the basics of the Pareto analysis and shows how to make a Pareto chart in different versions of Excel.
In a perfect world, everything would be in harmony - every job would pay the same, every taxpayer would get the same tax benefits, every player would be equally important to a team. But our world is not perfect, and the relationships between inputs and outputs are not equal. Do you want to know the principle causes to which you should devote the most efforts? It is what the Pareto principle, or the law of the vital few, is all about.
Pareto analysis is based on the Pareto principle, named after Italian economist Vilfredo Pareto. And this principle states that for many events about 80% of the effects come from 20% of the causes. Which is why, the Pareto principle is sometimes called the 80/20 rule.
Here are a few practical examples of the Pareto principle:
To identify the most significant factors that you should focus on, you can draw a Pareto chart in your Excel worksheet.
Pareto chart, also called a Pareto diagram, is a graph based on the Pareto principle. In Microsoft Excel, it is kind of sorted histogram that contains both vertical bars and a horizontal line. The bars, plotted in descending order, represent the relative frequency of values, and the line represents cumulative total percentage.
Here's what a typical Excel Pareto chart looks like:
As you see, the Pareto graph highlights the major elements in a data set and shows the relative importance of each element for the total. Below you will find the detailed instructions on how to create a Pareto diagram in different versions of Excel.
Plotting a Pareto diagram in modern versions of Excel is easy because it has a built-in Pareto chart type. All you need to have is a list of items (issues, factors, categories, etc.) in one column and their count (frequency) in another column.
As an example, we are going to do Pareto analysis of typical user complaints about software based on this data set:
To make a Pareto graph in Excel, please follow these simple steps:
That's all there is to it! The Pareto chart is immediately inserted in a worksheet. The only improvement that you'd probably want to make is to add/change the chart title:
The Pareto chart created by Excel is fully customizable. You can change the colors and style, show or hide data labels, and more.
Click anywhere in your Pareto chart for the Chart Tools to appear on the ribbon. Switch to the Design tab, and experiment with different chart styles and colors:
By default, a Pareto graph in Excel is created with no data labels. If you'd like to display the bar values, click the Chart Elements button on the right side of the chart, select the Data Labels check box, and choose where you want to place the labels:
The primary vertical axis showing the same values has become superfluous, and you can hide it. For this, click the Chart Elements button again, then click the little arrow next to Axes, and unselect the Primary Vertical Axis box.
The resulting Pareto chart will look similar to this:
Excel 2013 does not have a predefined option for the Pareto graph, so we will be using the Combo chart type, which is the closest to what we need. This will require a few more steps because all the manipulations that modern Excel performs behind the scene, you will have to do manually.
Set up your data set as explained below:
Add one more column to your data set and enter the cumulative total percentage formula there:
Where B2 is the first and B11 is the last cell with data in the Count column.
In the dividend, you put a cumulative sum formula that adds up the numbers in the current cell and in all cells above it. Then, you divide part by total to get percentages.
Enter the above formula in the first cell, and then copy it down the column. For the results to be displayed as percentages, set the Percent format for the column. If you'd like the percentages shown as integers, reduce the number of decimal places to zero (please see How to display decimal places in Excel for instructions).
Since the bars in a Pareto chart should be plotted in descending order, arrange the values in the Count column from higher to lowest. For this, select any cell and click A-Z on the Data tab, in the Sort and Filter group. If Excel prompts to expand the selection, do it to keep the rows together while sorting.
Alternatively, add auto filter to be able to re-sort data faster in the future.
At this point, your source data should look similar to this:
With the source data properly organized, creating a Pareto graph is as easy as 1-2-3. Literally, just 3 steps:
The chart that Excel inserts in your worksheet will resemble this one:
Your chart already looks very much like a Pareto diagram, but you may want to improve a few things:
By default, Excel has set the maximum value for the secondary vertical axis to 120% while we want it 100%.
To change this, right-click the percentages values on the Y-axis in the right-hand side, and choose Format Axis… On the Format Axis pane, under Bounds, set 1.0 in the Maximum box:
In a classic Pareto graph, the bars are plotted closer to each other than in a combo chart. To fix this, right-click the bars and choose Format Data Series… On the Format Data Series pane, set the desired Gap Width, say 5%:
What you now have looks like a perfect Excel Pareto chart:
Excel 2010 has neither Pareto nor Combo chart type, but that does not mean you cannot draw a Pareto diagram in earlier Excel versions. Of course, this will require a bit more work, but also more fun :) So, let's get started.
This will insert a column chart with 2 series of data (Count and the Cumulative percentage).
Voilà, your Pareto Chart in Excel 2010 is ready:
That's how to make a Pareto chart in Excel. If you'd like to learn about other chart types, I encourage you to check out the resources below. I thank you for reading and hope to see you on our blog again next week!
Table of contents