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:

Dynamic Excel Chart with Series Selection Check-boxA 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:

Dynamic Excel Chart with Series Selection Check-box - Data Set

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

Dynamic Excel Chart with Series Selection Check-box - Insert Check Box

  1. Click anywhere in the worksheet to insert the check box. Change the name of the Checkbox from Check Box 1 to 2011.
  2. 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.
  3. Repeat Steps 2 to 4 to insert another check box for 2012, and use a different cell link (I have used C8 here).
  4. Copy the original chart data from B2:F5 and paste it in some other location. I have used B10:F13.

Dynamic Excel Chart with Series Selection Check-box - Final Data

  1. 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.
  2. 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.

Dynamic Excel Chart with Series Selection Check-box - Insert Column Chart Excel

  1. 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.

Dynamic Excel Chart with Series Selection Check-box - Change Chart Type Excel

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

Dynamic Excel Chart with Series Selection Check-box - final chart

  1. That’s it. You have a dynamic chart ready. Now when you un-check the check box for a year, its data would disappear from the chart, and when checked, it would reappear.

Try it Yourself.. Download the file
Download File Pic

Related Tutorials:
  • MAndeep

    good

  • Pablo Alves Morais da Silva

    Very good this article

    • Thanks for commenting Pablo.. Glad you like it 🙂

  • KUANGYU

    great job. It’s very impressive and clear, thanks you.