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

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:

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:

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

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.

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.

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

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?

The Best Office Productivity Tools

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. 60-day money back guarantee.

Read More... Free Download... Purchase...

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!

Read More... Free Download... Purchase...

23 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

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.
akshay · 3 years ago
it will not work,its just copying & pasting as a image,you cant change its contents or cant use it as real graph
- Reply
To post as a guest, your comment is unpublished.
ian · 3 years ago
i follow all the steps and on the last step i got an error saying Reference is not valid, i am using excel 2010
- Reply
To post as a guest, your comment is unpublished.
Charlie · 4 years ago
Wow! thank you so much for this. I was able to create interactive charts following your instructions. I was so happy. :)
- Reply
To post as a guest, your comment is unpublished.
Salamay · 4 years ago
Would this work with Pivot Charts? I crash excel when I paste the copied range as image.
- Reply
To post as a guest, your comment is unpublished.
Mohammed · 4 years ago
I cant get past step 12, it say, "reference not valid"
- Reply
To post as a guest, your comment is unpublished.
Todd · 4 years ago
This worked great for me, but for some reason, one of my 5 graphs compresses and doesn't display properly. There are 5 charts, 4 work. Chart 4 is not readable as it is compressed into one tiny row. Any suggestions as to what may be wrong? Thanks.
- Reply
To post as a guest, your comment is unpublished.
Frank · 4 years ago
Works perfectly. But please verify: does this work in versions of Excel earlier than 2013? If it doesn't, then what could the workaround be? Thanks.
- Reply
To post as a guest, your comment is unpublished.
Jasmeet · 5 years ago
Great tutorial ! Been using this for one year!!

One question, How to make 2 charts dynamic together connected to one radio button?
- Reply
To post as a guest, your comment is unpublished.
Tom · 5 years ago
For me this gave an error =Choose($N$2,Chart1,Chart2,Chart3)
I replaced it with =Choose($N$2;Chart1;Chart2;Chart3)

Thank you for this tutorial. It makes sheets look neat.
- Reply
To post as a guest, your comment is unpublished.
Candice · 5 years ago
Yes, this is what I'm looking for as well.
If we use a picture of graph (linked picture), it shows picture only. However, for a real graph, if we put our mouse on it, it will show the sources and other available option to adjust the graph. Have you found a way to do this?
- Reply
To post as a guest, your comment is unpublished.
Erin · 5 years ago
Is it possible to do this, but show the graph itself rather than a picture of the graph?
- Reply
To post as a guest, your comment is unpublished.
SAJEESH · 5 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
- Reply
To post as a guest, your comment is unpublished.
SAJEESH · 5 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.
- Reply
To post as a guest, your comment is unpublished.
TONI · 5 years ago
Worked very well ,

thnx....
- Reply
To post as a guest, your comment is unpublished.
Gene Monroe · 6 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!
- Reply
To post as a guest, your comment is unpublished.
Dianne · 6 years ago
Yes there is a step missing.
At Step 12 --> before you name the linked picture --> Name the range you pasted it in (using the same name).
E.g. if you pasted the linked picture in A1:A5, highlight those rows and name them "SelectedChart"
Then click the chart image as shown in Step 12 and name it "SelectedChart".
Both the range and the image have to have the same name.
This worked for me. Hopefully it does for you.
- Reply
- To post as a guest, your comment is unpublished.
  matan · 3 years ago
  
  Hi dianne,
  Its shows me an error when I name the range with name that already exsists.
  
  Reply
To post as a guest, your comment is unpublished.
Gene Monroe · 6 years ago
I followed the directions precisely, and it works just fine. Double-check the spelling of your named ranges, etc. I had to make a couple of minor corrections to my names and formulas to ensure everything matched up. Would suggest keeping a seperate document in Word listing your name ranges and their formulas. That way, you can make changes in Word and copy/paste them into the Name Manager, eliminating any small typos that come with manual entry. The most common cause of my mistake was that my chart names did not match up precisely. Name, case, everything must be identical.
- Reply
To post as a guest, your comment is unpublished.
Ravi · 6 years ago
As per above, Step 12 results in an error message - how do we get across that hurdle?
- Reply
To post as a guest, your comment is unpublished.
DMC · 6 years ago
This simple approach seems to be a great solution for basic data and only a handful of potential views. However, if you want to offer dynamic interactive charting for complex data, with numerous potential end-user views, you need your drop downs to impact the data source itself, not just flip between predetermined outputs, no?
- Reply
To post as a guest, your comment is unpublished.
Wayne Peters · 6 years ago
Hi, I am following the instructions provided above for use of a drop down list. However when I get to step 12 and enter the range name in the formula bar, I keep getting a "reference is not valid" error message. Would you be able to assist me with this please?
- Reply
- To post as a guest, your comment is unpublished.
  Gene Monroe · 6 years ago
  
  Wayne, if your named range is on a different sheet, try including your sheet name in your range definition.
  
  For example, if you have a range in a sheet named "Table" that reads =OFFSET($B$80,0,0,1,COUNTA($80:$80))
  
  Try inserting your sheet name for that range as follows:
  =OFFSET(Table!$B$80,0,0,1,COUNTA(Table!$80:$80))
  
  Good luck!
  
  Reply
To post as a guest, your comment is unpublished.
Suzette · 6 years ago
I am afraid Step 12 did not work as it said an arugument is missing
- Reply

Login to your account

Password Reset