In This Chapter

Introducing Form controls

Using a Button control

Using a Check Box control to toggle a chart series

Using an Option Button control to filter your views

Using a combo box to control multiple pivot tables

Using a list box to control multiple charts

Today, business professionals increasingly want to be empowered to switch from one view of data to another 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?

Fortunately, Excel offers a handful of tools that enable you to add interactivity into your presentations. With these tools and a bit of creative data modeling, you can accomplish these goals with relative ease. In this chapter, you discover how to incorporate various controls, such as buttons, check boxes, and scroll bars, into your dashboards and reports. Also, I present you with several solutions that you can implement.

Getting Started with Form Controls

Excel offers a set of controls called Form controls, designed specifically for adding user interface elements directly onto a worksheet. After you place a Form control on a worksheet, you can then configure it to perform a specific task. Later in this chapter, I demonstrate how to apply the most useful controls to a presentation.

Finding Form controls

You can find Excel’s Form controls on the Developer tab, which is initially hidden in Excel. To enable the Developer tab, follow these steps:

1. Go to the Ribbon and click the File button.
2. In the menu that appears, click the Options button.

3. In the Excel Options dialog box that appears, click the Customize Ribbon button.

   In the list box on the right, you see all available tabs.

4. Select the check box next to the Developer tab; see Figure 11-1.
5. Click OK.

Now click the Developer tab and choose the Insert command, as shown in Figure 11-2. 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 can be configured far more easily than their ActiveX counterparts, you generally should use Form controls.

Here are the nine Form controls that you can add directly to a worksheet, as shown in Figure 11-3:

• 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.

Adding a control to a worksheet

To add a control to a worksheet, simply click the control that you require and click the approximate location where 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 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 from the menu that appears. This opens the Format Control dialog box, illustrated in Figure 11-4, 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. There, 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.

Throughout the rest of this chapter, you walk through a few exercises that demonstrate how to use the most useful controls in a reporting environment. At the end of this chapter, you’ll have a solid understanding of Form controls and how they can enhance your dashboards and reports.

Using the Button Control

The Button control gives your audience a clear and easy way to execute the macros you’ve recorded. To insert and configure a Button control, follow these steps:

1. Click the Insert drop-down list under the Developer tab.
2. Select the Button Form control.

3. Click the location in your spreadsheet where you want to place the button.

   The Assign Macro dialog box appears and asks you to assign a macro to this button, as shown in Figure 11-5.

4. Edit the text shown on the button by right-clicking the button, highlighting the existing text, and then overwriting it with your own.

   To assign a different macro to the button, simply right-click and select Assign Macro from the menu that appears in order to reactivate the Assign Macro dialog box. (Refer to Figure 11-5.)

   When you add macros to a workbook, you have to save that workbook as an .xlsm file in order to share your macros with others. If you save the workbook as a standard .xlsx file, Excel strips your macros out of the workbook.

Using the Check Box Control

The Check Box control provides a mechanism for selecting and deselecting options. When a check box is selected, it returns a value of True. When it isn’t selected, False is returned. To add and configure a Check Box control, follow these steps:

1. Click the Insert drop-down list on the Developer tab.
2. Select the Check Box Form control.
3. Click the location in your spreadsheet where you want to place the check box.
4. After you drop the Check Box control onto your spreadsheet, right-click the control and select Format Control from the menu that appears.
5. Click the Control tab to see the configuration options shown in Figure 11-6.

6. Select the state in which the check box should open.

   The default selection (Unchecked) typically works for most scenarios, so it’s rare that you’d have to change this selection.

7. In the Cell Link box, enter the cell to which you want the check box to output its value.

   By default, a Check Box control outputs either True or False, depending on whether it’s checked. Notice in Figure 11-6 that this particular check box outputs to cell A5.

8. (Optional) You can select the 3-D Shading check box if you want the control to have a three-dimensional appearance.
9. Click OK to apply your changes.

To rename the Check Box control, right-click the control, select Edit Text from the menu that appears, and then overwrite the existing text with your own.

As Figure 11-7 illustrates, the check box outputs its value to the specified cell. If the check box is selected, a value of True is output. If the check box isn’t selected, a value of False is output.

If you’re having a hard time figuring out how this could be useful, take a stab at the exercise in the following section, which illustrates how you can use a check box to toggle a chart series on and off.

Toggling a Chart Series On and Off

Figure 11-8 shows the same chart twice. Notice that the top chart contains only one series, with a check box offering to show 2011 trend data. The bottom chart shows the same chart with the check box selected. The on/off nature of the Check Box control is ideal for interactivity that calls for a visible/not visible state.

To download the Chapter 11Samples.xlsx file, go to this book’s companion website.

You start with the raw data (in Chapter 11Samples.xlsx) that contains both 2011 and 2012 data; see Figure 11-9. The first column has a cell in which the Check Box control will output its value (cell A12 in this example). This cell will contain either True or False.

Next, create the analysis layer (staging table) that consists of all formulas, as shown in Figure 11-10. The chart actually reads from this data, not the raw data. This way, you can control what the chart sees.

As you can see in Figure 11-10, the formulas for the 2012 row simply reference the cells in the raw data for each respective month. You do that because you want the 2012 data to appear at all times.

For the 2011 row, test the value of cell A12 (the cell that contains the output from the check box). If A12 reads True, you reference the respective 2011 cell in the raw data. If A12 doesn’t read True, the formula uses Excel’s NA() function to return an #N/A error. Excel charts can’t read a cell with the #N/A error. Therefore, they simply don’t show the data series for any cell that contains #N/A. This is ideal when you don’t want a data series to be shown at all.

Notice that the formula shown in Figure 11-10 uses an absolute reference with cell A12 — that is, the reference to cell A12 in the formula is prefixed with a $ sign ($A12). This ensures that the column references in the formulas don’t shift when they’re copied across.

Figure 11-11 illustrates the two scenarios in action in the staging tables. In the scenario shown at the bottom of Figure 11-11, cell A12 is True, so the staging table actually brings in 2011 data. In the scenario shown at the top of Figure 11-11, cell A12 is False, so the staging table returns #N/A for 2011.

Finally, create the chart that you saw earlier in this section (refer to Figure 11-8) using the staging table. Keep in mind that you can scale this to as many series as you like.

You can apply this technique to as many check boxes as you need. For instance, Figure 11-12 illustrates a chart that has multiple series whose visibility is controlled by Check Box controls. This allows you to make all but two series invisible so that you can compare those two series unhindered. Then you can make another two visible, comparing those.

Using the Option Button Control

Option buttons allow users to toggle through several options one at a time. The idea is to have two or more option buttons in a group. Then selecting one option button automatically deselects the others. To add option buttons to your worksheet, follow these steps:

1. Click the Insert drop-down list under the Developer tab.
2. Select the Option Button Form control.
3. Click the location in your spreadsheet where you want to place the option button.
4. After you drop the control onto your spreadsheet, right-click the control and select Format Control from the menu that appears.
5. Click the Control tab to see the configuration options shown in Figure 11-13.

6. Select the state in which the option button should open.

   The default selection (Unchecked) typically works for most scenarios, so it’s rare that you’d have to change this selection.

7. In the Cell Link box, enter the cell to which you want the option button to output its value.

   By default, an Option Button control outputs a number that corresponds to the order it was put on the worksheet. For instance, the first option button you place on the worksheet outputs a number 1, the second outputs a number 2, the third outputs a number 3, and so on. Notice in Figure 11-13 that this particular control outputs to cell A1.

8. (Optional) You can select the 3-D Shading check box if you want the control to have a three-dimensional appearance.
9. Click OK to apply these changes.

10. To add another option button, simply copy the button you created and paste as many option buttons as you need.

    The nice thing about copying and pasting is that all the configurations you made to the original persist in all copies.

    To give your option button a meaningful label, right-click the control, select Edit Text from the menu that appears, and then overwrite the existing text with your own.

Showing Many Views through One Chart

One of the ways you can use option buttons is to feed a single chart with different data, based on the option selected. Figure 11-14 illustrates an example. When each category is selected, the single chart is updated to show the data for that selection.

Now, you could create three separate charts and show them all on your dashboard at the same time. However, using option buttons as an alternative saves valuable real estate by not having to show three separate charts. Plus it’s much easier to troubleshoot, format, and maintain one chart than three.

To create this example, start with three raw datasets — as shown in Figure 11-15 — that contain three categories of data; Income, Expense, and Net. Near the raw data, reserve a cell where the option buttons output their values (cell A8, in this example). This cell contains the ID of the option selected: 1, 2, or 3.

You then create the analysis layer (the staging table) that consists of all formulas, as shown in Figure 11-16. The chart reads from this staging table, allowing you to control what the chart sees. The first cell of the staging table contains the following formula:

=IF($A$8=1,B9,IF($A$8=2,B13,B17))

This formula tells Excel to check the value of cell A8 (the cell where the option buttons output their values). If the value of cell A8 is 1, which represents the value of the Income option, the formula returns the value in the Income dataset (cell B9). If the value of cell A8 is 2, which represents thevalue of the Expense option, the formula returns the value in the Expense dataset (cell B13). If the value of cell A8 is not 1 or 2, the value in cell B17 is returned.

Notice that the formula shown in Figure 11-16 uses absolute references with cell A8. That is, the reference to cell A8 in the formula is prefixed with dollar ($) signs ($A$8). This ensures that the cell references in the formulas don’t shift when they’re copied down and across.

To test that the formula is working fine, you could change the value of cell A8 manually, from 1 to 3. When the formula works, you simply copy the formula across and down to fill the rest of the staging table.

When the setup is created, all that’s left to do is create the chart using the staging table. Again, the major benefits you get from this type of setup are that you can

• Make any formatting changes to one chart and then easily add another dataset by adding another option button.
• Edit your formulas easily.

Using the Combo Box Control

The Combo Box control allows users to select from a drop-down list of predefined options. When an item from the Combo Box control is selected, an action is taken with that selection. To add a combo box to your worksheet, follow these steps:

1. Click the Insert drop-down list under the Developer tab.
2. Select the Combo Box Form control.
3. Click the location in your spreadsheet where you want to place the combo box.
4. After you drop the control onto your spreadsheet, right-click the control and select Format Control from the menu that appears.
5. Click the Control tab to see the configuration options shown in Figure 11-17.
6. In the Input Range setting, identify the range that holds the predefined items you want to present as choices in the combo box.

7. In the Cell Link box, enter the cell to which you want the combo box to output its value.

   A Combo Box control outputs the index number of the selected item. This means that if the second item on the list is selected, the number 2 will be output. If the fifth item on the list is selected, the number 5 will be output. Notice in Figure 11-17 that this particular control outputs to cell E15.

8. In the Drop Down Lines box, enter the number of items you want shown at one time.

   You see in Figure 11-17 that this control is formatted to show 12 items at one time. When users expand the combo box, they’ll see 12 items.

9. (Optional) You can select the 3-D Shading check box if you want the control to have a three-dimensional appearance.
10. Click OK to apply your changes.

Changing Chart Data with a Drop-Down Selector

You can use Combo Box controls to give your users an intuitive way to select data via a drop-down selector. Figure 11-18 shows a thermometer chart that’s controlled by the combo box above it. When a user selects the Southwest region, the chart responds by plotting the data for the selected region.

To create this example, start with the raw dataset shown in Figure 11-19. This dataset contains the data for each region. Near the raw data, reserve a cell where the combo box will output its value (cell M7, in this example). This cell will catch the index number of the combo box entry selected.

You then create the analysis layer (the staging table) that consists of all formulas, as shown in Figure 11-20. The chart reads from this staging table, allowing you to control what the chart sees. The first cell of the staging table contains the following INDEX formula:

=INDEX(P7:P14,$M$7)

The INDEX function converts an index number to a value that can be recognized. An INDEX function requires two arguments to work properly. The first argument is the range of the list you’re working with. The second argument is the index number.

In this example, you’re using the index number from the combo box (in cell M7) and extracting the value from the appropriate range (2012 data in P7:P14). Again, notice the use of the absolute dollar signs ($). This ensures that the cell references in the formulas don’t shift when they’re copied down and across.

Take another look at Figure 11-20 to see what’s happening. The INDEX formula in cell P2 points to the range that contains the 2012 data. It then captures the index number in cell M7 (which traps the output value of the combo box). The index number happens to be 7. So the formula in cell P2 will extract the seventh value from the 2012 data range (in this case, Southwest).

When you copy the formula across, Excel adjusts the formula to extract the seventh value from each year’s data range.

After your INDEX formulas are in place, you have a clean staging table that you can use to create your chart; see Figure 11-21.

Using the List Box Control

The List Box control allows users to select from a list of predefined choices. When an item from the List Box control is selected, an action is taken with that selection. To add a list box to your worksheet, follow these steps:

1. Select the Insert drop-down list under the Developer tab.
2. Select the List Box Form control.
3. Click the location in your spreadsheet where you want to place the list box.
4. After you drop the control onto your worksheet, right-click the control and select Format Control from the menu that appears.
5. Click the Control tab to see the configuration options shown in Figure 11-22.

6. In the Input Range setting, identify the range that holds the predefined items you want to present as choices in the list box.

   As you can see in Figure 11-22, this list box is filled with region selections.

7. In the Cell Link box, enter the cell where you want the list box to output its value.

   By default, a List Box control outputs the index number of the selected item. This means that if the second item on the list is selected, the number 2 will be output. If the fifth item on the list is selected, the number 5 will be output. Notice in Figure 11-22 that this particular control outputs to cell P2. The Selection Type setting allows users to choose more than one selection in the list box. The choices here are Single, Multi, and Extend.

   Always leave this setting on Single because Multi and Extend work only in the VBA environment.

8. (Optional) You can select the 3-D Shading check box if you want the control to have a three-dimensional appearance.
9. Click OK to apply your changes.

Controlling Multiple Charts with One Selector

One of the more useful ways to use a list box is to control multiple charts with one selector. Figure 11-23 illustrates an example of this. As a region selection is made in the list box, all three charts are fed the data for that region, adjusting the charts to correspond with the selection made. Happily, all this is done without VBA code; all it takes is a handful of formulas and a list box.

To create this example, start with three raw datasets — as shown in Figure 11-24 — that contain three categories of data: Revenues, Net Income %, and Gross Margin. Each dataset contains a separate line for each region, including one for All Regions.

You then add a list box that outputs the index number of the selected item to cell P2; see Figure 11-25.

Next, create a staging table that consists of all formulas. In this staging table, you use the Excel’s CHOOSE function to select the correct value from the raw data tables based on the selected region.

In Excel, the CHOOSE function returns a value from a specified list of values based on a specified position number. For instance, the formula CHOOSE(3,"Red", "Yellow", "Green", "Blue") returns Green because Green is the third item in the list of values. The formula CHOOSE(1, "Red", "Yellow", "Green", "Blue") returns Red. See Chapter 2 to get a detailed look at the CHOOSE function.

As you can see in Figure 11-26, the CHOOSE formula retrieves the target position number from cell P2 (the cell where the list box outputs the index number of the selected item) and then matches that position number to the list of cell references given. The cell references come directly from the raw data table.

In the example shown in Figure 11-26, the data that will be returned with this CHOOSE formula is 41767. Why? Because cell P2 contains the number 3, and the third cell reference within the CHOOSE formula is cell B9 — the cell containing January revenues for the North region.

You enter the same type of CHOOSE formula into the Jan column and then copy it across; see Figure 11-27.

To test that your formulas are working, change the value of cell P2 manually by entering 1, 2, 3, 4, or 5. When the formulas work, all that’s left to do is create the charts using the staging table.

If Excel functions such as CHOOSE or INDEX are a bit intimidating for you, don’t worry. You can use various combinations of Form controls and Excel functions in literally hundreds of ways to achieve interactive reporting. The examples I give in this chapter are designed to give you a sense of how you can incorporate Form controls into your dashboards and reports. There are no set rules on which Form controls or Excel functions you need to use in your model.

Start with basic improvements to your dashboard, using controls and formulas you’re comfortable with. Then gradually try to introduce some of the more complex controls and functions. With a little imagination and creativity, you can take the basics in this chapter and customize your own dynamic dashboards.