What is a Pareto Chart?

A Pareto Chart is a combination of a bar and a line charts, essentially using both in a single visualization. This chart type is especially useful for comparing the importance of items against other factors. Ideally suited for tracking indicators like acceptance metrics or production quality, Pareto charts are one of the key visualizations in Microsoft Excel.

In a typical use case, the bars are drawn in descending order to represent the frequency, and the line represents cumulative total percentage.

The Pareto Chart is based on Pareto principal, named after its founder. The theory indicates that 80% of effects come from 20% of the causes. A well-known example is that the richest 20% of the world population is controlling 82.7% of the world income. This principal also called the 80/20 rule.

Pareto Chart Excel Basics

A Pareto chart mainly consists of 2 objects:

• Bars with values in descending order
• A line for cumulative total percentage

These requirements demand data sorted accordingly to be displayed on two types of graphs.

Creating a Pareto Chart

Newer versions of Excel already have this chart in their repertoire. However, older versions might require a bit more work to get this chart. Let’s break it down for the two approaches.

Excel 2016

Excel 2016 comes with a Histogram tool featuring Pareto charts. This feature can allow you to create a Pareto chart very quickly. Let's see how you can do this on a sample data. You can download our sample workbook by pressing the button below.

Assume that you have a data table like below.

Begin by selecting the set of values to be used in the visualization, just like you would when creating any other chart.

Next, go to INSERT > Charts in the Ribbon, and click Histogram. From the list of options, select Pareto.

Your Excel Pareto chart will be created with values sorted automatically. You can always customize your Pareto chart, just like in other charts by going to CHART TOOLS > DESIGN in the Ribbon.

Excel 2013 and Before

Unfortunately, Excel 2013 and earlier versions do not have a built-in feature to create a Pareto chart, but there is a workaround. Remember, a Pareto chart, in essence, is a combo chart with sorted values. Let’s take another look at our example from before.  

We begin by sorting the data.

• Click a cell in the values column and click the Sort Largest to Smallest icon under DATA > Sort & Filter.
• If your data is defined as a Table, click the down triangle ▼ symbol and click Sort Largest to Smallest.

Next step is calculating the cumulative total percentages. To do this, we need to divide the running totals by the actual total. In our example, these values are placed in range C3:C22. Select the cell D3 and enter the following formula:

=SUM($C$3:C3)/SUM($C$3:$C$22)

Copy down the formula for the rest of the values. The last value should be equal to 1 - representing 100%. Format the cumulative total values as percentages and give the new column a name for a better view.

Now, we can create the chart. Begin by selecting your data. Unlike Excel 2013, Excel 2010 and previous versions don't have a definition for a combo chart (but they are supported). Since these versions don’t have the Combo Chart feature, you need to create a bar or line chart first. The next steps will be selecting one of the series and turning it into the other type, and selecting the Secondary Axis option.

Go to INSERT > Charts > Combo > Clustered Column – Line path to create a combo chart. Excel automatically sets the first selection as columns, and the second as line.

At this point, both series are using the same axis, whereas the cumulative total percentage values are supposed to be displayed on another axis. Right-click on the chart area and click the Change Chart Type option.

Check the Secondary Axis option for the cumulative total values and click OK to apply your settings.

Our Excel Pareto chart is ready! You can apply a few more design tweaks to finalize. Below are a few ideas.

• Remove title and legend.
• Set cumulative axis' Maximum value to 1.

• Set Gap Width to remove gaps between columns.

You’re all set! Below is the final chart using our sample data. Excel Pareto charts can be very useful in identifying how certain elements change over time and also give you the ability to compare these values against another metric.