Tgood37 asked the Answer Line forum how to add checkboxes to Excel spreadsheets, and how to make sure that checking a box will have an effect.
If you’re setting up a worksheet only for yourself, you can simply leave an empty cell for this purpose. To check it, just type in an x or any other character. Then use a formula with the =isblank() function to make the contents of that cell affect the rest of the spreadsheet.
But you might want something more mouse-friendly–especially if you’re designing a spreadsheet for other people.
I’ve tested the technique below on Excel 2007, 2010, and 2013. I’m not sure about earlier versions.
[Email your tech questions to answer@pcworld.com or post them on the PCW Answer Line forum.]
You can insert checkboxes on Excel’s Developer tab. Unfortunately, that tab is hidden by default.
To make it visible in Excel 2010 or 2013, click the File tab and select Options. Click Customize Ribbon. You’ll see two lists. Make sure that the one on the right is titled Main Tabs. Check Developer.
If you’re using Excel 2007, click the Office orb in the upper-left corner. Then click the Excel Options button near the lower-right corner of the resulting pop-up box. Select Popular in the left pane, then check Show Developer tab in the Ribbon.
Once the Developer tab is visible, these instructions work for all three versions:
To insert a checkbox, click the Developer tab, then click the Insert icon in the Controls section. From the resulting pop-up menu, select the checkbox icon in the Form Controls section.
The mouse pointer will turn into a plus sign. Click where you want the checkbox to appear. You can drag and drop it elsewhere later.
Now you need to set the checkbox up. Right-click it and select Format Control. You’ll find various options here, but these two are particularly important:
Click the Control tab, go to the Cell link field, and enter a cell address (or click the icon to the right and select one). The contents of that cell will change when the checkbox is checked or unchecked, displaying either True or False, and thus allowing you to create formulas that react to the checkbox’s status.
When you’re done with the Control tab, click the Protection tab. Uncheck Lock text, then click OK to close the dialog box. This will allow you to edit the checkbox text by clicking on it. Once you’ve changed it, you can return to the Format Control dialog box and recheck Lock text.
Read the original forum discussion.