How to create Pareto chart in Excel

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:
Pareto chart in Excel

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

Plotting a Pareto diagram in Excel 2016 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:
Data for Pareto analysis

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 tab, in the Chats 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.

Creating a Pareto chart in Excel 2016

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:
Pareto chart in Excel 2016

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:
Design the Pareto chart to your liking

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:

Show or hide data labels on a Pareto chart.

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:
Pareto chart with data labels

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 Excel 2016 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:

Data for a Pareto chart in Excel 2013

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:

  1. Select your table or any cell within it.
  2. On the Inset tab, in the Chats group, click Recommended Charts.
  3. 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.

Creating a Pareto chart in Excel 2013

The chart that Excel inserts in your worksheet will resemble this one:
A Pareto chart in Excel 2013

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:
Set the maximum percentage value to 100%.

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%:
Remove extra spacing between the bars of a Pareto chart.

Finally, change the chart title, and optionally, hide the chart legend.

What you now have looks like a perfect Excel Pareto chart:
an improved Pareto chart in Excel

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.

  1. Organize your data like explained earlier: sort by count in descending order and calculate cumulative total percentage.
  2. Select your table, go to the Insert tab > Charts group, and choose 2-D Clustered Column chart type:
    Insert 2-D Clustered Column chart type.

    This will insert a column chart with 2 series of data (Count and the Cumulative percentage).

  3. 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.)
    Changing the chart series type for Cumulative percentage.
  4. In the Change Chart Type dialog box, pick a Line
    The chart series type for Cumulative percentage is changed to Line chart.
  5. 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
    Right click the Cumulative % line, and then click Format Data Series….
  6. In the Format Data Series dialog box, choose Secondary Axis under Series Options, and close the dialog:
    Adding a secondary vertical axis to the right side of the Pareto chart.
  7. 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:
Pareto Chart in Excel 2010

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!

You might also be interested in:

One Response to "How to create Pareto chart in Excel"

  1. Ali says:

    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

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
33
Ultimate Suite 2018.5 for Excel
33
60+ tools for Excel
November offer: Nov. 5 – Nov. 21