Forum Discussion
How to unlink cells in Format Control
I am developing a survey in excel (because the questions are complicated and long and do not format well in e.g. MS Forms) and using option buttons to choose responses. Each question has 6 potential responses. In developer I have inserted a group box, into which I placed 6 option buttons. After formatting the buttons so that they snap to the excel grid I then linked them to a cell to make calculating responses easier.
On-line advice then suggested that I could copy and paste these to each new question. However, by doing this, I am only able to choose 1 option as they are ALL linked to the same cell.
How do I unlink each group box of 6 option buttons so that I can link each group individually to a different cell for each question? Or - do I have to create a group box and option boxes from scratch for each question set?
I have removed the questions for simplicity of view (note that there are 6 in Q1 and Q2 not visible) and highlighted the linked cell from Q1. As you can see - for Q5 this has given an answer of 26 (the 26th option button) rather than 2 (the 2nd button in Q5)
5 Replies
Thank you m_tarler...
Excel does appear to be treating each of the different groups as all part of the same group and updating the linked cell from one group will update them all
OK so I think I reproduced the problem. When I created 1 group box and then added all the select buttons into that box then after they were all created I moved them out of the box (or to a new box) they stayed linked to the original set. If I then removed the group box and added a new group box, it appeared to fix the issue but I did have to assign the referrence cell again.
The easiest way I found to delete or remove is to right click the name of the box then click somewhere else to get rid of the context menu then click Delete.
Hope that helps
Based on your description, you’re using Excel Form Controls (Option Buttons) inside Group Boxes to create survey questions, but when you copy-pasted them, all option buttons across different groups ended up linked to the same cell. Is exactly what happens when copied Form option buttons all keep the original Cell link reference.
When you copy a Group Box (Frame) with Option Buttons in Excel, all copied groups remain linked to the same cell by default. This causes all option buttons across questions to write to a single cell (e.g., selecting Q5’s 2nd button overwrites Q1’s value).
Here’s how you can try to fix it:
You do not need to recreate groups from scratch. Instead, adjust the linked cell for each copied group:
Step-by-Step…
1. Select the Group Box (the frame itself, not the buttons inside it):
- Click the border of the group box (the gray frame around the buttons) to select it.
2. Open Format Control Settings:
- Right-click the group box → Format Control.
3. Change the Linked Cell:
- In the Control tab, find the "Cell link" field.
- Replace the current cell reference (e.g., $B$2) with a new unique cell for that question (e.g., $B$3 for Q2, $B$4 for Q3, etc.).
.
- Critical: Use a different cell for each question (e.g., B2 for Q1, B3 for Q2, B4 for Q3, etc.).
When you copy a group box, Excel copies the linked cell reference but does not auto-update it.
Each group box (frame) should contain only its own 6 option buttons. If buttons from other groups are accidentally inside a single frame, they’ll act as one group.
Each group’s buttons start at 1 (e.g., Q1’s 1st button = 1, Q2’s 1st button = 1). Since they link to different cells, this is safe.
Excel automatically handles mutual exclusivity within a single group box. Just ensure each group has its own linked cell.
Do not set the linked cell for individual option buttons (this is handled at the group box level).
Do not copy/paste without updating the linked cell—this is the root cause of your issue.
Use a Consistent Cell Pattern
- Link all question responses to a single column (e.g., B2:B100), where:
- B2 = Q1’s answer
- B3 = Q2’s answer
- B4 = Q3’s answer
This makes data analysis (e.g., COUNTIF, SUM) much easier later.
This method saves time (no recreating groups) and ensures clean, error-free data collection.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Thank you Niko - this is clear and exactly what I had tried to do originally - unfortunately when I update the linked cell in one group box, it changes the linked cell for all the other group boxes... so for some reason they are not uncoupling
Hi Niko,
i hope maybe your solution might solve their issue but your description of the problem is not correct. What you say about the groups is correct and by default each group will still point at the same cell reference BUT in that case when selecting an option button in any group (e.g. button 2 in group 5 is selected) then ALL second option buttons in each group should be selected and the value in the cell should be 2 not 26.
Instead, excel appears to treating each of the different groups as all part of the same group OR not recognizing those boxes as Group Controls. I couldn't recreate this situation.
