by 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 in Excel
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:
- In economy, the richest 20% of the world's population control about 80% of the world's income.
- In medicine, 20% of patients are reported to use 80% of health care resources.
- In software, 20% of bugs cause 80% of errors and crashes.
To identify the most significant factors that you should focus on, you can draw a Pareto chart in your Excel worksheet.
Pareto chart in Excel
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.
How to make a Pareto chart in Excel 2016 - 365
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:
- Select your table. In most cases it is sufficient to select just one cell and Excel will pick the whole table automatically.
- On the Insert tab, in the Charts group, click Recommended Charts.
- Switch to the All Charts tab, select Histogram in the left pane, and click on the Pareto thumbnail.
- Click OK.
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:
Customizing Excel Pareto graph
The Pareto chart created by Excel is fully customizable. You can change the colors and style, show or hide data labels, and more.
Design the Pareto chart to your liking
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:
Show or hide data labels
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:
How to create a Pareto chart in Excel 2013
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.
Organize data for Pareto analysis
Set up your data set as explained below:
1. Calculate cumulative total percentage
Add one more column to your data set and enter the cumulative total percentage formula there:
=SUM($B$2:B2)/SUM($B$2:$B$11)
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).
2. Sort by count in descending order
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:
Draw a Pareto chart
With the source data properly organized, creating a Pareto graph is as easy as 1-2-3. Literally, just 3 steps:
- Select your table or any cell within it.
- On the Insert tab, in the Charts group, click Recommended Charts.
- Switch to the All Charts tab, select Combo on the left side, and make the following modifications:
- For the Count series, select Clustered Column (default type).
- For the Cumulative % series, select the Line type, and check the Secondary Axis box.
The chart that Excel inserts in your worksheet will resemble this one:
Improve the Pareto chart
Your chart already looks very much like a Pareto diagram, but you may want to improve a few things:
1. Set the maximum percentage value to 100%
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:
2. Remove extra spacing between bars
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%:
Finally, change the chart title, and optionally, hide the chart legend.
What you now have looks like a perfect Excel Pareto chart:
How to draw a Pareto chart in Excel 2010
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.
- Organize your data like explained earlier: sort by count in descending order and calculate cumulative total percentage.
- Select your table, go to the Insert tab > Charts group, and choose 2-D Clustered Column chart type:
This will insert a column chart with 2 series of data (Count and the Cumulative percentage).
- Right click the Cumulative % bars, and click Change Chart Series Type. (This may be the trickiest part because the bars are very small. Try hovering your mouse over the bars until you see the Series "Cumulative %" hint, and then right-click.)
- In the Change Chart Type dialog box, pick a Line
- At this point, you have a bar chart with a flat line along the horizontal axis. To give it a curve, you need to put a secondary vertical axis on the right-hand side. For this, right click the Cumulative % line, and then click Format Data Series…
- In the Format Data Series dialog box, choose Secondary Axis under Series Options, and close the dialog:
- Make the finishing touches: set the maximum value for the secondary vertical axis to 100%, make the bars wider, and optionally, hide the legend. The steps are basically the same as in Excel 2013 described above.
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!
19 comments
Great explanation. Thanks.
Dear Ms. Cheusheva:
Please allow me to bring to your attention a couple of typing mistakes under instruction 2 below:
To make a Pareto graph in Excel, please follow these simple steps:
1. Select your table. In most cases it is sufficient to select just one cell and Excel will pick the whole table automatically.
2. On the Inset (should be Insert) tab, in the Chats (should be Charts) group, click Recommended Charts.
3. Switch to the All Charts tab, select Histogram in the left pane, and click on the Pareto thumbnail.
4. Click OK.
Once again, keep the good work up!
Dr. Elias M. Choueiri
Dear Dr. Elias,
Thank you for pointing those out! Two "r" missing in one point - perhaps the R key was stuck :) Fixed, thank you!
Dear Ms. Cheusheva:
As always, I have nothing but a strong admiration for your style of writing. You always convey what you are trying to get across in a straightforward way and in very simple, friendly language. I always refer my students to your pages for learning. Without a doubt, I always receive from them a positive feedback and a strong admiration for your writing style! Keep the good work up for the benefit of humanity!
GOD bless!
Dr. Elias M. Choueiri
Very help ful .
Thank you very very much
so at the end what does the chart suppose to look like
Thanks lot..
thank you this helps me a lot
Which one of these instruction can be used for EXCEL version 2120?
Thorough and helpful instructions, I could find no better online. GREAT JOB!
This is very clear and helpful. Thanks a lot.
Hi, it was a very very helpful blog.
Thank you for posting it
thank you it was very helpful to me
Thank you! very detailed and it helped me a lot
Thank you.it was helpful to me.
Thanks , Really detailed demo is here , Keep it up , I appreciate your effort , It was helpful to me.
Dear Madam/Sir
Thanks, Very nice explanation has shown in this format,
Hi,
How can I copy numbers under conditions, in specific column in another sheets automatically?
For example, I have a column containing various numbers in sheet1 and I want positive numbers copied in sheet2 and negative number copy to sheet3 automatically.
thanks