Use the Option Button Control for Your Excel Dashboards - dummies

Use the Option Button Control for Your Excel Dashboards

By Michael Alexander

Option buttons allow users to toggle through several options on an Excel dashboard or report one at a time. The idea is to have two or more option buttons in a group. Then selecting one option button automatically deselects the others. To add option buttons to your worksheet, follow these steps:

  1. Click the Insert drop-down list under the Developer tab.

  2. Select the Option Button Form control.

  3. Click the location in your spreadsheet where you want to place your option button.

  4. After you drop the control onto your spreadsheet, right-click the control and select Format Control.

  5. Click the Control tab to see the configuration options shown in this figure.


  6. First, select the state in which the option button should open.

    The default selection (Unchecked) typically works for most scenarios, so it’s rare that you’d have to change this selection.

  7. In the Cell Link box, enter the cell to which you want the option button to output its value.

    By default, an Option Button control outputs a number that corresponds to the order it was put onto the worksheet. For instance, the first option button you place on your worksheet outputs a number 1, the second outputs a number 2, the third outputs a number 3, and so on. Notice in the previous figure that this particular control outputs to cell A1.

  8. (Optional) You can select the 3-D Shading check box if you want the control to have a three-dimensional appearance.

  9. Click OK to apply your changes.

  10. To add another option button, simply copy the button you created and paste as many option buttons as you need.

    The nice thing about copying and pasting is that all the configurations you made to the original persist in all the copies.

    To give your option button a meaningful label, right-click the control, select Edit Text, and then overwrite the existing text with your own.