Radio Button in Excel (Options Button)

Radio buttons or also known options buttons in excel are used to record a user’s input in excel, they are available in the insert section of the developer’s tab, for any criteria there can be multiple radio buttons but only a single button can be checked rather than multiple options, to insert a radio button we need to click on the insert command and we can then draw it in any cell we want.

Radio Button in Excel

A Radio Button in Excel, also called the Options Button, is used to select one of the various options. We have seen it on hundreds of web pages where we are asked to select an option by clicking on a small round shape next to the text. As soon as we select it, it gets a black dot in it (an indication that is marked).

How to Insert a Radio Button in Excel?

To insert a radio button in Excel, we must have the ‘Developer’ tab. To get the same, steps are:

You can download this Radio Button Excel Template here – Radio Button Excel Template

Below are the steps to insert a radio button in excel –

  1. Right-clicking anywhere on the ribbon.


    Radio button in excel - getting developer step 1

  2. Choose ‘Customize the Ribbon in Excelfrom the list. This will open the ‘Excel Options’ Dialog box. On the right side, make sure the checkbox for the Developer tab is ticked and then click on ‘OK.’


    Radio button in excel - getting developer step 2

  3. The Developer tab is now visible.


    Radio button in excel - getting developer step 3

  4. To insert the radio button in excel, we will choose the ‘Insert’ option in the Controls group on the Developer tab.


    Inserting the Radio Button - step 1

  5. After choosing Radio Button from the Form Controls in excelThe Form Controls In ExcelExcel Form Controls are objects which can be inserted at any place in the worksheet to work with data and handle the data as specified. These controls are compatible with excel and can create a drop-down list in excel, list boxes, spinners, checkboxes, scroll bars.read more, click anywhere on the worksheet, Option Button in excel will be created.


    Inserting the Radio Button - step 2

  6. As we can see, the caption (label) for the options button is by default ‘Option Button 1’. We can rename this by right-clicking and choosing ‘Edit Text.’


    Inserting the Radio Button - step 3

    We can also resize the Options Button along with the Gridlines of the cells. To do the same, the ALT key is very helpful. While pressing the ALT key, if we resize the Option Button in excel, then it is automatically adjusted along with gridlines.


    Inserting the Radio Button - step 4

    As the name itself indicates, we create option/radio buttons in Excel to choose one of the various options; that is why only one option button in excel is not enough. We need to create more. That we can create using Ctrl+D or using Copy-Paste (Ctrl+C and Ctrl+V).

    Inserting the Radio Button - step 5

    There is a main feature of an Option Button in excel that if we select one, others are deselected automatically. How can we get to know which one is selected? For this, we need to link the options buttons with a cell, where the sequence number of the options button in excel is displayed, and we can use this number for the different type of purpose (dynamic chart in excel, to display a message, etc.)

    Inserting the Radio Button - step 6

    In the ‘Format Control‘ Dialog box, under the ‘Control’ tab, please select the cell link and click on OK.

    Now the value of the C2 cell gets changed according to the options button in excel selected.

    Inserting the Radio Button - step 7

    Inserting the Radio Button - step 8

    As we can observe, all option buttons in excel are linked to only one cell and to each other. However, what if we have 10 questions in a survey, and there are 4 options for each question? Then we want to have 10 answers. In that case, we need to have a ‘Group box’ to group the option buttons in excel. The same can be found in the Developer Tab -> Controls group -> Insert Command – Group Box (Form Control).

    Inserting the Radio Button - step 9

    We need to cover all 5 options buttons in the excel group box.

    Inserting the Radio Button - step 10

    We can delete the label of the Group box using the ‘Edit Text’ contextual command, ‘Delete’ button, and resize the group box using the ALT key.

    Inserting the Radio Button - step 11

    In the same way, we can create more option buttons in excel and group boxes.

    Inserting the Radio Button - step 12

Create Dynamic Chart Using Radio Buttons in Excel (Options Buttons)

Suppose we have procurement data of an organization as below:

Radio Button in Excel - Dynamic Chart Creation

We need to make a dynamic chart so that if the user wants to view the chart based on either Production Volume or Production Cost, he can.

For this,

  • We first need to copy the first column of the table except ‘Total Row’ to a new range.
Inserting the Radio Button - step 13
  • Then we will create two option buttons in excel captioned as ‘Production Volume’ and ‘Production Cost.’
Inserting the Radio Button - step 14
  • We will link the Options buttons to ‘C1.’
step 15
  • Now we will use the value of C1 cell (1 for Production Volume and 2 for Production Cost) for displaying the data in the 2nd table and then making the dynamic chart.
  • We have used the ‘=INDEX($A$2:$C$12,ROWS($E$2:E2),$C$1+1)’ formula for range ‘F2:F12’ for showing data according to the selection of the radio button in Excel. (learn more about Index Excel Function and Row function in excel)
step 16

We can copy the formatting of the last column of Table 1 using the copy and Paste Special –> Format command.

step 17
  1. Now using the new table, we can create the ‘Clustered Column’ chart using Insert tab -> Charts Group -> Recommended Charts -> Clustered Column excel chart. Make sure you follow these steps after selecting the new table. We will be formatting the chart according to our choice.
step 18
  1. Now we will adjust Option Buttons in excel and wrap the location of them with Cells Borders and make font color as white of the C1 cell so that this is not displayed to the user (User does not get confused). He should be shown only the required information.
step 19

Now whatever we select ‘Production Volume’ or ‘Production Cost,’ the data in the table and chart is displayed accordingly.

Changing Cell Background Color with Radio Buttons in Excel using ActiveX Control

Steps to do the same

step 20
  • Please copy the first radio button using Ctrl+C and paste 2 two times using Ctrl+V. You can resize the Options buttons in excel using the ALT key.
step 21
  • Now we need to change the caption and name for all 3 radio buttons in excel using the contextual menu. Make sure the Design Mode is activated. In the Contextual menu, choose Properties and then change the (Name) and Caption using the ‘Properties’ dialogue box.

step 22.

Button - step 23

Radio Button using Excel VBA

  • Double click on each ‘Radio Button in excel’ to enter into the coding area and paste the following code in between the Sub procedure.

For ‘Red’ Radio Button in Excel

Range(“b2”).Interior.Color = 255

For ‘Green’ Radio Button in Excel

Range(“b2”).Interior.Color = 5296274

For ‘Please choose one of the colour’ radio button in excel

MsgBox “Choose one of the colour”

Button - step 25
  1. Save the Excel Workbook with .xlsm extension.

Now, if we click on the red, the background color of the B2 cell changes to ‘Red’ and if we select on the green, then the background color of the B2 cell changes to ‘Green,’ and if we select the 3rd radio button in Excel, then a message box is displayed showing the message ‘Choose one of the colors.’

Recommended Articles

This has been a guide to what is Radio Button (OptionsButton) in Excel. Here we provide a step by step guide to inserting Radio Button (Options Button) using Form Control and ActiveX Control along with practical examples and downloadable templates. You may also look at these useful tools in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>