How to Add and Group Radio (Option) Buttons in Excel

This tutorial demonstrates 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. If you don’t have it, you can add the Developer tab by customizing the Ribbon.

Go to Add the Developer Ribbon before starting, if necessary.

1. In the Ribbon, go to Developer > Insert and choose the Option Button under Form Controls.

 

 

2. The cursor turns into a cross. Position and draw the option button box.

 

 

Step 2 creates the option button. 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, 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, link the option button to a certain cell. This way, you 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, go to the Control tab, enter the cell you want populated with the result ($D$2), and 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. With the first radio button positioned in a cell (here, B2), you can position the cursor in the lower-right corner of cell B2 until it changes to a black cross.

 

 

2. Now drag the cursor and drop it at Row 13 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 get the value 5 in D2.

 

 

Finally, change manually text of every radio button to the appropriate month. After that, the radio buttons group looks like this.

 

 

Group Option Buttons

If you have multiple questions and multiple option buttons for each question, group radio buttons by question. In addition to months, you also have products radio buttons in Column C. Say you want to get one month (in cell D2), and one product (in D3) as an answer. Now, if you select May and Speakers, you get the value 16 in cell D2.

 

 

This happens, because Excel puts all option buttons in one group; you can select only one option from the existing 17. To create two radio buttons groups (one for months and the other for products), add a group box for each group.

1. In the Ribbon, go to Developer > Insert, and choose Group Box in the Form Control.

 

 

2. The cursor turns into a cross. Position and draw the group box. In the first group box, you must add all radio buttons for months. Therefore, you should draw the first group box around cells B2:B13.

 

 

3. This step creates the group box for months. Now repeat Steps 1 and 2 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.