Pareto Chart is based on the Pareto principle (also known as the 80/20 rule), which is a well-known concept in project management. According to this principle, ~80% of the problems can be attributed to about ~20% of the issues (or ~80% of your results could be a direct outcome of ~20% of your efforts, and so on..). The 80/20 percentage value may vary, but the idea is that of all the issues/efforts, there a few that result in maximum impact. This is a widely used concept in project management to prioritize work.
Creating a Pareto Chart in Excel
In this tutorial, I will show you how to make a:
Simple (Static) Pareto Chart in Excel. Dynamic (Interactive) Pareto Chart in Excel.
Creating a Pareto Chart in Excel is very easy. All the trickery is hidden in how you arrange the data in the backend. Let us take an example of a Hotel for which the complaints data could look something as shown below: NOTE: To make a Pareto chart in Excel, you need to have the data arranged in descending order. Download the Excel Pareto Chart Template
Creating a Simple (Static) Pareto Chart in Excel
Here are the steps to create a Pareto chart in Excel: How to Interpret this Pareto Chart in Excel This Pareto chart highlights the major issues that the hotel should focus on to sort the maximum number of complaints. For example, targeting the first 3 issues would automatically take care of ~80% of the complaints. For example, targeting the first 3 issues would automatically take care of ~80% of the complaints.
Creating a Dynamic (Interactive) Pareto Chart in Excel
Now that we have a static/simple Pareto chart in Excel, let’s take it a step further and make it a bit interactive. Something as shown below: In this case, a user can specify the % of complaints that need to be tackled (using the excel scroll bar), and the chart will automatically highlight the issues that should be looked into. The idea here is to have 2 different bars. The red one is highlighted when the cumulative percentage value is close to the target value. Here are the steps to make this interactive Pareto chart in Excel:
That’s It! You have created an interactive Pareto Chart in Excel. Now, when you change the target using the scroll bar, the Pareto chart would update accordingly. Download the Excel Pareto Chart Template
I would love to hear your thoughts on this technique and how you have used it. Do leave your footprints in the comments section 🙂 Related Project Management and Charting Tutorials:
Analyzing Restaurant Complaints using Pareto Chart. Creating a Gantt Chart in Excel. Creating a Milestone chart in Excel. Creating a Histogram in Excel. Excel Timesheet Calculator Template. Employee Leave Tracker Template. Calculating Weighted Average in Excel. Creating a Bell Curve in Excel. Advanced Excel Charts How to Add a Secondary Axis in Excel Charts.
=SUM($B$2:B2)/SUM($B$2:$B$10)*100 However, the dynamic one highlights one colum to much, e.g. for 80% 4 columns are highlighted, but – as you mentioned – 3 would be correct, i.e. INDEX($C$2:$C$10,MATCH($B$12,$C$2:$C$10,1)). Furthermore, you won’t need an additional column for the remaining bars if you optimize the order of the other columns.
