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

Using and Grouping Option Buttons

Option buttons operate as a group, but the spreadsheet user can select only one at a time. If you need to create multiple sets of buttons to manage different settings on a worksheet, you can place each set inside a group box to isolate it from the other sets.

To see a group of option buttons at work, choose Developer > Insert > Group Box, and then drag a group box onto the worksheet. Click Option Button, and drag to add an option button inside the group box. Repeat to add two more option buttons inside the group box.

Right-click one of the option button controls, and click Format Control. You can configure an option button to be checked or unchecked. Since only one can be checked at a time, configuring any of the buttons as checked will automatically make all of the others unchecked. In addition, whatever cell you set as the ‘Cell link’ for one option button in the group box will be the same for all of the option buttons. To change the label for an option button, right-click the option button and choose Edit Text.

Option buttons return a value in the ‘Cell link’ cell according to the order in which you created them, so the first one will return a value of 1 when clicked, the second one will return the value 2, the third the value 3, and so on. You can use the values you get from the option buttons to perform an action. For example, you might use option buttons with text reading ‘Overnight’, ‘2-Day’, and ‘Ground’ on an invoice for a user to select how a delivery should be shipped. You could then write an IF function to make a calculation based on the user’s selection.

So, if you added the option buttons in the order ‘Overnight’, ‘2-Day’, and ‘Ground’, and if you made their ‘Cell link’ cell A1, you could type the following formula in any cell to return a shipping cost of $45 if Overnight is selected, $35 for 2-Day, and $20 for Ground:

=IF(A1=1,45,IF(A1=2,35,20))

Using a Checkbox to Select an Item

Each checkbox control is independent of any other checkbox controls on the worksheet, so the user can select or deselect a checkbox in isolation, without initiating a change to any other checkbox. You can set up a checkbox control to be checked, unchecked, or ‘Mixed’ (a gray fill that disappears as soon as the user clicks the checkbox the first time). In practice, you’ll probably need to use only the Checked and Unchecked settings. Each checkbox should be linked to its own ‘Cell link’ cell. It will return a value of False if it is unchecked, True if it is checked, and #N/A if you set it up as Mixed.

You can test the value that a checkbox returns by using an IF function to make a calculation depending on its state. An invoice, for example, might contain a checkbox with the label ‘Include State Tax’. If you link the checkbox to cell A7, and if you type the formula =IF(A7,InvoiceTotal*0.085,0) into any cell in the worksheet, that cell will contain the value from the cell named InvoiceTotal multiplied by 8.5 percent (to calculate state sales tax) if the spreadsheet user checks the checkbox, and it will contain the value of 0 if the user leaves it unchecked.

You’re on Your Way to Becoming an Excel Power User

Microsoft Excel’s Form Controls provide a flexible set of tools that you can harness to make data entry easier and more interactive. These controls let a user click to select items rather than having to type the value into a cell manually. They can help reduce errors, and speed up routine data entry.

Would you be interested in seeing even more Excel power-user tips? Hit the comments and let us know!