How to create interactive chart with radio or option buttons

Excel 2016
Adding controls to Excel charts allows you to create interactive charts to avoid overloading a chart with many data series, or to highlight certain data instead of creating multiple charts. Usually, adding the Option Buttons (radio buttons) control leads to a copy of the data you need to display on the chart. This can be inconvenient in some cases, for example, with a large amount of data. To avoid overloading the spreadsheet, you can use Defined Names.

See other techniques for creating interactive charts.

Interactive chart with radio or option buttons in Excel 365

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

The trick to 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.

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

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

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 365

   2.   Define names

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):

Define Name in Excel 365

   2.2.   In the New Name dialog box, define the name of the data series to display:

  • 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> ...)

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, $C$2, $D$2):

New Name dialog box in Excel 365

   2.3.   Add the new data range to define the data series values to display:

  • 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))

The MATCH () function searches the specified element <name> in a range of data <headers> and then returns the relative position of that element in the range.

The INDEX () function returns the value of an element in a table or an array from the range of data specified by the parameter <table>, selected by the row (second parameter, 0) and column number (third parameter).

In this example:

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

New Name for data range in Excel 365

   3.   Create a chart with visible data

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

Chart with new data in Excel 365

   4.   Change the data series

   4.1.   Do one of the following:

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

   4.2.   On the Select Data Source dialog box, for the Legend Entries (Series), click the Edit button:

Select Data Source in Excel 365

   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 365

   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 (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 can 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 365

   5.2.   Format option buttons:

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

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

Format Control in Excel 365

   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 365

See also this tip in French: Comment créer un graphique interactif 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.