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

Create dynamic interactive charts using Drop down menu

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

Save 50% of your time, and reduce thousands of mouse clicks for you every day!

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?

Excel Productivity Tools

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

•  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
•  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
•  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
•  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
•  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
•  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
•  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
•  Change the way you work now, and give you a better life immediately!

Read More Download Now Purchase

---

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

• Increases your productivity by 50% when viewing and editing multiple documents.
• Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
• Open and create documents in new tabs of same window, rather than in new windows.
• Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!

Read More Download Now Purchase