How to Insert a Checkbox in Excel

As checkboxes (and other form controls) aren't displayed on the ribbon menu by default, you may wish to add them to make them easily accessible for you. Once added to the ribbon, you will be able to add a checkbox to your worksheet with only three clicks.

You'll find checkboxes under Form Control under the Developer tab, which you will enable in the Customize Ribbon options.

1. Open the “File” tab

   

2. Select the “More…” menu and choose “Options”
   
   

   

3. Select “Customize Ribbon”
   
   

   

4. (Alternative method) Right-click on the Ribbon menu, and select “Customize Ribbon”

   This will also open the Customize Ribbon dialog window and can be used instead of going through the File tab.

   

5. Select “Main Tabs” and enable the “Developer” tab

   Make sure that the “Customize the Ribbon” dropdown menu has “Main Tabs” enabled, and then click in the checkbox next to “Developer” to activate that tab on your ribbon menu. Once this has been done, click on the “Okay” button at the bottom of the dialog window.

   

6. Check that the “Developer” tab is now being displayed on your ribbon menu
   
   With the new tab now showing on your ribbon menu, we can move on to the next section where we will show you how to add a checkbox in Excel.

   

The steps required to add a checkbox to your worksheet are fairly straightforward. The only aspect you may find a little unintuitive is the process of positioning it correctly, but we will touch on that later in this section.

1. Click on the “Developer” tab, then select the “Insert” icon, and click on the checkbox icon under “Form Controls”

   

2. Click where you wish to add the checkbox

   Don't worry about placing it precisely yet, as you will be adjusting its position in the next step.

   

3. Click and drag to position the checkbox precisely

   Move your cursor over the edge of the selection box for your checkbox, and when it changes to the move pointer you can click and drag the element to position it accurately on your sheet.

   

4. Right-click on the checkbox, and select “Edit Text”

   For this example, you will be removing the “Check Box 1” text completely but you are able to change what is displayed alongside the control if you wish.

   

5. Highlight the “Check Box 1” text and delete it

   

6. Select “Design Mode”

   Note: If the Design Mode option is unavailable and cannot be selected, you may need to open the Properties window immediately to the right of the Design Mode icon, which is the top-most of the three icons, to enable it.

   

7. Duplicate your checkbox

   Using your arrow or cursor keys, select the cell which contains your checkbox – clicking on the checkbox will enable and disable it, so this is a simpler way to select it. Once selected, move your cursor over the bottom-right corner of the cell until the cursor changes to a bold plus sign (+), then click and drag down to select the empty cells below the highlighted checkbox. When you release the mouse button, your checkbox will be copied into all of the cells you've selected.

   

8. You now have checkboxes added to your sheet, ready to be used

   As you can see, you now have checkmarks on your sheet ready to be used, but you'll also see that switching them on and off doesn't make any changes to the content of the sheet itself. In the next section, we will show you how to link your checkboxes to cells on your sheet in order to capture the state of individual boxes and make that information accessible to your formulas.

   

Now that you know how to add a checkbox, the next step is showing you how to connect it to a cell in order to make the control interactive, and allow the state of the checkbox to be stored on your sheet.

1. Right-click on the checkbox you wish to link, and select “Format Control”

   

2. Select the cell to be connected to the checkbox

   Make sure the “Control” tab is selected on the “Format Control” dialog window, click in the “Cell link” field, then select the cell you wish to connect to this checkbox. Once selected, you will see the cell location shown in the field, and you can click on “Ok” to confirm the link.

   Note: you may need to click outside of the selection box of the checkbox – in this example clicking on the right-hand area of the cell – as its selection box may block your mouse click.

   

3. Repeat for each checkbox, then test by clicking the checkbox on and off

   

Now that the checkboxes have been linked to cells on the sheet, you will set up two formulas that will use the contents of those cells to display useful information for you.

1. Use the COUNTA function to show a count of the number of items in a selection
   
   This formula will display the total number of cells in the selected range. Begin by selecting the cell where you wish to display the result, then in the formula bar enter this: =COUNTA(C3:C10)
   
    

   

2. Use the COUNTIF function to display the number of items in your selection that match a specific criteria

   Unlike COUNTA which counts all cells in the range selected, COUNTIF will allow you to count only the cells which match the criteria set up in your formula – in this case, the cells in the selection which have the value of TRUE. Locate the cell connected to the last checkbox (E10 in this example), and select the cell directly below it and then enter this into the formula bar: =COUNTIF(E3:E10,TRUE)

   

3. Use COUNTIFS to count two or more ranges of cells that are each subject to different criteria

   COUNTIFS will allow you to count the contents of cells in more than one selection, with each selection having its own criteria. In this example, the formula will check that the cell in column C is not blank or empty and that the accompanying value in column E is equal to TRUE; it then displays the total count of cells where these two conditions are true. Select the cell where you wish to display this result, then in the formula bar enter this: =COUNTIFS(C3:C10, "<>",E3:E10,TRUE)

   

In this last section, you'll be shown how to create a To-Do list using conditional formatting to present the information from your sheet in a very easy-to-read manner. In this example, we will set up a list showing which vegetables need to be bought.

1. Select the list of items and open the “Conditional Formatting” dialog window, then click on “New Rule…”

   Note: This menu item is located on the “Home” tab, so be sure to click on that once you're selected all the list items.

   

2. Select the “Use a formula” rule, then enter the formula which will trigger the format change and click on “Format”

   

3. Change the color and enable “Strikethrough”, then click on “Ok”

   

4. Click on “Ok” to save your formatting rule

   

5. Confirm that the text is now being displayed correctly.

   If your conditional formatting has been set up correctly, items that have the check box enabled will be displayed with strikethrough applied to the text of the item name, and the font color changed to a medium gray. Disabling a check box will revert the font color and style to normal.

   

6. Add a new formula to display a message dependent on the checkbox status

   Select the cell to the right of the top check box, and enter this into the formula bar: =IF(G3=TRUE,”ok”,”to buy”)

   

7. Select this cell and drag it to autofill the formula into the cells below

   Click on the cell to select it, then move your cursor over the bottom-right corner until the cursor changes to a bold cross (+) and click and drag down to select the empty cells below. When you release the mouse button, the formula will be copied into all of the cells you've selected.

   

8. Check that your To-Do list is working as intended

   Your list should now be complete, and enabling and disabling individual checkboxes will change the font color and formatting in the cell to the left-hand side of each, and will display a simple message in the cell to the right indicating whether you need to buy more of each.