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?
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.
- 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.
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.
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.
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.
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.
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.
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
- 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.
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.
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.
_____________
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.
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
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!
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!
@Dennis, if you have cells linked to those option buttons, just clear the linked cells.
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!
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?
@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 SubOption 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