How to create an interactive chart with drop-down list in Excel

  • Excel
  • 2016
Excel has a lot of different, useful features to help you to present data. There are a lot of different charts you can create (see charts). One of the features for presenting your data is an interactive chart with drop-down list.

See also interactive charts.

Interactive chart with drop-down list in Excel 2016

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

    1.    Add additional data to your spreadsheet for drop-down 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 list of row names, this step isn't needed).

For example:

Additional data for drop-down list in Excel 2016

    2.    Create data ranges, which will be used for the interactive chart:

    2.1.    On the Formulas tab, in the Defined Names group, click Define Name (see Creating and using named ranges for more details):

Defined Names in Excel 2016

    2.2.    In the New Name dialog box:

New Name dialog box in Excel 2016
  • 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, condition is the option to show this data series.

In this example:

= IF ($H$2=1, $C$3:$C$14, 0)

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

Chart with all data in Excel 2016

    4.    Change the data series:

    4.1.    Do one of the following:

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

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

Select Data Source in Excel 2016

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

Edit Series in Excel 2016

    4.4.    Repeat the previous steps for all other data series that you will show using the drop-down list.

    5.    Add the drop-down 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:

Controls, Combo box in Excel 2016

    5.2.    Right-click on the drop-down list and choose Format Control... form the popup menu:

Format Control in Excel 2016

    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 index of the selected item (in this example, H2 ),
  • In the Drop down lines field, type the number of items in the list:
Format Control Combo box in Excel 2016

Make any other adjustments to get the look you desire.

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

Custom colors for chart in Excel 2016

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

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.