How to Construct a Pareto Diagram in Excel

Pareto diagram or Pareto analysis is a visual representation of the powerful 80-20 rule, also called the Pareto Principle. A combination of bar and a line graph, this diagram has bars plotted from highest frequency to lowest in the right to left direction. The curved line is present to demonstrate the cumulative percentages. The values are represented on the bars from largest to smallest, and from left to right. It implies that the categories depicted by the bigger bars on the left are of more importance than those on the right.

The Pareto Principle, given by the Italian economist Vilfredo Pareto, suggests that 80% of the effects arise due to 20% of the causes. Therefore, a Pareto diagram helps to graphically depict where you should focus on maximum benefit and what you should ignore.

How to make your own Pareto Diagram in excel?

If you are wondering how to create a Pareto diagram, here is a step-by-step guide to assist you through the process on all versions of MS Excel.

1. Collect data and categorize the issues

Collect all the data about each defect or problem very carefully. Acknowledge them and create categories to group the items. The different units can include Frequency, Time, Cost, etc.

2. Tally the whole data

When gathering large amounts of data, some errors can occur. So, start accumulating the data that already exists or record the data in table form and tally for each item, sum up all the stats, and calculate the total time taken by it. This will help you calculate the percentage and create a cumulative result easily.

3. Creating a Pareto diagram

Select one column, each containing text, and frequencies (numbers). If you select two columns of numbers and one of text, Excel will chart your data in bins, like Histogram, which you can adjust later.

4. Select preferred bar graph

Now, click on the Insert tab, and from the Charts group, select Statistic Chart. Open Histogram and pick Pareto. You can also use any recommended chart type by going to the All Charts tab and then Recommended Charts to create a Pareto chart.

5. Change the series chart type

The above step will create a clustered graph. Now, right-click on any bar and click on Change series chart type. A dialog box will appear.

6. Final Step

On the bar graph, plot the causes on the X-axis and percent frequency on the Y-axis, while on the line curve, plot the causes on X-axis and cumulative percentage on the Y-axis. Now, at 80% from the y-axis, draw a horizontal dotted line intersecting the curve. Further, draw a vertical dotted line to the X-axis from the above point of intersection. This vertical dotted line will differentiate the important causes on the left and trivial causes on the right.

Now, click on OK, and the clustered bar graph with a curvy line will appear on your screen. This is the final Pareto diagram, portraying the 80-20 rule.

7. Percentage

Now, right-click on the percentages depicted on your Pareto chart and click on the Format Axis option. Set the Maximum Bounds to 100.

Importance of a Pareto Diagram

A Pareto chart is a powerful tool that helps you easily acknowledge the defects, complaints, or other factors that significantly influence your yield any other factors that influence your yield significantly.

• Quality improvement

Businesses can hugely benefit from the Pareto diagram to improve their services’ quality. The team leaders can figure out what business defects are responsible for the most losses or complaints and present the data in a simplified visual form via Pareto charts.

• Better Communication of Data

Pareto chart is a very effective communication tool, especially among different departments. It enables better communication of 80-20 rule via visual representation. It can help in resolving conflicts among different team members. Through a Pareto diagram, your employees can better understand the 20% of company issues that are leading to 80% of the damages.

• Improved Prioritization

Not only in the management sector but even in your daily lives, a Pareto chart can help you set your priorities, thereby improving your productivity and performance. The chart representing the 80-20 rule will help you recognize the relative importance of various categories, which businesses can use to prioritize their customer needs or better investment. With defects ranked in order of their severity, businesses can get a clear insight into which defects or issues must be eliminated first and which in the end.

While, in daily life, you can even use the chart to prioritize your choices for lunch to stay healthy. You can plan, analyze, prioritize and resolve problems or defects with the help of the Pareto diagram. Therefore, the Pareto chart helps to clearly distinguish between the vital few from the trivial many, which is the Pareto Principle or the 80-20 rule.

• Enhances Skills

You can also use the Pareto diagram to enhance your skills, such as problem-solving, decision-making, etc. When you prepare a Pareto chart of your skills, you will get to know which of your skills are benefitting you the most in different areas of life. Whereas, you will also get to know which skills or habits should be eradicated to improve the quality of your life.

Other additional advantages include-

• Creating your Pareto diagram is quite simple.
• It helps you to easily segregate the problems and their causes.
• It makes you focus on the most impactful problems to get the most significant results.
• It enables you to pay attention to a few causes which are leading to the maximum problems.

Conclusion

Therefore, a Pareto diagram is a data-based, result-oriented visual tool to determine which problems need to be focussed on and is applicable in various fields. It is easy to create your Pareto chart in all MS Excel versions by following the above-given steps. It helps to easily analyze various situations in the same organization using varied factors to improve quality and productivity.

Such Pareto charts can help a team reach a unanimous decision regarding where the company’s valuable time and resources should be spent, thereby preventing conflicts.

Pearl is a freelance content writer and lifelong learner with an ongoing curiosity to learn new things. She uses that curiosity with her experience as a content writer.