How to create dynamic interactive charts in Excel?

When presenting data using charts, you may encounter challenges such as visual clutter when dealing with large datasets. Comparing multiple data sets in a single chart can make it appear disorganized and hard to interpret. However, by using interactive charts, you can easily focus on specific data points or trends. For example, you can create a chart linked to a drop-down list, radio buttons, or checkboxes, allowing you to dynamically display data for a selected product or category.

This tutorial will guide you through creating dynamic interactive charts in Excel using these tools to enhance your data analysis and presentation.

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 to 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 following formulas in cells B12, B13, B14, B15 and B16 separately, and copy each formula to the entire row, see screenshot:

=IF($B$8=1,B2,NA())

=IF($B$8=2,B3,NA())

=IF($B$8=3,B4,NA())

=IF($B$8=4,B5,NA())

=IF($B$8=5,B6,NA())

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

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: