How to Make Pareto Chart in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

How 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\$13`

Here, 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.

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.

Related Articles

<< Go Back to Excel Pareto ChartÂ |Â Excel ChartsÂ |Â Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF