How to Create a Stacked Pareto Chart in Excel (2 Smart Ways)

In order to summarize values with bars and lines in descending order, it is best to use a Pareto Chart. It can visualize the dataset with elegance and simplicity. In this article, I am going to explain 2 smart ways to create a stacked Pareto Chart in Excel. I hope it will be helpful for all Excel users.


Download Practice Workbook


What Is a Pareto Chart?

A Pareto chart is a form of a graph with both bars and a line graph, where the bars reflect individual values in descending order and the line the cumulative total. This chart is widely used for some project management tasks and it follows the 80/20 rule.


2 Smart Ways to Create a Stacked Pareto Chart in Excel

1. Using Histogram to Create a Stacked Pareto Chart

A histogram is the simplest way to create a stacked Pareto chart. For this, you need to follow the following procedures.

Steps:

  • Decorate your dataset with the related data to create a stacked Pareto chart. Here, I have organized my data of product sales in Serial Number, Product Name, and Quantity(Kg) columns.

How to Create a Stacked Pareto Chart in Excel

  • Next, select the data with that you want to create the stacked Pareto chart.
  • Then, go to the Insert tab.
  • Click on the Insert Statistic Chart option.
  • From the Histogram option, choose Pareto to create a stacked Pareto chart.

Thus, we can create a stacked Pareto chart in the simplest possible way.

How to Create a Stacked Pareto Chart in Excel


2. Using Recommended Charts to Create a Stacked Pareto Chart

We can also use the Recommended Charts command to create a stacked Pareto chart. It is not as simple as the previous one. It is a detailed and manual process to create the chart. This process can be explained in three simple steps.

  1. Arrange data in descending order
  2. Calculate the percentile of a product on the whole amount
  3. Creation of a stacked Pareto chart

Step-01: Arrange Data in Descending Order

The first thing we need to do is to arrange our data in descending order. Maintain the following steps to do so.

Steps:

  • Firstly, make a dataset with the related data to create a stacked Pareto chart. I have decorated my data of product sales in Serial Number, Product Name, and Quantity (Kg).

  • Next, select the data based on which you want to descend your table. Here, I want to descend my table based on the quantity amount of a product. So, I chose cells D4:D10.
  • Followingly, go to the Home tab.
  • Click on Sort & Filter from the ribbon.
  • Then, pick Sort Largest to Smallest option.

How to Create a Stacked Pareto Chart in Excel

Then, we will have the arranged dataset in descending order.


Step-02: Calculate Product Percentile on Whole Amount

After organizing the dataset, the first thing we need to do is find out the product percentile on the whole amount.

Steps:

  • Add two columns to find out the cumulative value and the product percentile value. I have created columns Cumulative and Percentage for this purpose. I have used an additional row to find the Total Quantity (Kg).

  • Followingly, input the formula mentioned below to find the cumulative value for the first cell in Cumulative
=D5

Here, D5 signifies the quantity for the first product.

How to Create a Stacked Pareto Chart in Excel

  • Press ENTER to have the output.

  • In the second cell of the Cumulative column, input the following formula:
=E5+D6

Here,
E5 = First product quantity
D6 = Second product quantity

How to Create a Stacked Pareto Chart in Excel

  • Next, hit ENTER to have the result.

How to Create a Stacked Pareto Chart in Excel

  • After that, use the following formula to find the total quantity in Kg:
=SUM(D5:D10)

Here, the SUM function is applied in cells from D5 to D10.

  • Press ENTER to have the total quantity.

How to Create a Stacked Pareto Chart in Excel

  • Afterward, apply the following formula in the first cell of the Percentage column to have the percentile value.
=E5/$D$12

Here, the cumulative value is divided by the total amount.

  • Now, press ENTER to have the result.

How to Create a Stacked Pareto Chart in Excel

  • AutoFill the rests.

  • Now, select the values in the Percentage column.
  • Go to the Home tab.
  • Then, click on the percentage sign from the Number group in the ribbon.

How to Create a Stacked Pareto Chart in Excel

Now, we have the product percentile on the whole amount.


Step-03: Creation of a Stacked Pareto Chart

It is the final part of the Pareto chart creation.

Steps:

  • Select the data with what you want to create the stacked Pareto chart. Here, I have selected the values in columns Product Name, Quantity (Kg), and Percentage.
  • Then, go to the Insert tab.
  • Click on Recommended Charts from the ribbon.

How to Create a Stacked Pareto Chart in Excel

An Insert Chart wizard will appear.

  • Go to Recommended Charts tab.
  • Click on Clustered Column option.
  • Press OK to finish the process.

This is how we can create a stacked Pareto chart.

How to Create a Stacked Pareto Chart in Excel


Practice Section

For more expertise, you can practice here.


Conclusion

That’s all for the article. In this article, I have tried to explain 2 smart ways to create a stacked Pareto chart in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our Exceldemy site for more details on Excel.

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo