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.
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.
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.
You are guest
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 years agoit will not work,its just copying & pasting as a image,you cant change its contents or cant use it as real graph
To post as a guest, your comment is unpublished.· 3 years agoi follow all the steps and on the last step i got an error saying Reference is not valid, i am using excel 2010
To post as a guest, your comment is unpublished.· 4 years agoWow! thank you so much for this. I was able to create interactive charts following your instructions. I was so happy. :)
To post as a guest, your comment is unpublished.· 4 years agoWould this work with Pivot Charts? I crash excel when I paste the copied range as image.
To post as a guest, your comment is unpublished.· 4 years agoI cant get past step 12, it say, "reference not valid"
To post as a guest, your comment is unpublished.· 4 years agoThis 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.
To post as a guest, your comment is unpublished.· 4 years agoWorks 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.
To post as a guest, your comment is unpublished.· 5 years agoGreat tutorial ! Been using this for one year!!
One question, How to make 2 charts dynamic together connected to one radio button?
To post as a guest, your comment is unpublished.· 5 years agoFor 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.
To post as a guest, your comment is unpublished.· 5 years agoYes, 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?
To post as a guest, your comment is unpublished.· 5 years agoIs 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.· 5 years agoSir,
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.· 5 years agoSir,
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.· 5 years agoWorked very well ,
To post as a guest, your comment is unpublished.· 6 years agoI 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.
To post as a guest, your comment is unpublished.· 6 years agoYes 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.
To post as a guest, your comment is unpublished.· 3 years agoHi dianne,
Its shows me an error when I name the range with name that already exsists.
To post as a guest, your comment is unpublished.· 6 years agoI 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.
To post as a guest, your comment is unpublished.· 6 years agoAs per above, Step 12 results in an error message - how do we get across that hurdle?
To post as a guest, your comment is unpublished.· 6 years agoThis 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?
To post as a guest, your comment is unpublished.· 6 years agoHi, 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?
To post as a guest, your comment is unpublished.· 6 years agoWayne, 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:
To post as a guest, your comment is unpublished.· 6 years agoI am afraid Step 12 did not work as it said an arugument is missing