Concept
1. Where they are
Developer tab (File → Options → Customize Ribbon → Developer) → Insert → Form Controls. Use Form Controls, not ActiveX — they're simpler, stable, and work on Mac and in more versions.
Every control has Format Control → Cell link: the cell where it writes its value. Put linked cells in CALC and name them (inp_…).
2. Combo box (dropdown)
Format Control: Input range = a list (e.g. CALC!$F$5:$F$9 with All, East, North, South, West), Cell link = inp_RegionIdx, Drop-down lines = 8.
It returns the position (1, 2, 3…), not the text. Convert:
inp_Region: =INDEX(CALC!$F$5:$F$9, inp_RegionIdx)
Simpler alternative: a cell with Data Validation → List gives the text directly and works with spilled lists (=lst_Regions). Use the combo box when you want it to float over the dashboard like an app control.
3. Spin button
Format Control: Minimum 1, Maximum 12, Incremental change 1, Cell link inp_MonthNo.
Selected month: =EDATE(DATE(2025,4,1), inp_MonthNo-1) → Apr-2025 … Mar-2026
Top N: spin 3–10 linked to inp_TopN
Place a cell next to it showing the current value (=TEXT(selected month,"mmm-yy")).
4. Checkbox
Format Control → Cell link inp_ShowLY → returns TRUE/FALSE. Use it to show or hide a chart series:
Last-year series: =IF(inp_ShowLY, CALC!D20, NA())
NA() values are not plotted, so unticking removes the LY line from the chart. (Also: show/hide targets, include returns, etc.)
5. Option buttons
Draw a Group Box first, then option buttons inside it (Revenue · Profit · Orders). All buttons in a group share one Cell link, e.g. inp_Metric = 1, 2 or 3.
Metric name: =CHOOSE(inp_Metric, "Revenue", "Profit", "Orders")
Metric value: =CHOOSE(inp_Metric, rev_cell, profit_cell, orders_cell)
One chart now switches between three measures — and the title (Module 3, Lesson 4) can include the metric name.
6. Scroll bar (bonus)
Like a spin button with a slider: useful to scroll a long table (Minimum 1, Maximum = rows − visible rows) with INDEX/TAKE/DROP showing a "window" of rows.
7. Tips
- Right-click a control to select it without triggering it.
- Format Control → Control → 3-D shading off for a flat look.
- Properties → Don't move or size with cells; group a control with its label.
- Controls stay usable on a protected sheet if their linked cells are on an unprotected CALC sheet or unlocked cells.
Common mistakes
Forgetting combo boxes return a number. Linked cells on DASH where users can overwrite them. Option buttons without a group box (all buttons on the sheet become one group). ActiveX controls that break on other PCs.