Note: The other languages of the website are Google-translated. Back to English
Log in Register

How to create dynamic interactive charts in Excel?

Sometimes, when you demonstrate data by using a chart, you might encounter a situation that there is a lot of data to be presented. Making a chart to compare multiple sets of data, the chart may look mussy and chaotic. But with the help of an interactive chart, you can choose a particular product and analyze its performance and trend. For example, to show the data series in the chart based on a drop-down list, when selecting one item from the drop down, your corresponding data will be showed in the chart as below demo.

This article, I will talk about creating an interactive chart by using the drop-down list, radio buttons and checkbox.

Create a dynamic interactive chart by using the drop-down list

Create a dynamic interactive chart by using the radio buttons

Create a dynamic interactive chart by using the checkboxes

Create a dynamic interactive chart by using the drop-down list

To create the dynamic interactive chart by using a drop-down list, please do with the following steps:

1. First, you should insert a drop-down list form, please click Developer > Insert > Combo Box (Form Control), and then draw a combo box as below screenshots shown:

2. Then, right click the combo box, and select Format Control from the context menu, see screenshot:

3. In the Format Object dialog box, under the Control tab:

  • In the Input range text box, select the data range in the data table exclude the column heading(A2:I6);
  • In the Cell link text box, select an empty cell assigned for storing the drop-down menu output (B8);
  • In the Drop down lines box, enter the number of data points in your data set (in this case, 5).

4. Then, click OK button, now, the product names have been added into the drop-down, and the linked cell value (B8) characterizes the currently selected item from the menu as option 1, 2, 3, 4 or 5, see screenshot:

5. And then, copy the header of the original data, and leave an empty row for displaying the filtered sales data, see screenshot:

6. Enter the following formula into A12 and copy it across to I12, and it returns the value in the row which number corresponds to the currently selected item in the drop-down list, see screenshot:

=INDEX(A2:A6, $B$8)

Note: In the formula, A2:A6 is the first column data of your original data, and B8 is the cell linked to the combo box.

7. This step, you can create a chart based on the new helper data (A11:I12), please select this data and then insert a chart as you need, see screenshot:

8. At last, if you want to put the drop-down list on the top of the chart, please right click the combo box, and select Order > Bring to Front, see screenshot:

9. Then, drag the combo box on top of the chart, now, when you choose one option from the drop-down list, the corresponding data series will be displayed in the chart as below demo shown:

Create a dynamic interactive chart by using the radio buttons

If you want to show the data series in the chart based on the radio buttons, please do as this:

1. First, please insert the radio buttons, click Developer > Insert > Option Button (Form Control), see screenshot:

2. And then, draw several radio buttons based on your product names, here, I will draw 5 radio buttons, and rename them as the product names, see screenshot:

3. Then, right click on any of the radio buttons and select Format Control, see screenshot:

4. In the popped out Format Object dialog box, under the Control tab, click a blank cell to assign for storing the radio button output (B8). See screenshot:

5. Now, the linked cell value (B8) characterizes the currently selected radio button as option 1, 2, 3, 4 or 5, see screenshot:

6. After inserting the radio buttons, now, you should prepare the data for creating chart, please copy the row and column headers from the original table, and paste them into another place, see screenshot:

7. Then, enter the below formulas, and you will get the following data, see screenshot:

In cell B12: =IF($B$8=1,B2,NA()), and copy this formula into the entire row;
In cell B13: =IF($B$8=2,B3,NA()), and copy this formula into the entire row;
In cell B14: =IF($B$8=3,B4,NA()), and copy this formula into the entire row;
In cell B15: =IF($B$8=4,B5,NA()), and copy this formula into the entire row;
In cell B16: =IF($B$8=5,B6,NA()), and copy this formula into the entire row.

Note: In the above formulas, B8 is the cell linked to the radio button, 1,2,3,4,5 in each formula means the number linked to the radio buttons, B2, B3, B4, B5, B6 in each formula indicates the first data in each row that you want to display from the original table.

8. After preparing the data, then, please select the new data range (A11:I16), and insert a chart as you need, and now, the interactive chart is crated completely, when select one radio button, its corresponding data series will be displayed in the chart as below demo shown:

Create a dynamic interactive chart by using the checkboxes

The above method can only show one data series of the chart each time, if you need to show two or more data series each time, you can create an interactive chart with check boxes. If you have Kutools for Excel, with its Check Box Line Chart feature, you can generate a dynamic interactive line chart with check boxes in Excel. At the same time, you can decide which lines to be displayed in the chart by just checking the corresponding check boxes.

Note:To apply this Check Box Line Chart, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click Kutools > Charts > Category Comparison > Check Box Line Chart, see screenshot:

2. In the Check Box Line Chart dialog box, do the following operations:

  • In the Data range box, select the data series you will display in the chart;
  • In the Axis Labels box, select the axis labels data;
  • In the Legend Entries (Series) box, select the data you will display as the chart legend (where the checkboxes display).

3. After finishing the settings, please click OK button, and a dynamic interactive chart is created successfully, you can check or uncheck the checkboxes to show or hide the data series in the chart based on your need. See the below demo:

More relative articles:

  • Create Box And Whisker Chart In Excel
  • In Excel, a box and whisker chart, also named as box plots is used to display the statistical analyses which helps to show you how numbers are distributed in a set of data. For example, with the help of the box and whisker chart, you can display the statistical data of test scores between different subjects to identify which subject need more attention for the students. In this article, I will talk about how to create a box and whisker chart in each version of Excel.
  • Create Speedometer Or Gauge Chart
  • A gauge chart, also named as dial chart or speedometer chart which looks like a speedometer in cars using a needle to show information as a reading on a dial, when the data changes, the needle moves dynamically as well as below screenshot shown. In Excel, a gauge chart is composed of two Doughnut charts and a Pie chart, it shows the minimum, maximum and current values in the dial. It can be used for presenting sales performance of representatives or work completed as against total work or other situations with a visualization way. This article, I will talk about how to create a gauge or speedometer chart in Excel step by step.
  • Create A Column Chart With Percentage Change In Excel
  • In Excel, you may create a simple column chart for viewing the data trends normally. For making the data looks like more intuitively to display the variances between the years, you can create a column chart with percentage change between each column as below screenshot shown. In this type of chart, the up arrows indicate the increased percentage that the later year than the previous year while the down arrows indicate the decreased percentage.
  • Create Project Status Spectrum Chart In Excel
  • This tutorial will talk about how to create an especial chart – project status spectrum chart in Excel. The project status spectrum chart is a type of bar chart with a slider block which the bar is filled as a spectrum from red to yellow to green to represent the project status as below screenshot shown.

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.
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
Comments (23)
No ratings yet. Be the first to rate!
Suzette
Suzette
This comment was minimized by the moderator on the site
I am afraid Step 12 did not work as it said an arugument is missing
Reply
Wayne Peters
Wayne Peters
This comment was minimized by the moderator on the site
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
Gene Monroe
Gene Monroe     Wayne Peters
This comment was minimized by the moderator on the site
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
DMC
DMC
This comment was minimized by the moderator on the site
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
Ravi
Ravi
This comment was minimized by the moderator on the site
As per above, Step 12 results in an error message - how do we get across that hurdle?
Reply
Gene Monroe
Gene Monroe
This comment was minimized by the moderator on the site
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
Dianne
Dianne
This comment was minimized by the moderator on the site
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
matan
matan     Dianne
This comment was minimized by the moderator on the site
Hi dianne,
Its shows me an error when I name the range with name that already exsists.
Reply
Gene Monroe
Gene Monroe
This comment was minimized by the moderator on the site
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
TONI
TONI
This comment was minimized by the moderator on the site
Worked very well , thnx....
Reply
SAJEESH
SAJEESH
This comment was minimized by the moderator on the site
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
SAJEESH
SAJEESH
This comment was minimized by the moderator on the site
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
Erin
Erin
This comment was minimized by the moderator on the site
Is it possible to do this, but show the graph itself rather than a picture of the graph?
Reply
Candice
Candice
This comment was minimized by the moderator on the site
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
Tom
Tom
This comment was minimized by the moderator on the site
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
Jasmeet
Jasmeet
This comment was minimized by the moderator on the site
Great tutorial ! Been using this for one year!! One question, How to make 2 charts dynamic together connected to one radio button?
Reply
Frank
Frank
This comment was minimized by the moderator on the site
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
Todd
Todd
This comment was minimized by the moderator on the site
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
Mohammed
Mohammed
This comment was minimized by the moderator on the site
I cant get past step 12, it say, "reference not valid"
Reply
Salamay
Salamay
This comment was minimized by the moderator on the site
Would this work with Pivot Charts? I crash excel when I paste the copied range as image.
Reply
Charlie
Charlie
This comment was minimized by the moderator on the site
Wow! thank you so much for this. I was able to create interactive charts following your instructions. I was so happy. :)
Reply
ian
ian
This comment was minimized by the moderator on the site
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
There are no comments posted here yet
Load More
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0  Characters
Suggested Locations