How to create an interactive chart with radio or option buttons

  • Excel
  • 2016
To create interactive charts, you need to add some of the controls in Microsoft Excel. This tip is about using Option Buttons that can be easily added and customized.

See also interactive charts.

Interactive chart with radio or option buttons in Excel 2016

To create an interactive chart with radio or option buttons, do the following:

    1.    Add additional data to your spreadsheet for option button values:

For example, enter in the cell G2 the number 1:

Additional data for radio or option buttons 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 Name,
  • In the Refers to field, enter this formula:

= CHOOSE (<index>, <first item>, <second item> ... )

Where, index is the selected item from the following items: first item, second item, etc.

In this example:

= CHOOSE ($G$2, C2, D2)

    2.3.    Add the new data range with the following data:

New Name for data range in Excel 2016
  • In the Name field, enter the name of this new range, for example Team,
  • In the Refers to field, enter this formula:

= INDEX (<table>, 0, MATCH (<name>, <headers>, 0))

Where:

  • table is the data table, from which will be chosen the appropriate column,
  • name is the column name,
  • headers is the range for headers.

In this example:

= INDEX ($C$3:$D$14, 0, MATCH (Name, $C$2:$D$2, 0))

    3.    Create a chart for any of the columns (in this example, the first column for Team A ):

Chart with new 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), 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 of the created ranges:

Edit Series in Excel 2016

    5.    Add the option buttons 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 Option Button:

Controls, Option button in Excel 2016

Add as many option buttons as number of series, which will be selected by these option buttons and place them where you want.

In this example just two option buttons were used:

Chart with option buttons in Excel 2016

    5.2.    Format option buttons:

    5.2.1.    Double-click on every option button and change the label.

    5.2.2.    Right-click on the option button and choose Format Control... form the popup menu:

Format Control in Excel 2016

    5.2.3.    In the Format Control dialog box, on the Control tab, choose the appropriate cell in the Cell link field (in this example, G2 ):

Format Control Option button in Excel 2016

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.