You can use option buttons on a worksheet, to make it easy for people to pick just one item from several options. There’s no programming required – just set up a group box, add option buttons, and link the buttons to a cell. The video at the end of this post shows you those steps.
The option buttons send a value to their linked cell, and we’ll take a look at how you can use those numbers.
Option Button Numbering
When you add the option buttons, Excel automatically gives each one an index number, within its group. To see an index number, click one of the option buttons, and its index number appears in its Linked Cell.
TIP: Right-click on an option button to select it, and you’ll see its Linked Cell’s address in the formula bar. In this example, the option buttons are linked to cell B1.
In the screen shot below, I added the Lunch and Dinner options first. Later, I added an option button for Breakfast, and moved it to the top of the group box.
Even though Breakfast appears to be the first option button in the group, when I click that option, it puts a “3” in the linked cell (B1). Excel indexes them in the order that they were created.
Can’t Change the Index Numbers
You can’t change the index numbers for the existing buttons, but Excel can change them automatically. For example, if I delete the Dinner option (currently #2 in the index), the Breakfast option moves up to #2 in the group.
Keep that in mind if you’re deleting and adding option buttons in a group. If you’re going to use the index numbers in a formula later, be sure that you know what those numbers are!
Return a Different Number
Even though you can’t change the option button index numbers, you can use a formula to return a different number.
On one of my previous posts about option buttons, a commenter asked how they could give a zero value to one of the option buttons:
“Is there a possibility to include in the group the N/A point and have 0 for it at final result?”
Excel automatically numbers the option buttons starting with “1”, so we’ll need a formula to convert those index numbers to a different set of values.
Use the CHOOSE Function
In that article, I showed how to use a lookup table and the INDEX function, to assign a score to each option button value. Maybe the commenter couldn’t see how to add a zero, or found that setup too complicated.
For an easier solution, you could use the CHOOSE function. I created new buttons in the correct order, then added a “None” option to the Meals. Guests will be charged $5 for breakfast, $10 for lunch and $15 for dinner. If they select None, the cost will be zero.
In cell C1, enter this formula:
=CHOOSE(B1,5,10,15,0)
Click on one of the meal options, and its cost appears in cell C1.
How It Works
The CHOOSE function returns a specific numbered item from a list.
=CHOOSE(B1,5,10,15,0)
- If Dinner is selected, cell B1 contains the value 3.
- The formula has a list of 4 items — 5,10,15,0
- The 3rd item in that list is 15, and that is the cost of Dinner.
If the “None” option button is selected, its value is 4, so the 4th list item (0) will be returned.
Note: The list items in the CHOOSE formula can be ranges too, so you could enter values on a worksheet, and refer to those cells. For example, =CHOOSE(B1,E1,E2,E3,E4)
Download the Sample File
To see how the option buttons and formulas work, you can download the sample file. On the Excel Sample Files page, go to the Functions section, and look for FN0040 – Option Button Choose Scores. The file is in xlsx format, and does not contain macros.
Video: Set Up Group Box and Option Buttons
Watch this video to see the steps for setting up a group box and option buttons.
Or watch on YouTube: Pick Answers With Excel Option Boxes
______________
______________