Abdul Alim: a little bit learning, let's go
Excel, VBA and Power BI tutorials
Abdul Alim: a little bit learning, let's go
Excel, VBA and Power BI tutorials
In this dynamic chart you will learn how to hide a series by unchecked a checkbox.
Below is the data point for which we will create this chart.
Below are the steps to create this dynamic chart:
Copy “A1:A11” and paste on “G1”
Copy header “B1:D1” and paste on “H1”
Go to Chart Sheet.
Go to the Developer tab > Insert >Form Controls >Check Box
Insert 3 Check boxes and rename them as Call Offered, Call Answered, Abandoned
Right click on check boxes and click on “Format Control…”
Put in cell link of Call offered Checkbox “Data!$F$1”
Put in cell link of Call Answered Checkbox “Data!$F$2”
Put in cell link of Abandoned Checkbox “Data!$F$3”
Now go to Data sheet and put formula on
Range H2 “=IF($F$1,B2,NA())”
Range I2 “=IF($F$2,C2,NA())”
Range J2 “=IF($F$3,D2,NA())”
Fill down the formula on “H2:J11”
Change the number format of column J as percentage.
Select Range “G1:J11”
Go to Insert>>Charts>>2D Column>>Clustered Column
Now right click on any column and click on “Change Chart Type”
In Change Chart Type window change the chart type for Abandoned as “Line with Markers”
Also check the Secondary Axis box
We will change the Chart Style now
Select the chart and go to “Design Tab>>Chart Style>>Style6”
Change the chart title as “Date wise Performance”
Cut (Ctrl+X) the chart and paste on Chart Sheet.
Insert a rectangle in Chart Sheet from Insert>>Shapes>>Rectangles>>Rectangle
Match the Rectangle width with chart width and send to back of check boxes.
Our dynamic Chart is ready. Please download this excel file for practice.