Alok
The Pareto Chart is the combination of the bar and line chart. It indicates the frequency of the cumulative impact. In this article, we will show how to use the Pareto Chart in Excel in different versions.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
What Is Pareto Chart?
Pareto Chart is a special type of chart that contains both bar and line charts. The bars are sorted in descending order and the line represents the cumulative total in percentage form. The main significance of the Pareto chart is to show that 80% of earnings come from the 20% sectors.
2 Ways to Use Pareto Chart in Excel
We can draw the Pareto chart in Excel in 3 different ways. These 2 ways are due to the different versions of Excel. In the older versions, the latest chart options are not available.
1. Use Pareto Chart in Excel 2016 with Built-In Feature
In this section, we will draw a Pareto chart that is allowed in Excel 2016 to later versions. In this version, a built-in option is available for Pareto.
📌 Steps:
- We will draw a Pareto chart based on the below data set. The data set is the breakdown of salary.
- Now, select all cells of both columns.
- Then, go to the Insert tab.
- Click on the arrow at the bottom of the Chart group.
- Now, the Insert Chart window appears.
- Click on the Histogram from the list.
- Now, choose the Pareto chart option.
- Have a look at the dataset now.
The Pareto chart is shown here.
- Now, double-click on any of the bars of the chart. All bars are in editable mode now.
- Hit the Plus sign from the right side.
- Chart Elements section appears.
- Check the Data Labels option from there.
- Again, look at the chart.
The bars are showing the values. Also, one thing that needs to mention is that bars are showing in the chart in descending order automatically.
2. Use Pareto Chart in Excel 2013
In Excel 2013, there is no built-in option to draw the Pareto chart. Here, we use the Combo chart. In the Combo chart, we can manually, combine the Line and the Bar chart to form a Pareto chart.
📌 Steps:
- In this section, we need another value which is the cumulative percentage. Add a new column for that in the dataset.
- First, we need to sort the data in descending order.
- Select Range C5:C9.
- Press the right button of the mouse.
- Click the Sort option from the Context Menu.
- Choose Sort Largest to Smallest option.
- A warning shows. We check the Expand the selection option from the window.
- After that, click on Sort.
- We can see the dataset is sorted in the desired order.
- Now, put a formula on Cell D5 based on the SUM function.
=SUM($C$5:C5)/SUM($C$5:$C$9)
- Now, press the Enter button and drag the Fill Handle icon.
- Now, select all the data cells.
- Then go to the Insert tab.
- Click on the arrow button at the bottom of the Chart group.
- Choose the Combo option from the Insert Chart.
- Choose the Custom Combination option.
- Mark the corresponding axis of the Line chart as the secondary axis.
- The chart is showing now.
- Double click on the secondary axis at the right of the graph.
- The Format Axis option will show now.
- Putting the maximum value as 1 means 100%.
- Again, look at the chart.
Pareto chart is showing concerning percentage.
Conclusion
In this article, we described how to use the Pareto chart in Excel. We showed two methods for different versions of Excell. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
