Excel Pareto Analysis ( Table of Contents)
Introduction to Pareto Analysis in Excel
Pareto Analysis has a base of Pareto principle, which says 80% of the effect for a particular event (or many events in that case) has its roots in 20% of the causes/reasons. It is most of the time remembered as an 80/20 pattern/principle in laymen terms. This principle was first developed by an Italian economist named Vilfredo Pareto, and therefore it has been named as Pareto Principle based on his name, and at the same time, the analysis being done is considered/named as Pareto Analysis. Some of the real-life examples of Pareto can be formulated as below:
- 80% of the shares of one particular company are owned by 20% of the stakeholders.
- 80% of the wealth is acquired by 20% of the people in this world.
- 80% of the software issues are caused due to 20% of the bugs.
How to Create a Pareto Chart in Excel?
Suppose we have data as shown in the screenshot below. This data is associated with a hotel and the complaints they receive from their clients. As of now, they have several categories under which the complaints are raised, with the frequency of complaints as a parameter. Ex. If one particular category has got complain about once, the frequency will be one. Every time a complaint gets raised under a category, the frequency count gets raised by one unit. Therefore, when someone says the frequency of complaint under a category is 40, it means 40 times a complaint has raised under that category. See the screenshot below for your reference.
Step 1: Under column C, capture the cumulative percentage. The cumulative percentage can be captured using the formula as shown below:
Well, this formula seems somewhat weird for the naked eyes. However, believe me, this is the best suitable formula for capturing the running totals or cumulative sums.
Step 2: Drag this formula Across the cells C3:C8 in order to get the running total of the frequencies in column B. You can see it as shown below:
Every time, the system captures the sum of frequencies starting from cell B2 and up-to-the corresponding cell. For Ex. in cell C4, the sum value starts from B2 to B4 and so on.
Step 3: In column D, find out the cumulative percentage with the help of the formula =C2/SUM($B$2:$B$8).
Step 4: Drag this formula down across the cells D3:D8 so that we can get the cumulative percentage of frequency to proceed with our Pareto chart. This can also be achieved using a keyboard shortcut Ctrl + D.
Step 5: Select the cells D2:D8 and navigate to the Number Formatting group under the Home tab, where you can see the Percentage Style button. Click on that button to change the style of cells as a percentage. Or else, you can press Ctrl + Shift + % button through your keyboard as a shortcut to achieve the result.
You should see the cells under column D are formatted as percentage values.
Step 6: Select column A, B and column D in your excel data and navigate to the Insert tab through the Excel ribbon.
Step 7: Now, under the Charts group, click on the Recommended Charts option. And you will see all the charts which can be used for representing this data visually.
As soon as you click on Recommended Charts option under the Charts section, a new window named Insert Charts will open up as shown below:
Step 8: In the Insert Chart window, click on the All Charts tab. Where you can see a list of charts available to insert under Excel.
Step 9: Move towards the Combo option at the left-hand side and select the Custom Combination under it to customize the chart.
Step 10: Now, under Custom Combination, select and tick the Secondary Axis option for the Cumulative % series. It means that the Cumulative % values will be plotted on the Secondary Axis. Click on the OK button once done. See the screenshot below:
The final chart should look at the one below:
We would like to modify this chart to look like a Pareto chart. Follow the steps below for the same.
Step 11: Right-click on the Secondary Axis values on the graph and choose Format Axis… option. A new Format Axis pane will open up at the rightmost side of the Excel sheet. There, under Axis Options, change the Maximum value for Bounds to 1.0 it is automatically set for 1.2, which means 120%.
If you see any Pareto Chart, you’ll come up with an observation that the gap between bars is really very less. Bars are close to each other. We will try to reduce the gap between the bars of our Pareto chart.
Step 12: Right-click on any one of the bar and choose Format Data Series… option placed at the end of the list of options.
Step 13: You can see on the right-hand side; the Format Data Series window will open up in Excel. Under Series Options, You will have the Gap Width option, which can be managed custom. Change the Gap Width to say 3% so that the bars get close to each other.
It actually looks like a Pareto Chart.
Here I have changed the color of the Cumulative % line series. Also, I have added a chart title for this chart.
Based on our Pareto Chart, we can say that Almost 90% of the complaints are raised for Delay in Room Service and Delay in Room Allocation. Therefore, these are the major areas we should keep improving for better customer feedback and reviews.
This is it from this article. Let’s wrap the things up with some points to be remembered:
Things to Remember About Pareto Analysis in Excel
- In laymen terms, Pareto Analysis is also called as 80/20 principle.
- It is always good to capture the cumulative percentage of the frequencies or data value and sort the data values in descending order.
- Cumulative values should not be a part of the chart. Only Frequency values and Cumulative Percentage should be a part of the chart.
Recommended Articles
This is a guide to Pareto Analysis in Excel. Here we discuss How to use Pareto Analysis in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
13 Online Courses | 100+ Hours | Verifiable Certificates | Lifetime Validity
4.5
View Course
Related Courses
