Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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

doc-interactive-charts2-2-2 doc-interactive-charts23-23

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.

doc combine multiple worksheets-1

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:

doc-interactive-charts3

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

doc-interactive-charts4 -2 doc-interactive-charts5

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:

doc-interactive-charts3

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:

doc-interactive-charts3

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:

doc-interactive-charts8 -2 doc-interactive-charts9

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

doc-interactive-charts3

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

doc-interactive-charts3

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

doc-interactive-charts3

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:

doc-interactive-charts3

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:

doc-interactive-charts3

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:

doc-interactive-charts3

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.

doc-interactive-charts4 -2 doc-interactive-charts5

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:

doc-interactive-charts8 -2 doc-interactive-charts9

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

doc-interactive-charts3

(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:

doc-interactive-charts3

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

doc-interactive-charts3

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

doc-interactive-charts3

(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:

doc-interactive-charts3

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

doc-interactive-charts4 -2 doc-interactive-charts5

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

Ribbon of Excel (with Kutools for Excel installed)

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!

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!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Erin · 3 years ago
    Is it possible to do this, but show the graph itself rather than a picture of the graph?
  • To post as a guest, your comment is unpublished.
    SAJEESH · 3 years ago
    Sir,

    Excellent Work, but i am facing issue with after closing and reopening the excel sheet - While selecting the combo box, only image is showing not showing the graph.

    Thanks and Regards
    SAJEESH - P
  • To post as a guest, your comment is unpublished.
    SAJEESH · 3 years ago
    Sir,

    Excellent work but when i closed the excel and reopened it - The graph which i called using the combo box is not working. It doesn't shows the GRAPH only the blank image.
  • To post as a guest, your comment is unpublished.
    TONI · 4 years ago
    Worked very well ,

    thnx....
  • To post as a guest, your comment is unpublished.
    Gene Monroe · 4 years ago
    I found that when I received "reference not valid" errors, it was (sometimes) because the worksheet name was not included prior to the name range. For example, if I have a named range for a data set ChlyScrnTotX, which refers to a dynamic range on a sheet named "Table" as =OFFSET($B$80,0,0,1,COUNTA($80:$80)), when I entered the named range in the Source Data for a graph on a different page of the workbook, I received the "reference not valid" error. When I re-wrote the named range to include the sheet name, as follows, the error was resolved.

    =OFFSET(Table!$B$80,0,0,1,COUNTA(Table!$80:$80))

    Good luck!