How to create a simple interactive chart with radio or option buttons

  • Excel
  • 2016
There are a lot of different features in Microsoft Excel, that can be useful for creating a presentation of your data. One of these features is interactive controls, that can be added to your Excel spreadsheet to create dynamic charts. 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 a simple interactive chart with radio or option buttons, do the following:

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

    1.1.    Add the new data to select one of the teams. For example, enter in the cell G2 the number 1:

Additional data for radio or option buttons in Excel 2016

    1.2.    To the next cells add the following 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)

So, you will see the column that will be shown in your chart:

New data for radio or option buttons in Excel 2016

    2.    Create a chart with visible data:

Chart with new data in Excel 2016

In this example:

  • Series 1: Volumes from the range E3:E14,
  • Series 2: Team A from the range H3:H14:
Select Data Source in Excel 2016

    3.    Add the option buttons to the chart:

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

    3.2.    Format option buttons:

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

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

Format Control in Excel 2016

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