Create an Interactive Excel Chart using Option Buttons

Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart.

The goal is to have an option button for each league. The user can then click on the league they want to view, and the chart will change to show the user the appropriate goal scorers.

To insert the option buttons onto the spreadsheet you will need to have the Developer tab on the ribbon.If you do not already have this, click the Office Button > Excel Options, click the Show Developer tab in the Ribbon option and click Ok.

Now to insert the option buttons in Excel;

1. Click the Developer tab on the Ribbon
2. Click the Insert button in the Controls group
3. Click on the Option Button under the Form Controls header
   
4. Click on the spreadsheet where you want to position the control and repeat this step for the number of option buttons that you need
5. Right click on each option button in turn and select Edit Text to change the label that accompanies the button. Alternatively you can enter the label in the adjacent cell which is the approach taken in this example

For the option buttons to work you need to specify a cell to hold the user response.

1. Right click on one of the controls and select Format Control from the shortcut menu
2. Click in the Cell Link box and then select the cell on the spreadsheet that you want to use
3. Click Ok

Now when you click on one of the option buttons the result of 1, 2 or 3 and so on will appear in the cell you chose. This number represents the button you clicked on.

The next step is to create the data source for the chart. This table of data will change depending on the option button selection.

The formula below is used to check the cell linked to the action buttons to see what number is in it, and then display the data from the appropriate league.

=CHOOSE($B$9,B1,E1,H1,K1)

This formula is then copied to the required cells.

The CHOOSE function checks cell B9 to see what option was selected. Then displays the content of the cell from the appropriate index number e.g. If the cell contains 1 then display Premier League data, if it displays 2 then show La Liga data and so on.

The last step is to create the chart from the interactive data source that was just created.

1. Select the cell range you want to use
2. Click the Insert tab on the Ribbon
3. Click the Column Chart button and select Clustered Column

In a real scenario steps would be taken to tidy the workbook up including hiding the gridlines and hiding the cell holding the option button response and cells being used for the chart data source.

This data is untidy and would usually be placed on a separate sheet entirely, or on hidden columns so as not to confuse the user.