Today, business professionals increasingly want to be empowered to switch from one view of data to another, in Excel dashboards and reports, with a simple list of choices. For those who build dashboards and reports, this empowerment comes with a whole new set of issues. The overarching question is — how do you handle a user who wants to see multiple views for multiple regions or markets?

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.

To add a control to a worksheet, simply click the control that you require and click the approximate location that you want to place the control. You can easily move and resize the control later just as you would a chart or shape.

After you add a control, you want to configure it to define its look, behavior, and utility. Each control has its own set of configuration options that allow you to customize it for your purposes. To get to these options, right-click the control and select Format Control. This opens the Format Control dialog box, illustrated in this figure, with all the configuration options for that control.


Each control has its own set of tabs that allow you to customize everything from formatting to security to configuration arguments. You see different tabs based on which control you’re using, but most Form controls have the Control tab, where the meat of the configuration lies. Here, you find the variables and settings that need to be defined for the control to function.

The Button and Label controls don’t have the Control tab. They have no need for one. The button simply fires whichever macro you assign it. As for the label, it’s not designed to run macro events.