June 02, 2012, 7:12 AM — 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