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.
- 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.
- 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.
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.
- Arrange data in descending order
- Calculate the percentile of a product on the whole amount
- 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.
- 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.
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.
- 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
Here, D5 signifies the quantity for the first product.
- Press ENTER to have the output.
- In the second cell of the Cumulative column, input the following formula:
E5 = First product quantity
D6 = Second product quantity
- Next, hit ENTER to have the result.
- After that, use the following formula to find the total quantity in Kg:
Here, the SUM function is applied in cells from D5 to D10.
- Press ENTER to have the total quantity.
- Afterward, apply the following formula in the first cell of the Percentage column to have the percentile value.
Here, the cumulative value is divided by the total amount.
- Now, press ENTER to have the result.
- 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.
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.
- 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.
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.
For more expertise, you can practice here.
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.