Excel Tip of the Week: Check Box

Today’s topic is how to create a check box.

Check boxes are technically an advanced Excel feature and somewhat hidden in Excel. But once you know how to create one, it is relatively simple to use.

Before we jump into creating check boxes, the first step is to enable the “Developer” tab in the Excel ribbon. By default this tab is hidden, and it is where you’ll find the check boxes among many other useful advanced Excel features. Here is how to enable the Developer Tab:

Windows:

1. Click the File tab.
2. Click Options.
3. In the categories pane, click Customize Ribbon.
4. In the list of main tabs, select Developer.
5. Click OK to close the Options dialog box.

Mac:

1. Click on the ‘Excel’ menu in the menu bar
2. Click on Preferences
3. Click on the Ribbon icon
4. Select the Developer Tab
5. Click OK

Check boxes give you the ability to toggle between two scenarios. For example, I’ve used them to toggle between a simple or detailed assumption. Or you could use it to turn things on and off. Let’s look at an example. First download this spreadsheet.

Take a look at the example already created. I have a list of products and their costs. Using the check boxes (and a simple IF formula) the spreadsheet is more dynamic and able to calculate the total costs based on your preferences. Try clicking some of the check boxes and watch how the spreadsheet changes.

One thing to note is that normally the “TRUE/FALSE” column would be hidden or out of the way, but I left it in this spreadsheet for teaching purposes. Now it is time for you to create your own check box.

1. From the Developer Tab click on Insert and then click on the Check Box icon (Keyboard Shortcut Alt → L → I → H). For a Mac the check Box is just right there as its own icon, no need to click “Insert.”
2. You should notice your mouse cursor change to a cross.
3. Somewhere near cell B11 click and drag your mouse to make a box. Make it about the size you want the check box to be.
4. You should now see a new check box that is labeled “Check Box” followed by a number.
5. Right click on the check box and select “Format Control”
6. Make sure you are on the “Control” tab. Using the “Cell Link” box select D11 as the cell. (You can type it or use the little button to the right of the “Cell Link” box.) Then click OK.
7. Right click the check box again, and then double click (with the left mouse button). This will allow you to edit the text. Type in “Headphones”
8. Give the Headphones a price of $50 in cell C11.
9. Excel might fill in cell E11 for you, but if not just copy the formula from above.
10. Update the formula in cell H4 to include the headphones. And you’re done. Good job. Here is a screen shot of the final product:

I think the hardest part of using check boxes is trying to line them up in the right spot. The best tip I can give you for that is to move it close to where you want it with the mouse and then use the arrow keys to align it just right.