How to Make a Pareto Chart in Excel

You will learn how to create a Pareto Chart in Excel. This is an example:

Excel Pareto Diagram ready chart example

The Pareto diagram consists of both a column chart and a line chart. It presents a graphic incidence of certain factors (e.g., causing complaints or defects). It is used mainly for quality control, to show what the focus is to get the best results (e.g., the elimination of one defect may reduce the number of complaints by half).

Pareto Principle

The Pareto Diagram was the invention of the Italian economist Vilfredo Pareto. During his research, he concluded that 80% of the country’s wealth is held by only 20% of the population. In this way, the so-called Pareto Principle, which is based on the proportion of 20:80, is isolated. According to the Pareto Principle:

  • 20% of clients, including 80% of complaints,
  • 20% of the effort results in 80% of the effects,
  • 20% of customers generate 80% of the company’s revenue,
  • 80% of the complaints are caused by 20% of the causes.

There are a lot of examples of the Pareto Principle. With its simplicity, the Pareto Principle is used, among others, in quality management.

Excel pareto chart table

First, you will need a data table. Focus on quality management. A simple example of a Pareto Diagram is a table where we can find the most common car damage. In this example, they are: light bulbs burned out, dead battery, blown fuse, worn brake pads, flat tire, and others. The table should include both the count and the cumulated count (preferably in percentage terms) of individual data points.

See also  How to Explode a Pie Chart in Excel

How to insert the Pareto Chart?

Then, create a chart. This should be a column chart.

Excel pareto chart

The chart is unreadable because the data values are very different. Add a second series of data to increase the transparency of the chart. Click on one of the red columns as a percentage of the cumulative amount of (tick all) and select ‘Format data series’ (keyboard shortcut CTRL + 1). A dialog box appears. Select the ‘Plot series on’ and ‘Secondary axis’.

Excel pareto chart format data series secondary axis

Now, the Pareto diagram looks a little bit better.

chart with secondary axis

Red columns are still selected. Do not uncheck them, because they change their type. Change the chart type of a column on a line chart. Simply right-click on any of the red columns and select Change Series Chart Type. Now select the line chart and confirm.

Excel pareto chart charty series type changed line chart and column chart

You can even add data labels. Right-click the data series and select “Add data labels”.

Excel pareto chart data labels

Add a title, format the axis as needed, and adjust label placement at the bottom.

Excel Pareto Diagram ready chart example

Now, your Pareto chart is ready. The columns represent the frequency of different types of damage, while the line chart shows cumulative percentages. You can quickly identify that the top 3 damage types account for a significant portion of the total issues.

Download free spreadsheet with Pareto Chart