Mrinmoy
When you want to analyze or compare two or more things, a Pareto chart can be a helpful tool. A Pareto chart can be helpful in many situations where a few items or factors make up a large proportion of something. In business, a Pareto chart is often used to compare different items in terms of profitability or cost. In this article, I will show you how to make a Pareto chart in Excel with some easy steps. So, without having any further discussion, let’s dive straight into it.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
What Is a Pareto Chart?
A Pareto chart is a graphic tool for showing the relative probability of occurrence of various values. Values are ordered from greatest to smallest and presented as a Histogram in a Pareto chart. The Pareto principle commonly referred to as the 80/20 rule, is a widely accepted idea in business that claims that 20% of causes account for 80% of effects. A visualization tool that can be used to illustrate this idea is the Pareto chart.
The Pareto chart is named for the Italian economist Vilfredo Pareto, who noted that 20% of the population in Italy controlled 80% of the country’s land. He also discovered that just 20% of the pea plants in his garden produced the majority of the peas, which were 80%. According to Pareto, this phenomenon was not exclusive to Italy or peas but rather was a generic rule that could be used in a variety of circumstances.
Steps to Make a Pareto Chart in Excel
I will use the following Sales Report to show you how to make a Pareto chart in Excel.
In the dataset, the Product column consists of a list of product names. The Sales column consists of the corresponding sales amount for each product.
📓 Note: Here, the products are sorted by the largest to smallest sales amount. It is a must to sort the data in descending order.
Step-1: Preparing Dataset to Make a Pareto Chart
To make a Pareto chart, you need to prepare your dataset first.
- First, calculate the sum of all the sales amount using the following formula in cell C13.
=SUM(C5:C12)
Now you need to calculate the cumulative sum of all the sales amounts in the column, Cumulative.
To calculate the cumulative sum of the sales amount,
- At first, copy the amount of the first sale in cell D5 using the following formula:
=C5
- Now insert the following formula in cell D6 to calculate the first cumulative sum.
=C6+D5- After that, press the ENTER button.
- Drag the Fill Handle button from cell D6 to D12.
- Now you will get the Cumulative Sum of all the sales amount in the column, Cumulative.
Now, you have to calculate the percentage of the cumulative sum amount.
To calculate the percentage of the cumulative sum,
- Insert the following formula in cell E5.
=D5/$C$13Here, D5 is the first cumulative sales amount. And $C$13 is the summation of all sales amounts. Here I used an absolute cell reference to lock cell C13.
- Now press ENTER.
- After that, drag the Fill Handle from cell E5 to E12.
After that, you will get the fraction of the numbers.
To convert all the fraction numbers into percentages,
- Select the range E5:E13.
- Go to the Home tab.
- From the Number group, select Percentage.
Now all the cumulative sum of the sales amount is in percentage.
Read More: How to Make a Pareto Chart Using Pivot Tables in Excel
Step-2: Inserting Pareto Chart
After preparing the data, the next step will be inserting a chart using the data. To insert a Pareto chart,
- Highlight the Product, Sales, and Percentage columns.
- Go to the Insert tab.
- From the Charts group, select the Recommended Charts command.
Now Insert Chart dialog box appears.
- Select the first recommended chart.
- Then click OK.
After that, you will see the following Pareto chart.
Step-3: Adding Data Labels
To add data labels on the Pareto chart,
- Click on the orange percentage line to select it.
- Then go to Chart Design ➤ Add Chart Element ➤ Data Labels ➤ Below.
This will add the percentages of the cumulative sum of the sales amount below the orange line.
Significance of Creating a Pareto Chart
From the above Pareto chart, we can see that the sales of Coconut, Matcha, and Strawberry contribute about 80% of the whole sales. This data representation supports the 80/20 rule completely. Now you can easily understand which products are more important for profitability and which aren’t.
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.
Conclusion
To sum up, I have discussed steps to make a Pareto chart in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
