How to Create a Pareto Chart in Excel - Complete Guide

Who I am
Marie-Ange Demory
@marie-angedemory
Author and references

Likewise, the Pareto chart can be easily applied to all study areas and that is why it will be very interesting for you to get to know it thoroughly. If you really want to learn a to represent i give statisticians like a pro in Excel, so that you can apply them to any topic, keep reading this article.

How do you create a Pareto chart in Excel?

It is important to know that a Pareto is a graph that represents several problems of an organization through bars and a line. Where, the bars represent how often these issues occur and the line would represent the percentage of each issue faced by the organization.



We will represent this diagram by means of a simple graphic, which will be very useful for prioritizing the most common failures of an organization. Next, you will know the steps to embed relevant information in an Excel chart with the Pareto principle.

Organize your data

Once inside Excel, you will enter the information you have about the problems that haunt the organization regardless of the order. In the first column you will place the problems, in the second column the frequency and in the third column the accumulated percentage. Even if you don't know how to add it you can learn how to put or create columns in Word

Next, you will select all of the above issues, to arrange them in order from highest to lowest frequency. Then, you will go to the bar of applications of Excel and click on «Sort & Filter» and you will select «Frequency».

Also, you will open the «Sort criteria» tab and choose from «Highest to lowest», this way your data will be sorted.


Calculate the cumulative percentage

per ottenere in Excel  la percentage equivalent to each problem, you must first add all the frequency data. You can do this by going to an empty column parallel to the previous ones and you will place "= cell number of the frequency of the first problem + the number of the previous cell"


Next, you will have the sum result in that cell, select it and auto fill all subsequent cells. This way you will get the total sum of the frequency and the particular sum of each problem the organization faces.

Then, you select the first cell in the column of cumulative percentage and divide the number of the frequency of each problem by the accumulated total. You will do this by inserting "= cell number of the sum of the frequency of the problem between the total of the sum of the frequency of the problem"

Next, auto-fill all lower cells and Excel will extract the values ​​of each cell from the accumulated percentage. Finally, select the percentage button in the Excel task bar and it will give you the percentage value of each problem.

Drawing up the diagram

Once you have all this data, go to the taskbar, select "Insert", then choose "Recommended graphics" and then "Grouped column". Then, hit the Accept button and the diagram will appear in Excel to place the data.


To place the numeric axis values, you will select it and right-click. Then a window will open and you will select "Format the axis", then another window will appear where you can place the frequency values.

The next item to be changed in the diagram will be the percentage values ​​and we perform the same steps above to change their values. Once inside the axis format, you will place the "0" value in the "minimum" row and the "1" value in the maximum row, which is equivalent to 100%.


This way your Pareto chart will be ready, where all the problems in your organization will be represented in percentage and their frequency. If you have managed to create a Pareto chart in Excel using this comprehensive guide, follow this wonderful post.

TagsEccellere report this ad
add a comment of How to Create a Pareto Chart in Excel - Complete Guide
Comment sent successfully! We will review it in the next few hours.