Function Problems

Link Excel Option Buttons to a Cell and Return a Value

Steve KSteve K Nov 14, 2026 870 views

Question details

Set up two mutually exclusive Excel option buttons to return different calculated results in a named cell.

Link Excel Option Buttons to a Cell and Return a Value
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet needs two option buttons that control the value displayed in a result cell.
Observed behavior
The first option may return “Not chosen” if the buttons do not share the correct linked cell or the formula tests the wrong value.
Before you start

Check that the buttons are Form Controls, not ActiveX controls, and identify an unused cell for their linked value. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.

Solution 1Recommended

Link the option button group and test its values with a formula

Use one linked cell to record the selected option and a separate result cell to calculate the output.

1
Create one button group

On the Developer tab, select Insert > Option Button under Form Controls and draw two buttons on the worksheet. Keep both in the same group: either on the worksheet outside group boxes or inside the same Form Controls group box.

2
Set the linked cell

Right-click an option button and select Format Control. On the Control tab, set Cell link to an unused cell such as $B$1, then click OK. Check the other button’s Format Control settings to confirm it uses the same linked cell.

3
Test each selection

Click each button and observe B1. Excel records the selected button’s position in the group, typically 1 or 2. Use the values you observe rather than assuming the visual order matches the numbering.

4
Name the linked cell

Select B1, enter Selection in the Name Box to the left of the formula bar, and press Enter. If you already use a named cell, check its reference under Formulas > Name Manager.

5
Enter the result formula

In a separate result cell, enter =IF(Selection=1,100,IF(Selection=2,200,"Not chosen")). Replace 100 and 200 with the desired values or calculations, then click each button to verify the result.

Link the option button group and test its values with a formula
Keep input and result separate: The linked cell stores the option number; the result formula must be in a different cell to avoid a circular reference. Name the result cell separately if needed.
Free Microsoft Office alternative

Use WPS Spreadsheets for selection-based calculations

WPS Office is a free, lightweight Office alternative with a familiar interface. Open compatible Excel workbooks and use a cell selection and formula to build a similar calculation without relying on Excel option buttons.

Use selection values and IF formulas to calculate resultsOpen and save Microsoft Excel XLSX filesWork in a familiar spreadsheet interface for an easier transitionUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the first option return “Not chosen”?

Click the first button and check the number in the linked cell. Then check that the formula references that cell or its correct name and tests the number Excel recorded.

How do I make two option buttons mutually exclusive?

Use Form Control option buttons in the same group. Place both on the worksheet outside group boxes, or place both inside the same Form Controls group box.

Can the linked cell also contain the result formula?

No. The linked cell receives the option number, so put the formula in a separate cell. You can assign a different name to each cell using the Name Box.

How can I check what a named cell refers to?

Open Formulas > Name Manager, select the name, and inspect its reference. Make sure the formula uses the name assigned to the option buttons’ linked cell.