How to Create Advanced Microsoft Excel Spreadsheets
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:
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!