How to create interactive chart with dropdown list in Excel

A Combo Box control in Excel is defined by two main elements: the target cell with the number of the active list item, and the list itself as a range of data.

See other techniques for creating interactive charts.

To create an interactive chart with a drop-down list, do the following:

   1.   Prepare the data that will be shown in the chart

Add additional data to your spreadsheet for dropdown list values:

• Number as an index of the selected list item,
• All items in one column if you want to create a list from the column names (if you want to create a list of row names, this step isn't needed).

For example:

• Enter in the cell H2 the number 1,
• Column names in the range H3:H5 (see how to change columns to rows and vice versa):

   2.   Define names

Create data ranges, which will be used for the interactive chart, on the Formulas tab, in the Defined Names group, click Define Name (see Creating and using named ranges for more details):

   2.1.   Define whether to display the first data series:

In the New Name dialog box:

• In the Name field, enter the name of this new range, for example, SeriesA,
• In the Refers to field, enter this formula:

= IF (<condition>, <series data>, 0)

where, the <condition> is the option to show this data series.

   2.2.   Add the new data range for the other series.

In this example:

• Second data range:
  • Name: SeriesB,
  • Refers to: = IF ($H$2=2, $D$3:$D$14, 0),
• Third data range:
  • Name: SeriesC,
  • Refers to: = IF ($H$2=3, $E$3:$E$14, 0).

   3.   Create a chart that includes all data series

See how to create a combination chart for more details.

   4.   Change the data series

   4.1.   Do one of the following:

• On the Chart Design tab, in the Data group, choose Select Data:
  
• Right-click on the chart area and choose Select Data... in the popup menu:
  

   4.2.   On the Select Data Source dialog box, for the Legend Entries (Series), select the first data series that you would like to show using the dropdown list and click the Edit button:

   4.3.   In the Edit Series dialog box, change Series Y values (or Series values) to the appropriate names:

   4.4.   Repeat the previous steps for all other data series you will show using the dropdown list.

   5.   Add the dropdown list to the chart

   5.1.   On the Developer tab (see Show the Developer tab), in the Controls group, click the Insert drop-down list and then choose Combo Box (Form Controls):

Note: You can also add to the Quick Access Toolbar (see how to add commands to the Quick Access Toolbar):

• The entire Insert controls dropdown list,
• The Combo Box control.

   5.2.   Right-click on the dropdown list and choose Format Control... in the popup menu:

   5.3.   In the Format Control dialog box, on the Control tab:

• In the Input range field, select the range of item names (in this example, H3:H5),
• In the Cell link field, select the cell with the index of the selected item (in this example, H2),
• In the Drop down lines field, type the number of items in the list:

Make any other adjustments to get the look you desire.

Note: Excel will use the colors from the theme by default to show the data series. To use your colors for checked data series, create a new theme with custom colors. For example:

See how to change the default colors that Excel uses for chart series for more details.

See also this tip in French: Comment créer un graphique interactif avec une liste déroulante dans Excel.