How to create dynamic interactive charts in Excel?

In Excel, if you have created multiple charts based on your range data series, and you want to make the charts look beautiful and clean. To do this, you can create the dynamic interactive charts in your worksheet, when you select one option, your corresponding chart will be showed as following screenshots. Here, I will introduce two types of interactive charts: Interactive charts using Drop down menu and Interactive charts using Option buttons.

Create dynamic interactive charts using Drop down menu	Create dynamic interactive charts using Option buttons

---

Combine / import multiple worksheets or csv files into one worksheet or workbook: In your daily work, to combine multiple worksheets, workbooks and csv files into one single worksheet or workbook may be a huge and headachy work. But, if you have Kutools for Excel, with its powerful utility – Combine, you can quickly combine multiple worksheets, workbooks or csv files into one worksheet or workbook. Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

---

Create dynamic interactive charts using Drop down menu

---

If you want the chart to change with choosing the item from the drop down list, you can solve this task with following steps:

First, you need to create some range names for your separate charts.

1. Create all charts that you want and put them neatly in your worksheet like this:

2. Then select all the range cells that the first chart is located, and click Formulas > Define Name, see screenshots:

3. In the New Name dialog, specify a range name for this chart, (Chart1 for example) and choose Workbook from the Scope drop down list, see screenshot:

4. Click OK, and then repeat the step2 and step3 to give a range name for other each charts like Chart2, Chart3…

Second, create a combo box for your interactive charts.

5. In blank range cells, type your chart names in a list as this:

6. Then insert a combo box into the worksheet by clicking Developer > Insert > Combo Box (if the Developer tab is not displayed in the ribbon, please click File > Option > Customize Ribbon to check Developer to display it on the ribbon), and then drag the mouse to draw a Combo Box in the worksheet, see screenshots:

7. Then right click the Combo Box, and select Format Control from the context menu.

8. In the Format Object dialog, click Control tab, then click  button from Input range to highlight the chart names you have listed in Step5, and then click  button from Cell link to select a blank cell beside the combo box. See screenshot:

9. Then click OK, and now, when you select an item from the Combo Box, the linked cell will display the linked number.

10. Then select the linked cell, N2 for instance, and then click Formulas > Define Name, in the New Name dialog, enter a range name for the linked cell, and type this formula =Choose($N$2,Chart1,Chart2,Chart3) into the Refers to field, (N2 is the linked cell reference, and Chart1,Chart2,Chart3 are the chart range names you have created in the former steps.) and click OK to finish this option, see following screenshot:

11. Then select the chart1 range cells and press Ctrl + C to copy the range chart, and then specify a cell and right click, select Paste Special > Linked Picture icon, see screenshot:

12. And now the linked picture of Chart1 has been inserted into the specified location, and then click this chart picture, and input this formula =Selectedchart (Selectedchart is the range name of the linked cell you are created in Step10) into the formula bar, and press Enter key. See screenshot:

13. And now your dynamic interactive charts have been created successfully. When you choose one item from the combo drop down list, the charts will be toggled automatically.

---

Demo: Create dynamic interactive charts using Drop down menu

---

Create dynamic interactive charts using Option buttons

Sometimes you want to use the Option buttons to choose the relative charts, here the following steps can help you to create the interactive charts using Option buttons.

1. Create the range names for the chart range as the same process as above Step1 to Step4.

2. Then insert the Option buttons and create the interactive charts as follows:

(1.) Click Developer > Insert > Option Button and then drag the mouse to draw an Option Button, then right click and choose Edit Text from the context menu, see screenshots:

(2.) Then enter your label of the Option button.

(3.) And then draw other Option buttons and change the labels as you need by repeating the above step (1) and (2), see following screenshot:

(4.) Then select one Option button and right click, and then choose Format Control, see screenshot:

(5.) In the Format Object dialog, click Control tab, and click  button from Cell link to select a blank cell beside the Option buttons.

(6.) Then click OK, now when you click one of the option buttons the linked number 1, 2 or 3 will appear in the cell you choose.

(7.) And then select the linked cell M19, and give a range name for it by clicking Formulas > Define Name, in the New Name dialog, specify a name and type this formula =Choose($M$19,Chart1,Chart2,Chart3) into the Refers to text box. (M19 is the linked cell reference, and Chart1,Chart2,Chart3 is the chart range name you are created in the former steps.) Then click OK. See screenshot:

(8.) Then copy one chart range and paste it as linked picture into a location, and select the chart picture, enter this formula =Selectedchart (Selectedchart is the range name of the linked cell you are created) into the formula bar, and press Enter key. And now when you click one option button, the related chart will be displayed.

---

Demo: Create dynamic interactive charts using Option buttons

---

Related articles:

How to auto update a chart after entering new data in Excel?

How to create combination charts and add secondary axis for it in Excel?

---