In this article we will go through how to insert radio buttons in Excel, how they work and how to use the results. In another article – How To Insert Check Boxes In Excel, we went through the key differences between check boxes and radio buttons. In that article, we also did a walkthrough on how check boxes work in Excel. This time, we will go through radio buttons. In short, the key distinguishing features of radio buttons are that with radio buttons:

• Only one button can be selected at a time
• One button must be selected at any given time. That is, we cannot deselect all the buttons – there is one way to do this

How To Insert Radio Buttons

Similar to check boxes, we will first need to make sure Developer tab is enabled and the Excel file is saved in a macro-enabled format. Once we do, we can go to Developer tab at the top > Insert > Option button:

We can then click on anywhere within the spreadsheet and a radio button will be inserted:

Formatting Radio Buttons In Excel

Before we keep going, let’s go through some basic formatting with radio buttons.

First of all, if we click on the button, we will be selecting the button or choosing the option. To move it around or edit the button, we need to either:

• Hold the CTRL key and click on the button
• Right-click on the button

After we see the box around the button, we can move it around or resize it. We could also copy and paste to create more buttons rather than going to Developer > Insert > Options Buttons every time.

To edit the text next to the button, right-click the button and select Edit Text.

Linking Buttons To A Cell

The next step would be to link the buttons to a cell. Why do we need to do this? Because by linking the buttons to a cell, the relevant button selection will then be converted into a value in that cell. And of course with the value in that cell, we can then apply formulas or do conditional formatting with it.

To link the buttons to a cell:

• Right-click on the button
• Select Format Control
• In the Cell Link field, select the cell you want to link the button to:
  
  

Unlike check boxes where we need to link each check box manually to a different cell, because we can only select one radio button at a time, we only need to link the cell once. Also because of this difference between radio buttons and check boxes, the returned values from radio buttons are numbers whereas each check box returns a TRUE or FALSE result. With radio buttons, it depends on how many buttons have been inserted. Say there are 5 buttons and if the first one is selected, 1 will be returned as the value in the linked cell. If the third button is selected, 3 will be returned as the value in the linked cell:

How To Deselect All Buttons

At first glance it would seem like you cannot deselect all the radio buttons because by clicking on an already selected button, it…just stays selected, unlike a check box. But there is a way to deselect all buttons:

• Right-click the selected button
• Select Format Control
• In the Value section, make sure the button is unchecked:
  
  

And all buttons will be unchecked:

How To Use The Selection Result

The next step is to look at what we can do in Excel after a button is selected. After all there is not much use if we can create buttons but not use the results. It would just become a survey that requires manually calculation and analysis.

As an example, we exported our friend’s running log in Garmin and there’s a list of 290 running activities like below:

We can then insert a few buttons, one for each month:

Following the same steps as above, we’ve linked the buttons to cell N1. With that, if we select the “January” button, we will see the value 1 in cell N1. If we select “May”, we will see the value 5 in cell N1. You can link the buttons to any empty cell and it doesn’t matter because we are going to hide the value by changing the font color to white.

Because we now know that every time we select a different button, the value in cell N1 will update itself, we can now create a table and put in formulas that return different results depending on the value in cell N1:

=INDEX(M1:M12,N1,1)

We decided to use an INDEX function here. It is perfect in this situation. Whatever number is returned in N1 as a result, the INDEX function will return that row’s value in the M1:M12 array. If “July” is selected, N1 will return 7. The INDEX function will look for the 7th value in the M1:M12 range which is “July”. And once we’ve used INDEX function to identify the month, we can use various functions such as COUNTIF, SUMIF and AVERAGEIF to gather more information for the month:

As we can see, depending on the month selected, the values in the table change accordingly:

Summary: we’ve set up 7 buttons – one for each month. All the buttons are linked to cell N1. With INDEX function in Q1, we can identify the month selected with the radio buttons based on value in N1. Then the rest of the formulas refer to the value in Q1.

How To Group Buttons Together

So far we’ve only looked at situations where only one button at be selected at a time. But is it possible to select more than one button in a worksheet? Perhaps by assigning radio buttons together into groups so that in each group we could make a separate selection.

To do that, we will need to follow the steps below:

1. In the Developer tab, Insert > Group Box
   
   
2. Draw a Group Box on the worksheet
3. In the Develop tab, Insert > Option Button
4. Create two or more buttons
5. Put all the buttons inside the Group Box
6. Select all buttons and the Group box by holding CTRL key and left-clicking
7. Right-click > Group > Group
   

Follow the same process above to create more groups of buttons. With buttons being grouped separately, each set of buttons can be selected independently from buttons in other groups:

And of course we can link each group of buttons to a cell. In the example below, we linked the first group of buttons to cell E3 and the second group to cell E7:

Here’s an example below:

=IF(E3=1,”Head”, “Tail”)&” and “&IF(E7=1, “Head”, “Tail”)

If there’s anything you would like us to add to this article, please leave a comment below. We hope you now understand how radio buttons work in Excel, how to insert them and format them.