Abdul Alim: a little bit learning, let's go

Excel, VBA and Power BI tutorials 

Dynamic Chart With Checkboxes

A dynamic combination of Column Chart and Line Chart

Learn Dynamic Variance Arrow Chart step by step

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.