Featured - How to insert a checkbox in Excel

A checkbox is a simple control that I’m sure everybody will have encountered online, often as part of a cookie dialog or where you’ll tell a site to remember you being logged in. Checkboxes in excel are much the same thing, but you may not be aware of how useful they can be.

These are members of a family of control elements called Form Controls and they are one of the ways in which you are able to easily interact with a website or an application on your computer. You may know of them as check boxes, checkmark boxes, or even just tick boxes, but they’re boxes that you can click on to add or remove a check symbol or tick in order to show that something has been selected or deselected.

Advertisement

Reasons to insert a checkbox in Excel

Although they might not sound like much, an Excel check box can allow you to enter simple yes/no or on/off data with a click instead of needing to select a cell and enter your data using the keyboard. This makes it easy to set up worksheets to track things like goals, assignments, or even as we will show you in this article, a shopping list.

You may not have thought to add a checkbox in Excel before, but these are a simple and effective way to add functionality to your worksheets and make binary data easy to set up and read.

How to activate the Check Box-Option in the Ribbon

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

    Windows 11 - Excel - File

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

    Windows 11 - Excel - File - More - Options

  3. Select “Customize Ribbon”

    Windows 11 - Excel - File - More - Options - 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.

    Windows 11 - Excel - Right-click - Customize Ribbon

  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.

    Windows 11 - Excel - Customize Ribbon - Main Tabs - Check Developer - Accept

  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.

    Windows 11 - Excel - Customize Ribbon - Developer - Result

How to add checkboxes to cells

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”

    Windows 11 - Excel - Developer - Insert - Checkbox

  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.

    Windows 11 - Excel - Developer - Insert - Checkbox - Place

  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.

    Windows 11 - Excel - Developer - Insert - Checkbox - Drag

  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.

    Windows 11 - Excel - Checkbox - Edit Text

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

    Windows 11 - Excel - Checkbox - Edit Text - Del Text

  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.

    Windows 11 - Excel - Checkbox - Edit Text - Del Text - Result

  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.

    Windows 11 - Excel - Checkbox - Copy Checkbox

  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.

    Windows 11 - Excel - Checkbox - Copy Checkbox - Result

How to link checkboxes to cells

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”

    Windows 11 - Excel - Checkbox - 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.

    Windows 11 - Excel - Checkbox - Format Control - Control - Cell Link - Accept

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

    Windows 11 - Excel - Checkbox - Format Control - Control - Cell Link - Result

How to use connected checkboxes and formulas

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)

     

    Windows 11 - Excel - Total Items

  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)

    Windows 11 - Excel - Total Items 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)

    Windows 11 - Excel - Blank Items

How to create a To-Do list with check boxes and conditional formatting

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.

    Windows 11 - Excel - ToDo list - Home - Styles - Conditional Formatting - New Rule

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

    Windows 11 - Excel - ToDo list - New Formatting Rule - Use Formula - Format

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

    Windows 11 - Excel - ToDo list - New Formatting Rule - Use Formula - Format - Accept

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

    Windows 11 - Excel - ToDo list - New Formatting Rule - Use Formula - Format - Accept

  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.

    Windows 11 - Excel - ToDo list - New Formatting Rule - Use Formula - Format - Result

  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”)

    Windows 11 - Excel - ToDo list - IF Formula

  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.

    Windows 11 - Excel - ToDo list - IF Formula - Copy Formula

  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.

    Windows 11 - Excel - ToDo list - IF Formula - Copy Formula - Result

     

Extra: How to Lock Cells in Excel to Protect from Editing

Even if you aren’t sharing a spreadsheet, learning how to lock cells in Excel can save you a lot of trouble. No matter how proficient you are with the software, it’s easy enough for you or a pet to lean on a key and break your formulas.

Featured - How to lock cells in excel

Extra: How to Find Duplicates in Excel and Remove Their Rows

Creating duplicate content is so common that Microsoft’s spreadsheet software has an in-built Remove Duplicates tool. This isn’t always the best way to delete duplicate rows in Excel, however – sometimes the find and replace or advanced filtering options will be more efficient.

Advertisement