Dynamic Excel Chart with Series Selection Check-box

Apart from being super cool, Excel dynamic charts are really helpful in presenting a comparative analysis from the data.

Suppose you have the financial data for a company ABC Ltd, and you want to see how the revenue has grown over the quarters. At the same time, you also want to compare the growth with previous years. Dynamic excel chart can give you something as shown below:

A single click instantly shows you the performance of 2013 as compared to 2011 or 2012.

Dynamic Excel Chart with Series Selection Check-box

1. Arrange the data as shown below:

2. Go to Developer tab –> Insert –> Form Controls –> Check Box (Form Control)

3. Click anywhere in the worksheet to insert the check box. Change the name of the Checkbox from Check Box 1 to 2011.
4. Right click on the check box and select Format Control, specify a cell link (in this case I have used C7).
   • Cell C7 would return TRUE when the check box is checked, and FALSE when unchecked.
5. Repeat Steps 2 to 4 to insert another check box for 2012, and use a different cell link (I have used C8 here).
6. Copy the original chart data from B2:F5 and paste it in some other location. I have used B10:F13.

7. Delete the data for 2011 and 2012, and use the following formula:
   • In C11: =IF($C$7,C3,NA()) [Drag this to fill the row from D11 to F11]
   • In C12: =IF($C$8,C4,NA()) [Drag this to fill the row from D12 to F12]
   • This formula returns the value from original data if the check box for that year is checked, and #N/A if it is unchecked.
8. With both the checkboxes checked, select the entire data and go to Insert –> Charts–> Column–> Clustered Column. This will insert a column chart with three column bars for each Quarter.

9. Click on any data bar for 2011 (this will select all the data bars for 2011) and right click. From the menu select Change Series Chart Type. This will change the chart type from a bar chart to a line chart.

10. Repeat the same process for 2012 bar to convert it into a line chart. Format the chart as you wish.

11. That’s it. You have a dynamic chart ready. Now when you uncheck the checkbox for a year, its data would disappear from the chart, and when checked, it would reappear.

Try it Yourself.. Download the file

You May Also Like the Following Excel Tutorials:

• Creating Dynamic Chart Titles in Excel.
• Dynamic Pareto Chart – The 80/20 Rule.
• Dynamic Target Line in Excel Bar Charts.
• How to Create a Dynamic Chart Range in Excel.
• Creating Combination Charts in Excel.
• Inserting a checkmark in Excel.