Pareto Chart
A Pareto chart is a type of chart that contains both bars and a line graph, where individual values are represented in descending order by bars, and the cumulative total is represented by the line. In this article we will use the Pareto chart to measure the industry’s monthly expense. So that we can identify what are the 20 % factors contributes major (80%) of the expenses.
Data source
Step 1: Data sorting, Sort the data in descending (Largest to Smallest) order so it will looks like as below:
Step 2: Create a cumulative amount column. To start with cumulative amount cell C2=B2 then C2 + B3, C3 + B4 and so on as shown below:
Step 3: Calculate Cumulative Percentage in next Column as shown below:
Step 4: Insert a Clustered Column chart:
- Select data
- Select Insert tab
- Click on Recommended Charts button under Charts group
- Select Clustered Column Chart and press OK as shown below
Result
Step 5: Change the series by following below steps:
- Right click on any of the bars
- Select Change Series Chart Type… (figure 1.0)
- Under Cumulative (%) select Line with Markers (figure 1.1)
- Click on Secondary Axis tick box (figure 1.2)
- Say OK
Result
Step 6: Right click on Cumulative Amt series (red color) and delete it so the resulting chart would look like below:
Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.
