How do I create a checkbox in a drop

How to Insert Checkbox in Excel (to Create Interactive Lists and Charts)

• -- By Sumit Bansal

FREE EXCEL TIPS EBOOK - Click here to get your copy

Watch Video – How to Insert and Use a Checkbox in Excel

In Excel, a checkbox is an interactive tool that can be used to select or deselect an option. You must have seen it in many web form available online.

You can use a checkbox in Excel to create interactive checklists, dynamic charts, and dashboards.

This Excel tutorial covers the following topics:

• How to Get the Developer Tab in Excel Ribbon.
• How to Insert a Checkbox in Excel.
• Examples of Using Checkboxes in Excel.
• How to Insert Multiple Checkboxes in Excel.
• How to Delete a Checkbox in Excel.
• How to Fix the Position of a Checkbox in Excel.
• Caption Name Vs. Backend Name

To insert a checkbox in Excel, you first need to have the Developer tab enabled in your workbook.

Can’t see the developer tab? 

Don’t worry and keep reading!

Get the Developer Tab in Excel Ribbon

The first step in inserting a checkbox in Excel is to have the developer tab visible in the ribbons area. The developer tab contains the checkbox control that we need to use to insert a checkbox in Excel.

Below are the steps for getting the developer tab in the Excel ribbon.

• Right click on any of the existing tabs in the Excel ribbon and select Customize the Ribbon. It opens the Excel Options dialog box.
• In the Excel Options dialog box, you will have the Customize the Ribbon options. On the right, within the Main Tabs pane, check the Developer option.
• Click OK. This will make the developer tab appear as one of the tabs in the ribbon.

Now with the Developer tab visible, you get access to a variety of interactive controls.

How to Insert a Checkbox in Excel

Here are the steps to insert a checkbox in Excel:

1. Go to Developer Tab –> Controls –> Insert –> Form Controls –> Check Box.
   
2. Click anywhere in the worksheet, and it will insert a checkbox (as shown below).
3. Now to need to link the checkbox to a cell in Excel. To do this, right-click on the checkbox and select Format Control.
4. In the Format Control dialog box, in the Control tab, make the following changes:
   • Value: Checked (this makes sure that the checkbox is checked by default when you open the workbook)
   • Cell Link: $A$1 (this is the cell linked to the checkbox). You can manually enter this or select the cell to get the reference.
5. Click OK.

Now your checkbox is linked to cell A1, and when you check the checkbox, it will show TRUE in cell A1, and when you uncheck it, it will show FALSE.

Examples of Using a Checkbox in Excel

Here are a couple of examples where you can use a checkbox in Excel.

Creating an Interactive To-Do List in Excel

Below is an example of a To-Do list that uses checkboxes to mark the task as complete.

A couple of things are happening in the example above:

• As soon as you check the checkbox for an item/task, the status changes to Done (from To be Done), the cell gets a green shade, and the text gets a strikethrough format.
• The value of the cell link for that checkbox changes from FALSE to TRUE.
• The ‘Task Completed’ and ‘% of Task Completed’ numbers (in cell H3 and H4) change based on how many tasks have been marked as completed.

Here is how to make this:

• Have the activities listed in cell A2:A7.
• Insert checkboxes and place it in cell B2:B7.
• Link these checkboxes to cell E2:E7. There is no way to link all the checkboxes at one go. You’ll have to manually link each checkbox one by one.
• In cell C2, enter the following formula: =IF(E2,”Done”,”To Be Done”) and drag for all the cells (C2:C7).
  • In cell C2:C7, apply conditional formatting to give the cell a green background color and strikethrough format when the value in the cell is Done.
• In cell H3, use the following formula: =COUNTIF($E$2:$E$7,TRUE)
  • This will count the total numbers of tasks that have been marked as completed.
• In cell H4, use the following formula: =COUNTIF($E$2:$E$7,TRUE)/COUNTIF($E$2:$E$7,”<>”)
  • This will show the percentage of tasks completed.

Click here to download the checklist.

Creating a Dynamic Chart in Excel

You can use an Excel checkbox to create a dynamic chart as shown below:

In this case, the checkbox above the chart is linked to cell C7 and C8.

If you check the checkbox for 2013, the value of cell C7 becomes TRUE. Similarly, if you check the checkbox in for 2014, the value of cell C8 becomes TRUE.

The data used in creating this chart is in C11 to F13. The data for 2013 and 2014 is dependent on the linked cell (C7 and C8). If the value in cell C7 is TRUE, you see the values in C11:F11, else you see the #N/A error. Same is the case with data for 2014.

Now based on which checkbox is checked, that data is shown as a line in the chart.

Click here to download the dynamic chart template.

Inserting Multiple Checkboxes in Excel

There are a couple of ways you can insert multiple checkboxes in the same worksheet.

#1 Inserting a Checkbox using the Developer Tab

To insert more than one checkbox, go to the Developer Tab –> Controls –> Insert –> Form Controls –> Check Box.

Now when you click anywhere in the worksheet, it will insert a new checkbox.

You can repeat the same process to insert multiple checkboxes in Excel.

Note:

• The checkbox inserted this way are not linked to any cell. You need to manually link all the checkboxes. 
• The checkbox would have different caption names, such as Check Box 1 and Check Box 2, and so on.

#2 Copy Pasting the Checkbox

Select an existing checkbox, copy it and paste it. You can also use the keyboard shortcut (Control + D).

Note: 

• The copied checkboxes are linked to the same cell as that of the original checkbox. You need to manually change the cell link for each checkbox.
• The caption names of all the copied checkboxes are the same. However, the backend name would be different (as these are separate objects).

#3 Drag and Fill Cells with Checkbox

If you have a checkbox in a cell in Excel and you drag all fill handle down, it will create copies of the checkbox. Something as shown below:

Note:

• The caption names of all the new checkboxes are the same. However, the backend name would be different (as these are separate objects).
• All these checkboxes would be linked to the same cell (if you linked the first one). You need to manually change the link of all these one by one.

Deleting the Checkbox in Excel

You can easily delete a single checkbox by selecting it and pressing the delete key. To select a checkbox, you need to hold the Control key and the press the left button of the mouse.

If you want to delete multiple checkboxes:

• Hold the Control key and select all the ones that you want to delete.
• Press the Delete key.

If you have many checkboxes scattered in your worksheet, here is a way to get a list of all the checkbox and delete at one go:

• Go to Home –> Editing –> Find & Select –> Selection Pane.
  • This will open a Selection Pane that will list all the objects on that worksheet (including checkboxes, shapes, and charts).
• Select all the checkboxes you want to delete (to select multiple checkboxes, hold the control key while selecting) and hit the delete key.
  • Note that the names of the checkboxes here are the backend names and not the caption names.

Note: The selection pane displays all the objects of the active worksheet only.

How Fix the Position of a Checkbox in Excel

One common issue with using shapes and objects in Excel is that when you resize cells or hide/delete rows/columns, it also affects the shapes/checkboxes. Something as shown below:

To stop the checkbox from moving around when you resize or delete cells, do the following:

• Left click on the checkbox and select Format Control.
• In the Format Control dialog box, select the properties tab.
• In the properties tab, within Object Positioning, select Don’t move or size with cells.
• Click OK.

Now when you resize or delete cells, the checkbox would stay put.

Caption Name Vs. Name

When you insert a checkbox in Excel, you see a name in front of the box (such as Check Box 1 or Check Box 2).

This text – in front of the box –  is the Caption Name of the checkbox. To edit this text, right-click and select the ‘Edit Text’ option.

While you see the new text, in the backend, Excel continues to refer to this checkbox as Check Box 1.

If you select the checkbox and look at the Name Box field, you will see the name Excel uses for this checkbox in the backend.

You can easily change this backend name by first selecting the checkbox in the worksheet and then typing the name in the name box (the naming rules are same as that of named ranges).

See Also: How to Insert a Checkbox in Google Sheets.

You May Also Like the Following Excel Tutorials:

• Inserting Checkmark in Excel.
• Create Dynamic Chart using Checkbox.
• Create Checklists using Checkbox in Excel.
• VBA Guide to Using Checkboxes in Excel.
• How to Insert a Scroll Bar in Excel.
• How to Insert and Use a Radio Button in Excel.

17 thoughts on “How to Insert Checkbox in Excel (to Create Interactive Lists and Charts)”

1. Annett

   April 2020 at 3:37 am

   How do you get the check boxes to match going across? I can’t figure out how to make them online on my spreadsheet, without formatting each cell individually.

2. Nir

   February 2020 at 5:23 am

   I copy/pasted the formula for the Percentage of Tasks Completed function, with alterations re where my TRUE/FALSE parameters are but I get a DIV/0 error. Excel claims that I have made a divide by zero error.

   What am I doing wrong?

3. Masy

   December 2019 at 7:00 pm

   very very good wlshes for whom help us learn from two
   Ocean that is between us

4. Sara

   November 2019 at 1:01 am

   We should not have to manually link each checkbox to each cell.

5. Josh Clark

   July 2019 at 9:51 pm

   Can you use this to insert a checkbox inside of a cell instead of laying on top like a graph or just linking to a separate cell? I found another guide that does this using special characters but it would be nice if I could just represent the cell’s true/false value by a checked or unchecked box.

6. Gary Sternick

   March 2019 at 2:59 am

   Thank you Sumit – great tips and tutorials! I would like to find out if there’s a way to take this further. I want to create a spreadsheet with a row for each of the 50 United States. Each row would contain data specific to our industry in each state. I would like to insert a checkbox in each row. A user would check off the set of states involved in a transaction, and then click a “Filter” button, which would leave only the checked states columns visible. I would then want to create a report which would pull data only from the checked rows. Is this possible? Thanks again!

7. Lukasz

   January 2019 at 2:00 pm

   how can you filter by check boxes? I have one column with check boxes and what if I would like to filter by the check boxes which have a tick and which doesn’t…?

8. Masum

   January 2019 at 11:09 pm

   thanks Sumit. very useful tips.

9. Anser

   October 2018 at 5:55 pm

   Hi
   I have a question, I have an excel table, where I input my data in first 5 columns, next 3 have formulas which are Calculated automatically based on first 5 columns. Last 1 column has a check box which is linked to the cell.
   Now whenever I add another row in Table all formulas are copied from above table row with new range. but check box is not copied automatically. and if copy it manually and paste in new row. the new check box is also linked to the above cell instead of new row.
   I want it to copy automatically, same like formulas. I also want to be linked with the new row.

   Can you please advice me, how to do it?

10. Gnehd168

    September 2017 at 11:48 am

    Thanks! very useful. Keep it up!

11. Gnehd

    September 2017 at 11:47 am

    Thanks for posting this. Big help! Keep it up!

12. shabbir kanorewala

    April 2016 at 9:28 am

    Sumit it very useful when you have to display data for more than 2-3 years its easy and cosmetic too.
    Thanks.

13. Raz

    March 2016 at 1:07 pm

    sumit these tutorials are v useful…..tell me either to work with developer is easy or using formulas ..i want to learn dynamic filter with formulas also if easy

    • Sumit Bansal

      March 2016 at 4:33 pm

      Hello Raz.. Both have their benefits. Formulas are easy to apply and replicate, however, may have some limitations. With VBA, you can do a lot more (but that would require a working knowledge of VBA). Here is an example of creating a dynamic filter (non-vba) – http://trumpexcel.com/2015/01/dynamic-excel-filter/

14. Puneet Gogia

    December 2015 at 1:10 pm

    Check Box are very useful if you are working on some constant data formats. You can control your functions with check boxes. Very Nice Post. Thanks

    How do I add a checkbox to a drop

    How do I make a drop down list in a checkbox?.

    Use List Box to create a drop down list with multiple checkboxes..

    Click Developer > Insert > List Box (Active X Control)..

    Select the cell E4, enter ListBoxOutput into the Name Box and press the Enter key..

    Click Insert > Shapes > Rectangle..

    How do I add a tick to a drop down in Excel?

    Go to the Insert tab > Symbols group, and click Symbol. In the Symbol dialog box, on the Symbols tab, click the drop-down arrow next to the Font box, and select Wingdings. A couple of checkmark and cross symbols can be found at the bottom of the list. Select the symbol of your choosing, and click Insert.

    How do you create a drop down box?

    Create a drop-down list.

    Select the cells that you want to contain the lists..

    On the ribbon, click DATA > Data Validation..

    In the dialog, set Allow to List..

    Click in Source, type the text or numbers (separated by commas, for a comma-delimited list) that you want in your drop-down list, and click OK..

    How do I add a checkbox in Data Validation?

    Data validation. Next to Criteria, choose Checkbox. Select the Use custom cell values box. Next to Checked, enter a value. (Optional) Next to Unchecked, enter a value.