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.

 
img1

 

  • 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

 
img2

 

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

 
img3

 

  • In cell B14 the formula is =B16/100 to calculate Target
  • In cell B15; we have the following formula for Cumulative %
  • =INDEX($C$2:$C$11,IFERROR(MATCH($B$14,$C$2:$C$11,1),0)+1)

 
img4

 

  • 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())

 
img5

 

  • In cell E2 the formula is =IF($B$15<C2,B2,NA())

 
img6

 

  • Finally everything is set to create the Pareto Chart; we need to select range A1:A11 & C1:E11

 
img7

 

  • From Design tab click on Chart Type
  • Apply Line chart type to Cumulative % & click on Secondary Axis

 

img8

 

 

img9

 

  • 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

 

img10

 

  • 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

 
img11

 

  • To see what Target % is selected; in cell J24 enter the formula as
  • =”Target “&TEXT(B14,”0%”)

 

img12

 

In this way we can make Dynamic Pareto Chart wherein as you increase or decrease the scrollbar value it will update the Target.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube