Do you know how to create a Pareto chart in Excel?

If you need to decide which issues you're going to deal with most urgently in the company you work for, the Pareto Diagram is the ideal tool for that! With it, you will be able to have more control over the nonconformities and thus increase the quality of the process.

The Pareto chart allows for easy visualization and identification of the causes of a problem. From an analysis of the frequency of occurrences, it is possible to distinguish which the company's priority actions should be and where it should direct its resources to solve a certain type of problem.

In this article you’ll see:

• What is a Pareto Chart?
• How does the Pareto Chart work?

How to create a Pareto Chart using Excel?

What is a Pareto Chart?

The Pareto Diagram is one of the main tools when it comes to quality in an Organization. The Pareto Principle, better known as Rule 20-80, tells us that 20% of the root causes account for 80% of the problems in an organization, that is, if we solve this 20 %, we end up with most of the problems.

Are you tired of missing out on incredible career opportunities because you don't know about the main Excel tools?

So you are in the right place! In the Excel for Beginners course, you will learn to work with data in an agile way.

In addition, the course is available for FREE, just click on the banner button below and embark on this journey of knowledge!

How does the Pareto Chart work?

The Pareto chart is a vertical bar chart that arranges information in such a way as to make the prioritization of themes evident and visual (organized in descending order). Some of its features are:

• The bars on the left are relatively more important than the bars on the right.
• The information thus disposed also allows the establishment of viable numerical targets to be achieved.
• It separates the "most vital few" from the "most useful many" (Pareto Principle).

How to create a Pareto Chart using Excel?

To create a Pareto Chart with Excel we need to break the process down into a few steps. By doing this, the task will become much easier. The steps will be:

• Collecting Data
• Sorting and Organizing Data
• Identifying parameters
• Creating the Chart
• Configuring the Pareto diagram

Collecting data

To explain better, we created a very recurrent situation in our daily life: Loss of power in a car. Whenever this problem occurred, the mechanic would make a diagnosis in the car and find the cause for the problem. So, the table was created:

Sorting and Organizing Data

Therefore, every time you want to work with this tool, the first step is to collect the causes/defects and the frequencies that they have occurred in a certain period of time.

The second step is to place these frequencies in descending order. To do this, simply select any cell inside the table and click the Data > Sort.

The following panel will appear, in which you will sort in descending order the number of occurrences:

The result will be:

Identifying parameters

Now we have to identify two parameters: the percentage (%) that each occurrence obtained in the total and what is the accumulated frequency, given by the sum of the frequency of the defect with the previous one. In the case of the first data this sum does not occur. Let's see:

We use the function = SUM () to find the total number of occurrences. Therefore, we only divide the occurrence of each defect by the sum of the defects. Next, let's calculate the cumulative frequency:

Creating the graph

As explained above, simply add up the frequency of the defect and the previous one. Obligatory, the last data in the list has to appear 100.00%, since it shows that all data has been calculated.

With this data, just create the chart. To do this, we will click on an empty cell in the spreadsheet and insert a graphic of 2D Columns:

A "whiteboard" will appear, because our chart does not have data yet. So let's select it and click on the option: Select data.

When we click on this option, the table below will appear:

In the field of Subtitle Entries (Series) we will put the data present in the columns: Number of Occurrences and % Accumulated. To do this, click on Add:

In the name of the series we insert the cell with the name "number of occurrences" and in values of the series the interval referring to the number of occurrences of each defect. The result was:

We will do the same procedure for the % Accumulated series, getting the result:

Now we have to place the horizontal axis labels. To do this, we will use the Horizontal Axis Labels field (Categories). Let's select the data present in the field "defect types":

Configuring the Pareto Diagram

We can see that the % Accumulated series is on the Percentage scale and the series number of occurrences is in Number. So let's put the % Accumulated series on a secondary axis.

To do this, we will click on the bars of this series with the right mouse button and select the option: format data series > secondary axis.

Our graph will look like this:

We will change the chart type from the % Accumulated series to 2D Rows. So, just click on this series >Insert > Graphics > 2D Line > Line with Markers:

The result will be:

Conclusion

We can see that the main causes of the loss of power are the adulterated fuel and clogged nozzles. Therefore, stopping to refuel at gas stations that do not have quality seal can contribute to solving these two main problems.

If you made it this far, I have a question for you...

Several times we get lost in so many Excel functions, and we don't know where to start and what to learn. To help you, we created the First Steps eBook in Excel to guide your practice with the tool, as well as how and where to learn about its most complex resources. Learn how to solve everyday problems quickly and efficiently in Excel.

By downloading our eBook, you will learn the first steps to create your spreadsheets and use their basic and essential functions.

Let's get started?