How to Find Form Controls for Excel Dashboards and Reports
Excel offers a set of controls called Form controls, designed specifically for adding user interface elements to your dashboards and reports. After you place a Form control on a worksheet, you can then configure it to perform a specific task.
You can find Excel’s Form controls on the Developer tab, which is initially hidden in Excel 2013. To enable the Developer tab, follow these steps:
Go to the Ribbon and click the File button.
To open the Excel Options dialog box, click the Options button.
Click the Customize Ribbon button.
In the list box on the right, you see all the available tabs.
Select the check box next to the Developer tab, as shown in this figure.
Now, click the Developer tab and choose the Insert command, as shown in the following figure. Here you find two sets of controls: Form controls and ActiveX controls. Form controls are designed specifically for use on a spreadsheet, whereas ActiveX controls are typically used on Excel UserForms. Because Form controls need less overhead and can be configured far easier than their ActiveX counterparts, you generally want to use Form controls.
Here are the nine Form controls that you can add directly to a worksheet, as shown in the following figure:
Button: Executes an assigned macro when a user clicks the button.
Combo Box: Gives a user an expandable list of options from which to choose.
Check Box: Provides a mechanism for a select/deselect scenario. When selected, it returns a value of True. Otherwise, it returns False.
Spin Button: Enables a user to easily increase or decrease a value by clicking the up and down arrows.
List Box: Gives a user a list of options from which to choose.
Option Button: Enables a user to toggle through two or more options one at a time. Selecting one option automatically deselects the others.
Scroll Bar: Enables a user to scroll to a value or position using a sliding scale that can be moved by clicking and dragging the mouse.
Label: Allows you to add text labels to your worksheet. You can also assign a macro to the label, effectively using it as a button of sorts.
Group Box: Typically used for cosmetic purposes, this control serves as a container for groups of other controls.