A Pareto chart, alternatively known as a Pareto distribution diagram, is a type of vertical bar chart where the values are plotted down in descending order of their relative frequency.
These values are plotted from the left to the right and they are great for analysing problems which exist and need attention before others. The smaller bars towards the right of the chart illustrate which of the values have a lower impact or application to a system.
It is the Pareto chart which provides an illustration of the popular Pareto principle, the principle which says that maintaining 80% output is produced by 20% of the input. In quality control, Pareto’s principle (or “law”) is one of seven core principles.
On a Pareto chart, there is also a line which represents the cumulative total percentage. It is very good for identifying the largest contributory factors in a given set of data. When using a Pareto chart with valuable data sets, it becomes simple to see which ones are having the greatest impact.
What Can Pareto Charts Be Used For?
Just because they sound very simple does not mean their applications aren’t far-reaching. In fact, plotting data on a Pareto chart can be useful for a variety of situations, a good example being the analysis of customer complaints.
As a business, if you were to look at all your customer complaints and categorise them depending on what was complained about, you could then plot this information on a Pareto chart to see which areas needed your attention first.
Putting “number of complaints” on the X axis and then putting the nature of these complaints across the Y axis, such as “Rude sales representative” or “Website-related issues” you can see where your biggest problems lay.
Making a Pareto Chart in Excel
A Pareto chart in Excel is very easy to make. First of all, you need to select your data. To make a Pareto Chart in Microsoft Excel you have two columns, one for the data category – i.e. Rude Staff – and then another column for the instances within that category.
| Category A | 20000 |
| Category B | 10000 |
| Category C | 5000 |
| Category D | 1000 |
Standard Deviation in Microsoft Excel
When you have inserted all your datasets and the values which correspond to them, you should then highlight both columns by clicking and dragging and then heading to Insert > Insert Statistic Chart > Histogram > Pareto.
You can also use the All Charts tab which is found under Recommended Charts to create one of the Pareto charts by navigating to Insert > Recommended Charts > All Charts.
You will then be met with a Pareto chart which can be changed and manipulated to your liking. Don’t like how it looks? You can format it and change the design by going to the Design and Format tabs to play around with how it looks. If you cannot see the Design and Format tab, then just click anywhere on the Pareto chart and then press the properties icon.
Your Pareto chart may look something like this –
From this Pareto chart, it can be deduced that 80% of the complaints come from 20% (2/5) of the types of complaints as demonstrated by the orange Pareto line – too expensive and slow shipping. It is therefore clear that the Pareto principle applies to this situation and the company has a clear, illustrated and irrefutable source for their problems and they can now take action as a result.
How Excel Makes Pareto Charts
In Microsoft Excel 2016 and newer versions, it is a formula called Scott’s normal reference rule which is used to create Pareto charts. This formula minimises variation bias in the Pareto chart when compared with the data set and assumes that this data has been normally distributed.
