HomeWinBuzzer TipsHow to Insert a Checkbox in Excel

How to Insert a Checkbox in Excel

We show you how to add check boxes in Excel, demonstrate how they function as part of a spreadsheet, and show how they can be used to build a To-Do list.

-

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.

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 Excel 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 topmost 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 that contains your checkbox – clicking on the checkbox will enable and disable it, so this is a more straightforward 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 Excel 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

     

FAQ – Frequently Asked Questions About Checkboxes in Excel

How do I add a checkbox in Excel True or False?

To create a True/False checkbox, first insert a checkbox using the Developer tab as outlined in the tutorial. Then, right-click the checkbox, select “Format Control“, and in the “Control” tab, link the checkbox to a specific cell by clicking in the “Cell link” box and selecting a cell. This cell will now display TRUE when the checkbox is checked and FALSE when it’s unchecked. You can use this linked cell in formulas or conditional formatting to react dynamically to the checkbox state.

How do I use a checkbox to display a specific message in Excel?

After linking a checkbox to a cell, you can display a custom message based on the checkbox’s state by using the IF function. In a cell where you want the message to appear, enter a formula like =IF(linked_cell=TRUE, “Message for checked”, “Message for unchecked”), replacing “linked_cell” with the cell reference that is linked to your checkbox. This formula evaluates the checkbox state and displays the corresponding message.

How do I change the color of a cell based on a checkbox in Excel?

To change a cell’s color based on a checkbox, use conditional formatting linked to the checkbox’s cell. First, select the cell(s) you want to format. Go to the Home tab, click on “Conditional Formatting“, then “New Rule“, and choose “Use a formula to determine which cells to format“. Enter a formula like =$A$1=TRUE, assuming A1 is the cell linked to your checkbox. Click “Format“, choose your desired formatting options, and press OK. The cell will change color based on the checkbox’s state.

How do I create a dynamic drop-down list based on a checkbox selection in Excel?

To create a dynamic drop-down list that changes based on a checkbox, you’ll first need to define named ranges that correspond to the different options you want to display in your drop-down list. Then, in the cell where you want the drop-down list, go to Data > Data Validation, select “List” in the “Allow” box, and in the “Source” box, enter a formula like =INDIRECT(IF(A1=TRUE, “NamedRange1”, “NamedRange2”)), where A1 is the cell linked to your checkbox, and “NamedRange1” and “NamedRange2” are the names of your defined ranges. This setup allows the drop-down list to dynamically update based on the checkbox’s state.

How do I make checkboxes automatically check based on cell value in Excel?

Automating checkbox states based on cell values requires VBA. Press Alt + F11 to open the VBA Editor, insert a new module, and write a script that checks the value of a specific cell and updates the checkbox state accordingly. For example, a simple VBA code could loop through checkboxes in a worksheet and check or uncheck them based on the values in linked cells. Remember to trigger this script appropriately, such as on workbook open or sheet change, depending on your needs.

How do I copy checkboxes to multiple cells without changing the linked cell?

When copying and pasting a checkbox, Excel will retain the original linked cell reference. To assign unique cell links to each pasted checkbox, you must manually adjust them. After pasting, right-click each checkbox, select “Format Control,” navigate to the “Control” tab, and set a new “Cell link” for each. This ensures each checkbox interacts with a unique cell, allowing for individual tracking.

How do I align checkboxes within cells in Excel for a cleaner look?

For precise alignment, select your checkboxes (hold down the Ctrl key to select multiple) and then use the “Align” and “Distribute” tools found under the Drawing Tools Format tab (this tab appears when form controls are selected). These tools offer options like “Align Left,” “Align Center,” “Distribute Horizontally,” and “Distribute Vertically” to standardize the positioning of your checkboxes, making your worksheet look more organized.

Can I control the size of a checkbox in Excel?

Yes, the size of a checkbox can be adjusted manually for better visibility or to fit within a specific area of your worksheet. Click on the checkbox to select it, and you’ll see small squares or ‘handles’ at the corners and edges. Click and drag these handles to resize the checkbox. Be mindful of maintaining enough size for easy interaction while ensuring it doesn’t obstruct other content.

How do I group multiple checkboxes in Excel for unified behavior?

Grouping checkboxes allows you to move or format them as a single unit, which can be particularly useful for organizing your worksheet. To group checkboxes, hold down the Ctrl key and click each checkbox you want to include in the group. Then, with all desired checkboxes selected, right-click on one of them and choose “Group” > “Group” from the context menu. Now, any movement or formatting changes applied to one will affect the entire group.

How do I create a to-do list in Excel with checkboxes and conditional formatting that highlights the entire row?

After inserting checkboxes and linking them to cells, select the rows you want to format and go to the Home tab, click “Conditional Formatting,” then “New Rule.” Choose “Use a formula to determine which cells to format” and enter a formula like =$A1=TRUE, where A1 is the cell linked to the first checkbox in your list. Click “Format,” set your desired formatting options (like fill color), and click OK. This rule will apply the formatting to the entire row based on the linked cell’s value, visually indicating completed tasks.

How do I export data from Excel with checkboxes to another format while retaining the checkbox state?

When exporting Excel data that includes checkboxes, the visual checkboxes themselves won’t be exported. Instead, ensure each checkbox is linked to a cell that reflects its state (TRUE for checked, FALSE for unchecked). When exporting, include these linked cells in your data range. In your exported format, you can use these TRUE/FALSE values to interpret the checkbox states, applying conditional formatting or data validation in the new environment if needed.

How do I use checkboxes to filter data in Excel?

To filter data based on checkboxes, link each checkbox to a specific cell and use those cells as criteria in your filtering logic. For example, you can set up an Advanced Filter that references the linked cells, creating a formula that includes conditions like =AND($A$1=TRUE, your_other_conditions), where A1 is the cell linked to a checkbox. This setup allows you to dynamically filter data rows based on the states of one or more checkboxes.

How do I create a progress bar in Excel that updates based on checkboxes?

To create a progress bar linked to checkboxes, first, ensure each checkbox is linked to a cell. Then, in a cell, use a formula like =COUNTIF(A1:A10, TRUE)/COUNTA(A1:A10) to calculate the percentage of checked boxes, where A1:A10 are the cells linked to your checkboxes. Insert a shape (like a rectangle) to act as your progress bar. Use a simple VBA script to adjust the width of the shape based on the calculated percentage, effectively creating a dynamic progress bar that updates as checkboxes are checked or unchecked.

How do I reset all checkboxes in Excel with a single action?

To reset all checkboxes simultaneously, you can use a VBA script. Open the VBA Editor (Alt + F11), insert a new module, and write a script that loops through all form controls on a sheet, identifying checkboxes and setting their value to FALSE (unchecked). Assign this script to a button or a shortcut for easy access. This method provides a quick way to reset your form controls without manually clicking each checkbox.

How do I prevent accidental modification of checkboxes in Excel?

To protect checkboxes from accidental changes, you can lock them by selecting the checkboxes, right-clicking to access the “Format Control” dialog, and then under the “Protection” tab, ensuring “Locked” is checked. Next, protect the entire worksheet by going to the Review tab and selecting “Protect Sheet“. You can specify a password and select what actions are allowed (like selecting locked and unlocked cells). This prevents any modifications to the checkboxes unless the sheet is unprotected.

Related: How to Create, Use and Edit a Drop Down List in Excel

Creating a drop down list in Excel is an essential skill for anyone who regularly works with . This feature enhances data entry efficiency, ensures consistency, and minimizes errors, making it invaluable in various applications such as data analysis, financial reporting, and inventory management. In our other guide we show you how to use drop down lists in Excel – creating, applying, updating and extending them.
 
Featured - How to add drop down list in Excel

Related: 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

Related: 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.
 

Last Updated on April 22, 2024 9:56 am CEST

Ross Clifton
Ross Clifton
Ross has been an avid gamer and technology geek ever since a handheld calculator replaced his first slide rule. For almost as long, he has also been a fan of helping people get the most out of their computers. Initially helping friends and family with 8-bit computers plugged into their TVs, now he shares his insights into how to make your Microsoft applications do what you need them to.
Table of Contents: