3 Excel Form Controls That Make Any Report Interactive | Combo Box, Spin Button, Option Buttons, by PK: An Excel Expert on YouTube. 11 min 30 s.
- Video length
- 11 min 30 s
- Time to do it
- 30 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Right-click the ribbon and choose Customize the Ribbon. Tick Developer in the right-hand list and click OK so the Developer tab appears.
Click Developer, then Insert, and pick a control from the Form Controls section. Every control works the same way: it writes a number or position into a linked cell.
For the scorecard, insert a Combo Box and set its Input range to your list of sales reps and the Cell link to an empty cell.
Wrap your rep data in INDEX so the linked cell number pulls that rep's figures. Add an IF formula to show an On Target badge when sales beat target.
For the payment quote, insert a Spin Button and set Current value, Minimum, Maximum and Incremental change. Link it to a cell for the loan term or amount.
If you need decimals, divide the linked cell by 400 in your formula, since spin buttons only return whole numbers.
Use PMT with the linked rate, term and amount to show the monthly payment, then add an IF check against the budget.
For the regional report, insert Option Buttons and draw a Group Box around each set so the buttons work independently.
Link each option button group to its own cell and use CHOOSE on that cell to switch the table and chart between the 6 views.
Hide the helper cells by selecting their columns or rows and choosing Format, Hide. Then protect the sheet so the links stay intact.
Tips and common mistakes
Option buttons outside a Group Box all share one link and fight each other, so always draw the Group Box first.
Spin buttons only return whole numbers, so scale decimals with a divide-by-400 or similar factor in the formula.
Keep all cell links on a hidden helper sheet so users cannot overwrite them by accident.
Test each control after protecting the sheet, since locked cells can block the link from updating.
Questions
Do I need macros or VBA for these controls?
No. Form Controls work with cell links and normal formulas like INDEX, CHOOSE and PMT, so the workbook stays macro-free.
Why do my option buttons all change together?
They are sharing one cell link. Draw a Group Box around each set of buttons so each group gets its own linked cell.
Can I use these controls on Excel for Mac?
Form Controls are limited on Mac Excel. The Developer tab and full Form Controls work best on Excel for Windows.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
