How to Add a Yes or No Box to an Excel Spreadsheet

Microsoft Excel 2010 and 2013 provide two ways to add option buttons and toggles to a worksheet: using form controls or using ActiveX controls. For basic tasks, such as displaying the choice in a cell, either type works, and form controls are easier to work with. If you need to tie in a VBA script, however, you need to use an ActiveX control. To start working with either type of control, enable the Developer tab by right-clicking the ribbon, choosing "Customize the Ribbon" and checking "Developer."

On the Developer tab, click "Insert" and choose the "Option Button" from the Form Control section. While you could also use two check boxes, the option button works best for "Yes" or "No" choices because a user can't choose both options at once. Click to place the first button, edit its name to read "Yes" and then repeat to insert a second button and label it "No." Right-click the "Yes" button, choose "Format Control" and enter a cell's location in the Cell Link field. Repeat the same process with the "No" button, linking it to the same cell. This cell displays the result of the "Yes" or "No" choice -- 1 for "Yes" or 2 for "No."

While option buttons are the best method available for "Yes" and "No" choices when working with form controls, ActiveX controls offer another solution: toggle buttons. On the Developer tab, click "Insert" and pick "Toggle Button." Click and drag to draw a single button, which when pressed, represents a "Yes." Right-click the button and choose "Properties." Edit the "Caption" line to change the button's label and enter a cell location on the "LinkedCell" line. When a user presses the button, the linked cell reads "TRUE." If the button is left unpressed, the cell reads "FALSE."

While you might remember that a "1" or a "TRUE" in a particular cell means a "Yes" choice on your form, the meaning won't come across to any other viewer. Rather than link your option buttons or toggle button to a cell in plain view, link to a cell off to the side and then use a formula to interpret the result as a clear "Yes" or "No." In an empty cell, enter the formula "=IF([cell]=1, "Yes", "No")" excluding the external quotation marks and replacing the linked cell's location for "[cell]." If you used the toggle button method, replace the "1" with the word "TRUE." Whenever a user changes the choice, the cell records the option with a clear "Yes" or "No."

Excel's IF function can do far more than just display a "Yes" or "No" -- insert an IF into a longer formula to adjust the outcome of the spreadsheet depending on the button press. For example, you could have the choice to add a given number to a cell, such as to adjust a price on an invoice depending on the "Yes" or "No." If a total price adds up items in cells A1 through A3, use the formula "=SUM(A1:A3)+IF([cell]=1, 2, 0)" to add $2 to the total when the "Yes" button is selected, as indicated by a 1 in the linked cell. For advanced projects that make use of VBA, reference the toggle box by its name, which appears on the "(Name)" line of the Properties window, to trigger parts of your script based on the button press.