Select Answers With Excel Option Buttons

Select Answers with Excel Option Buttons

image Male or female? English or French? Yes, No or Maybe? Those are just a few of the choices that you can make with Option Buttons in Excel. When people select answers with Excel Option Buttons, you can provide a list of possible answers to a questions, and users can only select one answer from the list.

Option Button Questions

You can use Option Buttons instead of Data Validation drop down lists, or instead of allowing free form answers. Do you use Option Buttons? Love them? Hate them?

OptionButton12

Get Ready to Add Excel Option Buttons

To add Option Buttons in Excel 2007, you’ll use commands on the Ribbon’s Developer tab.

If you don’t see that tab on your Excel Ribbon, follow the instructions here: Show the Developer Tab on Excel Ribbon

Add a Group Box

To connect a set of Option Buttons to one another, you can enclose them in a Group Box control on the worksheet. By using Group Boxes, we could have one set of Option Buttons that show Language choices, and another set of Options Buttons for Age Group.

Without Group Boxes, all the Option Buttons on the worksheet would be connected, and you’d only be able to select one at a time. So, if we want users to answer two different questions, we’d create two Group Boxes for the Option Buttons.

To add a Group Box:
  • On the Excel Ribbon, click the Developer tab
  • In the Control group, click Insert
  • In the Form Controls section, click the Group Box command, to activate that tool.

OptionButton01

  • Click on the Excel worksheet, where you’d like the top left of the Group Box to appear.

A Group Box is created, with the default name of Group Box 1.

OptionButton02

Modify the Group Box

You can change the Caption and size of the Group Box, to suit your needs.

To change the Group Box Caption:
  • With the Group Box selected, drag over the Caption text, to select it.
  • Type a new Caption for the Group Box.

In the screenshot below, I’ve changed the Caption from Group Box 1 to Language.
OptionButton03

To change the Group Box size:
  • With the Group Box selected, point to one of the round white handles on its border.
  • Drag the handle in or out, to change the size of the Group Box.
  • You’ll see an outline as you drag the handle, so release the mouse button when the outline is the size that you want,

In the screenshot below, I’ve dragged the bottom right handle up and to the right, to make the Group Box shorter and wider.

OptionButton04

Add the Option Buttons

Now that the Group Box is created, you can add the Option Buttons inside the Group Box.

  • On the Excel Ribbon’s Developer tab, in the Controls group, click Insert
  • In the Form Controls section, click the Option Button command, to activate that tool.

    • Click inside the Group Box, where you want the top left of the Option Button to appear.
  • The entire Option Button and its Caption must be within the Group Box, so adjust the Group Box size, if necessary.

In the screenshot below, I had to make the Group Box a bit taller, to fit the Option Button 2.

OptionButton06

Modify the Option Buttons

Add as many Option Buttons as you need in the Group Box, so there’s one for each possible answer. Then, you can change the Option Button captions.

To change the Option Button Caption:
  • Right-click on the Option Button, and click Edit Text.
  • Select the existing Caption text, and type the new text for that Option Button

In the screenshot below, I’ve changed the Option Button Captions to English and French.

OptionButton07

Test the Option Buttons

After you’ve created the Group Box, and added the Option Buttons, you can test the Option Buttons.

  • When you click a button, a black circle should appear in it.
  • All the other Option Buttons should change to white circles.
  • If one or more of the Options Buttons don’t work correctly, adjust the Group Box size to enclose the entire Option Button and its label.

OptionButton08

Store the Option Button Selection

On the worksheet, you can see which option has been selected in each Group Box. If you want to use that selection in a formula, or store it in a list, you can link the results to a worksheet cell.

To link the Option Box to a cell:
  • Right-click on any one of the Option Boxes in the Group Box.
  • In the popup menu, click Format Control

OptionButton09

  • In the Format Control dialog box, click in the Cell link box
  • On the worksheet, click the cell where you want to store the Group Box selection number.
  • Click OK

In the screenshot below, the Option Box is linked to cell C3 on a sheet named Results.

OptionButton10

Now, when you click on one of the Option Boxes in the Group Box, the Option Box’s number will appear in the linked cell. The number is based on the order in which you created the Option Boxes.

OptionButton11

You could use that result in a formula, such as, =IF(Results!C3=2,”Bonjour”,”Hello”)

Creating a Survey Form

To see an example of Option Boxes in a survey form, take a look at Dave Peterson’s Excel Survey Template. It uses programming to create the Group Boxes and Option Boxes.

Watch the Option Button Video

To see the steps for creating a Group Box with Option Boxes, watch this short Excel Option Buttons video.

_____________

41 thoughts on “Select Answers With Excel Option Buttons”

  1. I do! I created our company’s employee review forms in Excel and used option buttons for the rating scales on each item. It worked pretty well for a few years and we’ve finally (thankfully) migrated to a web-based solution. (No more maintaining the forms and solving people’s problems–for ME anyway.)

    Also use them on some client projects. What I sometimes do is not include text in the option button itself, but place the button with no text near a cell that will contain the text. Then I can link the text to another cell or have it be a lookup. For example, I might set up the scale as “really bad” to “Super great” and duplicate the option buttons for each item we want rated. But then someone will decide the scale is “Subpar” to “Rock Star” and I have to go back and change all the buttons. The “use a cell” approach prevents me having to do that.

    I also like to get rid of the boxes once I have them all defined, but of course they have to stay in order to maintain their link to the correct cells. You can hide them though, using some VBA code I found in a newsgroup.

  2. I tried to use them. My Excel is 2003 and it seams that it is a little bit different from 2007 which you explained.
    I couldn’t find “Insert” in the following line:
    Add the Option Buttons
    Now that the Group Box is created, you can add the Option Buttons inside the Group Box.
    • On the Excel Ribbon’s Developer tab, in the Controls group, click Insert

    Am I missing something?

  3. Khoshravan, in 2003 you can create a group box by clicking on the Group Box icon on the same toolbar as the options buttons are found. Then you click and drag to make it the right size.

  4. I am still stuck with Excel 2003! I have used option buttons a number of times, but I did not know about the Group Box. However, the models still work somehow – exactly as you describe in the YouTube post.

    Is it because I habitually place a border around the area with the option buttons, and Excel works out what I’m trying to do?

  5. When I see this article for the first time, I thought I can fill a list with option button (in above example, I can input language for many records in rows).
    Later I find out that this command (option button) can be used only for one-entry purposes.
    I know I can use data validation for what I want to accomplish but thought this might be another alternative.

  6. I do use the buttons. I used it to recreate a Project Management – Risk Assessment Tool. It stores scores for each vendor, adds up assigned risk score and charts the risk in each category.

  7. Nice and easy. Thanks a lot for the tutorial, it made it much easier for us to create a small questionnaire.

  8. I want to create Multiple Choice Questions or Test using option buttons (or radio buttons). I have a set of 30 or more questions. Each questions has four possible answers. Only one will be the correct answer. How do I create the user interface to accept user input and then grade how user fare in the Test using Excel or Calc? Kindly help. Thank you.

  9. I think options buttons are a great resource. I used them to create a questionnaire for my company and it has really simplified the process. One thing I did that was different from the article- I found I was having serious issues butting my buttons inside group boxes. To remedy this, I actually created and placed the buttons first, then drew the group box on top of the buttons that I wanted to be linked. This worked for me and I had 200+ buttons.

  10. appreciate your support to provide me with the supposed steps to select more than one option button in the same time as it’s not clear and there is no any information appeared on the web site so thank you to inform me as soon as possible urgently..

  11. I’m trying to create a sample personality test in excel. Ranging from 1-5. Can anyone tell me how to put values in each of the choice? Thank you.

  12. You rock Debra! Finally someone does exist in this world who can explain option buttons to a novice. I had trouble creating multiple option buttons on one form and I was not aware of group box command. Thank you.

  13. Dear Sir,
    I rally appreciate and very thankful for this web help. I made company CSI format which appreciated by everyone.
    Really thankful of you.
    Regards
    Atul

  14. Question: I am completing a report form. I did not create the form, which is password protected. When I select an answer using the radio buttons, they become skewed on the page. Is there a way I can stabilize the radio buttons? Thanks for your assistance.

  15. Could you please advise if it is possible to extract the caption text based on selection in option group which consists of 3 option buttons?
    Thank you very much.

  16. Excel 2010: I have a group box with 3 option buttons. All the option buttons are inside the border of the group box (including the text for the option button). Only 2 of the 3 buttons function properly. I have a yes, no and n/a. Instead of only being able to choose one of those three, it allows for both yes or no AND n/a to be selected at the same How do I copy a group of buttons to a new cell and rename the group (I want to have several group sets on same worksheet and don’t want to have to create every time.

  17. Unfortunately this does not work when your using more than 2 buttons with different values or text on them as the (=IF) only knows “true” and “false”. So you can not ad a value to your 3rd, 4th, 5th etc buttons. Any help to make this work would be much appreciated. Cheers

    1. Unfortunately this does not work when your using more than 2 buttons with different values or text on them as the (=IF) only knows “true” and “false”. So you can not ad a value to your 3rd, 4th, 5th etc buttons. Any help to make this work would be much appreciated. Cheers

      1. Yeeha I figured it out I think. Here’s my formula for using more than 2 buttons. =IF(S12=1,”9″)+IF(S12=2,”6″,”0″)
        Use only the true value in first command. False value not placed. You can continue to do this for as many buttons you like.
        I think, it seems to work!!

  18. Considering the example would like to know is it possible to have default button (I want the radio button for “French” enabled as and when I open the excel).

  19. First of all, thank you. This was exactly the information I was trying to find in setting up a group box. I could not find it anywhere else or explained so simply. Now that I have my group box set up, is there any way to make the option button text larger, bold or even to change the font? I have not found any applicable information on text formatting.
    Thanks!

    1. @ Brian, Thanks, and you could delete the text that is attached to the option button. Then, add a Text Box, and enter the label text that you want. You’ll be able to format and resize that text.

  20. This is great information. I have a slightly different need. We need to have a checkbox allowing the user to check one of four choices. Each of those choices has a corresponding drop down list associated with it. We would like to have this work so that if check box A is selected the user can choose from the drop down list of A. However, if checkbox B is selected we want only the drop down list for B to be active. We have four checkboxes and four drop down lists. Thank you!

  21. Thank you very much. That worked great. I also like the tip that you can remove the option box text and add a text box with your own text that you can control the font. This is an awesome site.

  22. Thank you so much! Your explanation was the only one that I understood and I was finally able to create SEPARATE radio button controls. Not sure why Microsoft makes this so confusing. It’s so nice that people like you take the time to publish help like this.

  23. Thank you very much for your help.
    One more question: I created a survey file and I have on the row 6 option:
    1. “Not Aplicable N/A” 2.Strongly Disagree (Below Expectation) 3. Disagree 4.Neutral (Meets Expectations) 5. Agree 6. Strongly Agree (Exceeds Expectations)
    I’ve used the radio button and I created group starting with point Strongly Disagree (Below Expectation)(because the point 1 it is N/A).
    Now, my question is: when I tried to get results the formula calculate starting with the point that I formed the group. Is there a possibility to include in the group the N/A point and have 0 for it at final result?
    Final result I have obtained it Ctrl, Select box, cell formula…
    Many thanks.

    1. In a separate cell, you could use the CHOOSE function to assign a score, based on the option number that is shown in the linked cell. You can include the N/A choice in the group box too.
      For example, if your option buttons are linked to cell B3, and N/A is option #6, use this formula in cell C3: =CHOOSE(B3,1,2,3,4,5,0)
      Then you can hide column B, where the option numbers are shown.
      I’ve uploaded a sample file on my Excel Sample Files page:
      FN0040 – Option Button Choose Scores
      http://www.contextures.com/excelfiles.html#FN0040

  24. A few questions: (1) I want 3 options: no, yes or N/A. But, when I have them in a group box, you are still able to click more than one option at a time. (2) How do I get the number to go away when you make your choice? (3) Can you have the group box not have an outline? Thank you!

  25. Debra, I have created a form with 20 questions, each has 5 possible answers, my grouping all works fine and I get the results into the data table just fine with a “Record” macro. But how can I reset the option buttons back to blanks after I record my data. Currently they are all showing the previous selections, I want them to go back blank.
    Thanks for the great website!

    1. Hi Dennis-I know you left your comment an awfully long time ago, but I’m hoping you see this reply. I’m wondering if you can explain what the “record” macro is that you are using to get the result into a data table. I think this is the solution I’m looking for!

  26. Thanks Debra
    I have one question. When you change the region on sheet ‘SurveyRegion’, the colours dropdown list possible values are updated within the list, but the item currently in the cell C8 is not. So that, it’s not a valid value, unless you go in there and select a new valid value from the list. Indeed nothing even says that it’s not valid anymore. Do you know of a way of at least automatically erasing the selected colour in C8 each time one chooses a different region in the option button?

    1. @ExcelLover, you could assign a macro to each option button, so it clears out the colour cell when the region is changed.

      Sub ClearColour()
      Sheets("SurveyRegion") _
        .Range("C8").ClearContents
      End Sub
  27. Option button does not highlights after clicking on it coz I have used this for a specific cell for filling desired value in it what is the reason help plz

  28. Is there a way of having many ActiveX option buttons on a sheet and having 2 of them being True? I have tried placing them in separate Group Boxes and in separate ActiveX frames, neither works.

  29. I really like Contextures presentations for they are easy to follow and understand. THANK YOU from those of us you only aspire to be proficient in VBA. Suggestion. Seems no one makes it clear you do not have a Format Control when your option buttons are on a VBA UserForm. You store your selection by right clicking on the optionButton and in the properties window scroll down to Control Source and enter the cell which will capture if that optionButton was selected (True) or not (False). I made worksheet called DATA to store optionButton selections. So for optionButton1, e.g., across from Control Source I typed in DATA!C14
    For those unfamiliar how to designate a sheet name – use the exclamation character – ! after the sheet name.

Leave a Reply to Ahmed Nabil Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.