How to create simple interactive chart with radio or option buttons

Excel 2016
Microsoft Excel has many useful tools for creating an interactive presentation of your data. One such feature is interactive controls that can be added to the Excel spreadsheet to create dynamic charts. To avoid cluttering the chart with many different data series, Excel offers using various controls, such as Option Buttons, that can be easily added and customized.

Option Buttons (also known as radio buttons) are used to select from a list of options.

See other techniques for creating interactive charts.

Interactive chart with radio or option buttons in Excel 365

The trick of using Option buttons is to assign the same cell to all the Option buttons in a particular group. Initially, the cell value is assigned to the first element of the option group. Next, all added Option buttons will automatically use and change the value of this cell.

Of course, you can create several different groups of options that will be linked to different cells - each group of options has its own data cell.

To create a simple interactive chart with 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 365

   1.2.   To the next cells, add the following formula:

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

The CHOOSE () function returns the value from the list (<first item>, <second item>, etc.) specified by the first parameter <index>.

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 365

   2.   Create a chart with visible data

Chart with new data in Excel 365

See how to create a combination chart for more details.

In this example:

  • Volume: Volumes from the range E3:E14,
  • Team A: Team A from the range H3:H14:
Select Data Source in Excel 365

   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 dropdown list and then choose Option Button (Form Controls):

Controls, Option button in Excel 365

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:
    Insert dropdown list in Quick Access Toolbar Excel 365
  • The Option Button control.

Add as many option buttons as the number of series 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 365

   3.2.   Format option buttons:

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

   3.2.2.   Right-click on each Options button and choose Format Control... in the popup menu:

Format Control in Excel 365

   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 365

See also this tip in French: Comment créer un graphique interactif simple avec des boutons radio ou des cases d'option.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.