Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
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 (Table of Contents)
- Radio Button in Excel
- How to Insert a Radio Button (Options Button) in Excel?
- Create Dynamic Chart Using Radio Buttons in Excel (Options Buttons)
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:
- Right-clicking anywhere on the ribbon.
- Choose ‘Customize the Ribbon‘ from the list. This will open the ‘Excel Options’ Dialog box. On the right side, make sure checkbox for Developer tab is ticked and then click on ‘OK’.
- Developer tab is now visible.
To insert the radio button in excel we will choose the ‘Insert’ option in the Controls group on the Developer tab.
After choosing Radio Button in excel from the Form Controls, click anywhere on the worksheet, Option Button in excel will be created.
As we can see the caption (label) for the option button is by default ‘Option Button 1’. We can rename this by right-clicking and choosing ‘Edit Text’.
We can also resize the Options Button along with the Grid lines of the cells. To do the same, ALT key is very helpful. While pressing ALT key, if we resize the Option Button in excel, then it is automatically adjusted along with gridlines.
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).
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 sequence number of the option button in excel is displayed and we can use this number for the different type of purpose (dynamic chart in excel, to display message etc.)
In ‘Format Control’ Dialog box, under ‘Control’ tab, please select the cell link and click on OK.
Now the value of the C2 cell gets changed according to the option button in excel selected.
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 ‘Group box’ to group the option buttons in excel. The same can be found in Developer Tab -> Controls group -> Insert Command – Group Box (Form Control).
We need to cover all 5 option buttons in excel group box.
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.
In the same way, we can create more option buttons in excel and group boxes.
Create Dynamic Chart Using Radio Buttons in Excel (Options Buttons)
Suppose, we have procurement data of an organisation as below:
We need to make dynamic chart so that if user wants to view the chart based on either Production Volume or Production Cost, he can.
- We first need to copy the first column of table except ‘Total Row’ to a new range
- Then we will create two option buttons in excel captioned as ‘Production Volume’ and ‘Production Cost’.
- We will link the Option buttons to ‘C1’
- 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)
We can copy the formatting of last column of table 1 using copy and Paste Special –> Format command.
- Now using the new table, we can create the ‘Clustered Column’ chart using Insert tab -> Charts Group -> Recommended Charts -> Clustered Column chart Make sure you follow these steps after selecting the new table. We will be formatting the chart according to our choice.
- Now we will adjust Option Buttons in excel and wrap the location of them with Cells Borders and make font colour 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 required information.
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
- We need to create 3 ActiveX Radio button using Developer tab -> Controls Group -> Insert Command -> Option Button ActiveX Control
- Please copy the first radio button using Ctrl+C and paste 2 two times using Ctrl+V. You can resize the Option buttons in excel using ALT key.
- 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 ‘Properties’ dialogue box.
Radio Button using Excel VBA
- Double click on each ‘Radio Button in excel’ to enter into 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”
- Save the Excel Workbook with .xlsm extension.
Now if we click on red, background colour of the B2 cell changes to ‘Red’ and if we select on green, then background colour of the B2 cell changes to ‘Green’ and if we select 3rd radio button in Excel, then a message box is displayed showing the message ‘Choose one of the colour’.
This has been a guide to what is Radio Button (OptionsButton) in Excel. Here we provide a step by step guide to insert 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 –