How to Create Advanced Microsoft Excel Spreadsheets

Looking to become an Excel power user? Excel has a number of features that will make it easier for you and your colleagues to enter data into your spreadsheets. If you’re developing a spreadsheet that you'll use over and over again, inserting a spin button or scrollbar will allow you to choose from a predefined range of values using your mouse, instead of typing numbers in with the keyboard. Or, if you wish to limit a spreadsheet user to selecting from a few preset choices, a set of option buttons will do.

Read on, and I’ll show you how to accomplish these and several other cool Excel tricks.

Locating the Tools You'll Need to Use

The key to utilizing these features lies in Excel’s Form Controls, which are accessible only from the Developer tab--and that tab isn't visible in Excel by default. To display the Developer tab, choose File > Options > Customize Ribbon. Now look in the right-hand panel, check the Developer checkbox, and click OK.

Click the Developer tab on the Ribbon, and select Insert (within the Developer tab--don’t click the Ribbon’s Insert tab) to view the Form Controls collection. Hover your mouse over each icon, and a text box will display its name and function. Note that the ActiveX Controls panel uses similar--in some cases, identical--icons, but they perform very different tasks. Be sure to click only Form Controls items for the purposes of following along with this article.

You can add Form Controls items to your worksheets to introduce click-and-drag functionality. An option button, for example, can run a macro when the spreadsheet user clicks it. The combo box and list box controls let the user choose items from a list, and the user can click checkboxes and option buttons to select the items they represent. The user can drag or click spin buttons and scrollbars to increase or decrease a value, too. In this article, I'll look at how to use the spin button, scrollbar, option button, and checkbox controls.

Starting With the Spin Button Form Control

One of the easiest controls to understand is the spin button, which lets the spreadsheet user increase or decrease the value in a cell by clicking on it. To see how it works, click Developer > Insert > Spin Button, and then drag (hold your left mouse button down) to create a spin button within the worksheet. The spin button control has large arrows at the top and bottom of a box.

Now, right-click the spin button, choose Format Control, and click the Control tab to configure the control. The 'Current value' is the starting value for the control. The 'Minimum value' and 'Maximum value' are, respectively, the smallest and largest values the control can provide (limited to between 0 and 30,000). 'Incremental change' refers to the amount by which the value changes each time the spreadsheet user clicks an arrow. The 'Cell link' is the cell in which the spin button will place its value.

Type the following values:

• Current value: 50
• Minimum value: 0
• Maximum value: 400
• Incremental change: 25
• Cell link: A1

Click OK, and then click outside the spin button to deselect it.

To test the spin button, use its arrows: Click its up arrow to increment the value in cell A1 by 25, and click its down arrow to decrement the value by 25. Note that the value can never increase beyond 400, which is the maximum value you set for it. By the same token, it cannot decrease below its minimum value of 0.

Exploring the Additional Functionality of Scrollbars

The scrollbar works in a similar way to the spin button. Click Developer > Insert > Scroll Bar to select this control. Now drag vertically or horizontally on the worksheet to create a vertical or horizontal scrollbar. Both work the same way; they just have different orientations. Now, right-click the scrollbar, choose Format Control, and click the Control tab to set its options.

The scrollbar has all the same settings as the spin button does, but it also has a 'Page change' value, the value by which the number in the 'Cell link' cell changes when the user clicks the scrollbar. The 'Incremental change' refers to the value that is added or removed when the user clicks the arrow at either end of the scrollbar. Of course, the scrollbar also has a slider that the user can drag to change the value; this comes in handy when the user needs to make large value changes.

Both the scrollbar and spin button can produce a value only between 0 and 30,000. This might seem like a severe limitation, but you can use a formula to obtain a much larger range of values. For example, create a scrollbar, right-click it, and choose Format Control > Control.

Type the following values for its settings:

• Current value: 0
• Minimum value: 0
• Maximum value: 100
• Incremental change: 1
• Page change: 10
• Cell link A3

Click OK and then click outside the scrollbar to deselect it.

Next, click in cell B3 and type this formula:

=A3-50

Now when the user clicks the scrollbar, the value in cell A3 will always be in the range 0 to 100. The cell next to it, however--cell B3--will display a value between -50 and +50 because of the formula it contains. If you hide the value in cell A3 (by setting it to show white text on a white background, for example), the user will see only the scrollbar changing the value in cell B3.

Next Page: Using and Grouping Option Buttons