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?


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
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.