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 first thing you need to do is have the raw data somewhere on the worksheet. For this example, there are four small tables of data showing the top 5 goal scorers from the Premier League, La Liga, Serie A and the Bundasliga.

League top goal scorers raw data
League top goal scorers raw data

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.

Insert the Option Buttons

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.

Inserting an option button in Excel
Inserting an option button in Excel

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

Option buttons on the Excel spreadsheet
Option buttons on the Excel spreadsheet
Specify the Cell Link for the control
Specify the Cell Link for the control

Specify the Cell Link

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.

Create the Chart Data Source

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.

Create the Interactive Chart

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

The finished interactive Excel chart using option buttons
The finished interactive Excel chart using option buttons

Final Steps

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.

More by this Author

  • Sort Pictures in an Excel List
    0

    If you are using pictures in your Excel spreadsheet you may be having some difficulty with sorting the pictures along with the other data in the list. The good news is that you can sort pictures in an Excel list just...

  • Simulate a Cup Draw using Excel
    0

    Microsoft Excel can do some amazing things. I like to attempt little projects testing what Excel can do. I was asked the other day to simulate a cup draw using Excel and this is the result. This article will give you...

  • Create Star Wars Movie Credits in PowerPoint
    2

    This hub will explain how to create the scrolling text credits from the Star Wars movies in your PowerPoint presentation. To create the Star Wars credits we will use different animation effects simultaneously. ...


6 comments

Vasyl 5 years ago

Thanks a lot

Extremely useful


Urbi 4 years ago

Nice!!

how to change this if I have more or less goal scorers?


Alan 4 years ago

You will just need to enter more or less goalscorers and change the chart data source to match the range. The same technique will be used, you will just need a larger or smaller range.


Sue 4 years ago

Any Macros?


Ashu 3 years ago

OK I got it. but below B9 cell.

how it is displaying whole data for selected cell.


Ashu 3 years ago

Hey Thanks I got solution.

But now my problem is.. in my one field it contain percentage and in one column its simple Value.

and when I select percentage eg- 34% our formula shows 0.34 value.

while it works fine in simple value.

what to do now.

Help

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working