How to Add and Group Radio (Option) Buttons in Excel
In this article, you will learn how to add and group radio buttons in Excel.
Add a Radio Button
In Excel, you can add radio buttons (also called “option” buttons) to collect a user’s answer to a certain question. This is done through the Developer tab in the Ribbon. In case that you don’t have it, you can add the Developer tab by customizing the Ribbon. To add a radio button to the worksheet, follow these steps:
1. In the Ribbon, go to Developer > Insert and choose Option Button under Form Controls.
2. Now the cursor turns into the cross, and you have to position and draw the option button box.
As a result of this step, the option button is created. By default, option buttons are named sequentially as Option Button 1, 2, etc. Also, the caption of the option button is the same as its name.
Change the Option Button Text
To change the text that appears next to the option button, you have to follow these steps.
1. Right-click the option button, and choose Edit Text.
2. Change the text to what you need. This example uses months, so this first option button should be labeled January.
As you can see, after this step, only the text of the option button is changed to January, while the name remains the same (Option Button 1).
Link the Option Button to a Cell
To be able to get a user’s answer, you need to link the option button to a certain cell. This way, you will get the number of the checked radio button. Say you want to store this value in cell D2.
1. Right-click the option button, and choose Format Control.
2. In the Format Object window, (1) go to the Control tab, (2) enter a cell where you want to get the result ($D$2), and (3) click OK.
Now, if the option button is unchecked, the value of cell D2 is 0.
If you check the option button, the value of cell D2 changes to 1.
Insert Multiple Option Buttons
Since option buttons expect only one answer, it’s necessary to have two or more option buttons for one question. Say you want to have 12 option buttons to allow the user to select a month. You can simply copy and paste the existing radio button, insert more buttons from the Developer tab, or drag and fill cells with more buttons. The last option is the easiest one, so we’ll show that method. You can add multiple radio buttons by following these steps:
1. In a case that the first radio button is positioned in a cell (in this case B2), you can position a cursor in the right lower corner of cell B2 until it changes to the black cross.
2. Now drag the cursor and drop it at Row 13, as you need to create 11 more radio buttons.
As you can see, you have a total of 12 radio buttons. Since all of them are copied from the first one, the caption is the same (January), but the names are different (from Option Button 1 to Option Button 12). Also, all radio buttons link to the same cell as the first one (D2). Therefore, if you select the fifth button, you will get 5 in D2.
Finally, you need to change manually text of every radio button to the appropriate month. After that, the radio buttons group looks like this.
Group Option Buttons
In case when you have multiple questions and multiple option buttons for each question, you will need to group radio buttons by question. Let’s say that, in addition from months, you also have products radio buttons in Column C. In this case, you would like to get one month (in cell D2), and one product (in D3) as an answer. Now, if you select May and Speakers, you get 16 in cell D2.
This happens, because Excel puts all option buttons in one group, and in this case, you can select only one option from the existing 17. To create two radio buttons groups (one for months and the other for products), you need to add a group box for each group. To do this, follow these steps.
1. In the Ribbon, go to Developer > Insert, and choose Group Box in the Form Control.
2. Now the cursor turns into the cross, and you have to position and draw the group box. In the first group box, you have to put all radio buttons for months. Therefore, you should draw the first group box around cells B2:B13.
As a result of this step, the group box for months is created. Now you need to repeat these two steps and create a group box around radio buttons for products (cells B2:B6). If you now link product radio buttons to cell D3, you should have the selected index for months in D2, and for products in D3.
As you can see in the Month group, October is selected, so cell D2 is 10. Also, in the Product group, Monitor is checked, so 2 is the result in D3. This way, you can create multiple option buttons groups for different questions.
Note: You can also use VBA code to add and group multiple radio buttons.
Unfortunately, adding radio buttons in Google Sheets is not possible.