How to Make a Pareto Chart in Excel
You will learn how to create a Pareto Chart in Excel. This is an 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.
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.
How to insert the Pareto Chart?
Then, create a chart. This should be a column 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’.
Now, the Pareto diagram looks a little bit better.
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.
You can even add data labels. Right-click the data series and select “Add data labels”.
Add a title, format the axis as needed, and adjust label placement at the bottom.
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.
Leave a Reply