Excel Inputs

Here’s how to add spinners, check boxes, and other useful controls to your worksheets.

Even if you’ve used Excel for years, you may not know about its form controls, which let you enter worksheet values using elements like sliders, spinners, list boxes, and check boxes. We’ll show you how these controls work, give you handy tips for customizing them, and look at some applications.

The Spinner Control

The spinner lets you alter the value in a cell by clicking an up or down arrow on the control. The techniques for customizing spinners also apply to scroll-bar controls, so you’re learning two controls in one, effectively.

Create a simple savings worksheet by entering the data shown in the table below. Note that row 6 is blank.

Cell Value

A1 Savings calculator

A2 Interest rate p.a.

B2 6%

A3 Number of years

B3 4

A4 Monthly deposit

B4 -200

A5 Initial balance

B5 -100

A7 Amount saved

B7 =FV(B2/12, B3*12, B4, B5, 0)

This worksheet calculates the amount saved at the end of four years if you start with $100 and save $200 a month at an interest rate of 6 percent compounded monthly. Monies paid out are expressed as negative values, so your starting deposit and the monthly payments are negative. This simple example offers plenty of options for using spinners.

Begin by creating a spinner to adjust the number of years shown in cell B3. Choose View Toolbars Forms to display the Forms toolbar and locate the Spinner control. Click the control and place it by using your mouse to draw a rectangle in cell C3. Right-click the control, choose Format Control..., and select the Control tab. Set the Current value to 4, the Maximum value to 20, and the Cell link to B3, then click OK.

Deselect the spinner by clicking away from it in the worksheet; test the spinner by clicking its up and down arrows. As you click, the value in the linked cell (B3) should increase or decrease within the specified range.

Overcoming Limitations

Spinners are limited to returning integers between 0 and 30,000, but you can get a range of real numbers (including negative numbers) by performing some simple arithmetic on the value returned by the spinner. To show how this is done, we’ll add a second spinner to adjust the interest rate in quarter-point increments.

Place the spinner in cell C2 and right-click on it. Choose Format Control..., select the Control tab, and set the Current value to 24, the Maximum value to 40, and the Cell link to E2, then click OK. Now format cell B2 to show percentage with two decimal places, and alter the cell’s contents to read: =E2/400. When you click the new spinner, you’ll see the value in cell B2 change in increments of .25 percent, from 0 to 10 percent. The new formula in B2 takes the value the spinner returns in cell E2 (a number from 0 to 40) and divides it by 400 to produce the displayed value.

You can also create a spinner to give you the negative value that represents your monthly deposit. Add a third spinner, this time in cell C4. Set the Current value to 200, the Maximum value to 30000, the Incremental change to 10, and the Cell link to E4, then click OK. In cell B4, type -E4 and test the spinner. The E4 value will change in increments of 10 within the range 0 to 30000, giving B4 values ranging from 0 to -30000. You can create a range of values by using a different formula or by modifying parameters like the maximum value.

Check Boxes

Check boxes are controls that return either true or false, depending on whether they are selected or cleared. They are useful for managing options that have only two possible settings, such as on/off, true/false, or 1/0. In our example, the last argument in the formula in cell B7 is currently set to 0, which indicates that the payment (the amount saved each month) is due at the end of every month. Changing this value to 1 alters the calculation to show the result when payments are made at the beginning of each month. This is a good use for a check box.

Click the Check Box control and add a check box into cell B6. Right-click this control and choose Format Control... Control tab. From the Value options choose Unchecked. Set the Cell link to E6 and click OK. Alter the formula in cell B7 to read: =FV(B2/12, B3*12, B4, B5, E6)

Change the check box’s text by right-clicking it and choosing Edit Text. In place of the current text, type: Payments made at beginning of period, then adjust the size of the control so the text can be clearly seen. Test the box by clicking in it; the value in cell B7 should change according to whether the check box is selected or not.

Combo Boxes

The final control we’ll look at is the combo box, which lets you choose an entry from a list. Combo boxes are handy when you have a fixed number of choices and can be used to return more than one piece of data from a table of data.

To understand more about the combo box control (and its close relative, the list box), type these values into a blank worksheet.

Cell Value

A2 J. Brown

B2 CA

C2 10%

A3 P. Smith

B3 NY

C3 12%

A4 J. Peters

B4 TX

C4 9%

A7 Sales

B7 Salesperson

C7 State

D7 Commission

A8 200000

C8 =INDEX(A2:C4,E2,2)

D8 =INDEX(A2:C4,E2,3)*A8

Ignore the errors that appear in cells C8 and D8. Click the Combo Box control and draw a combo box in cell B8. Right-click the control, choose Format Control... and the Control tab, set the Input Range to A2:A4, set the Cell link to cell E2, and click OK. You can now choose a salesperson from the combo box. When you do so, the person’s state will appear in cell C8 and the commission amount will appear in cell D8.

The combo box control returns the position of the selected item in the Input range list. The first item—J. Brown, in this example—is in position 1. In our sample worksheet, each INDEX function queries the array A2:C4 and returns the value in the row and column specified by the formula. The row number is the value returned in cell E2 by the combo box. The column number is supplied in the INDEX function itself.

Note that the data in column E is necessary but does not have to be visible. You can hide it by right-clicking the column and choosing Hide.

This covers the basics of using form controls on your worksheets. You’ll find other controls on the Forms toolbar, such as the Option Button, the List Box, and the Scroll Bar. Each of these works in a similar way to one of the controls we’ve looked at. Option buttons work like check boxes, but only one at a time can be selected. Scroll bars work like spinners, but also include sliders. List boxes work like combo boxes, but the full list can be visible. Some controls on the toolbar are grayed; these cannot be used on worksheets. See the sidebar for directions to Microsoft Knowledge Base articles that discuss these controls.