Create an Interactive Excel Chart Using Option Buttons

Updated on March 8, 2017

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.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Ashu 

      5 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

    • profile image

      Ashu 

      5 years ago

      OK I got it. but below B9 cell.

      how it is displaying whole data for selected cell.

    • profile image

      Sue 

      6 years ago

      Any Macros?

    • profile image

      Alan 

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

    • profile image

      Urbi 

      6 years ago

      Nice!!

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

    • profile image

      Vasyl 

      7 years ago

      Thanks a lot

      Extremely useful

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)