How To Create Dynamic Pareto Chart in Excel
In this article, you will learn how to create dynamic Pareto chart.
A Pareto chart, named after Vilfredo Pareto, 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.
Let us understand with an example:
- We have School Complaint data & we need to create Pareto Chart.
- We need helper column Cumulative % in column C; formula in cell C2 would be =SUM($B$2:B2)/SUM($B$2:$B$11) & drag the formula down
To create dynamic Pareto; chart we need three cells in which we will do some calculations
- First of all we will create Scroll Bar & linked it to cell B16
- Right click on Scroll Bar & select Format Control & enter the values as shown in below snapshot
- In cell B14 the formula is =B16/100 to calculate Target
- In cell B15; we have the following formula for Cumulative %
- Now we need to create 2 more helper columns i.e. Highlighted % & Remaining %
- In cell D2 the formula is =IF($B$15>=C2,B2,NA())
- In cell E2 the formula is =IF($B$15<C2,B2,NA())
- Finally everything is set to create the Pareto Chart; we need to select range A1:A11 & C1:E11
- From Design tab click on Chart Type
- Apply Line chart type to Cumulative % & click on Secondary Axis
- We need to make the Cumulative % to 100 % as its showing 120 %
- Right click on Secondary axis & select Format Axis
- Select Maximum value as 1 instead of 1.2
- Now we are all set to view Dynamic Pareto Chart; the only problem is as you click on Scrollbar you will find that the Bar will shift from its original position because there are two series
- To fox this issue we will click on Broken Benches (Highlighted %) Bar & right click on it select Format Data Series
In Series Overlap enter 100% refer below snapshot
- To see what Target % is selected; in cell J24 enter the formula as
- =”Target “&TEXT(B14,”0%”)
In this way we can make Dynamic Pareto Chart wherein as you increase or decrease the scrollbar value it will update the Target.