How to insert checkbox in Excel?

*Click images to enlarge

One of the great things about Excel is that you can insert checkboxes to make your data entry more efficient.
The checkbox is a useful tool in Excel that allows you to select or deselect certain options.
For example, for a simple use of checkbox in Excel, you could select all of the data in a column, or you could use it to select a specific range of cells.
Also for an advanced use of Checkboxes you can track tasks, mark items off a list, or select options.

So in this tutorial we gonna explain step-by-step how to insert checkboxes in Excel and as usual if you are a beginner with Excel Application , there is some terms and basics you need to know. You can take a look to our brief introduction to Excel.

NB :  In Excel there is the drop down list that can replace the checkbox that has same functionalities but its not ideal for advanced use  , you can find out more explanation by reding our post about how to add a drop down list in Excel?.

Before adding a checkbox in Excel

To add a checkbox in Excel, if you have not already done so, you must first add the developer tab to the ribbon. What’s the developer tab and what’s the ribbon you can check our tutorial How to add the developer tab in Excel?

Insert checkbox in Excel

And here is the most important part after enabling the developer tab , follow those steps to add checkbox in excel

1. On the developer tab click “Insert” button,
2. Select Check Box under Form Controls to insert the checkbox in Excel.
   Find more details in this link about ActiveX and Form Controls in Excel.

The checkbox symbol in Excel is the same for all version its look like a tick

Of course with a video you will understand more :).

Excel checkbox Form Controls vs ActiveX controls

Ok! that’s a good question and the answer its simple , you can say that Form controls is linked to Excel and works both in Windows and MacOS system . However ActiveX works separately and only on windows system (they are mainly a Microsoft-based technology) and sometimes even in windows system they are disabled.

So conclusion obviously Form controls are the most used one for Excel users but be carful when using macros in Excel with Checkbox you need to choose carefully what to use because clicking on a Form Control or Active X Control will give two different results for the same macro.

What about other Excel version?

For all other versions of Excel, it’s pretty much the same thing just the place of the menu Insert change. The below figure shows how to insert a checkbox in Excel 2010 to Excel 2016.

Excel checklist and link a checkbox to a cell

You can add an Excel checklist or insert a multiple checkbox by :

• Doing the same steps as adding a single checkbox and repeating the same process in every cell or simply you can use a macro
• Or insert one single checkbox and put the cursor in the bottom right ( the Excel fill handle )of the cell and move to any cells you want to add a checkbox in it.
  

Tips and Tricks with Checkbox in Excel

The Checkboxs in Excel (✓) can help you in different ways to organize your data. Like indicating the True/False status of activities in a list or even perferming another task if the checkbox is checked.
First thing first if you still don’t know how to add a checkbox in Excel you can check this quick tutorials that describe how to insert a checkbox in Excel.
Below you will find several tips and tricks for selecting checkboxs in Excel.

Count checked and unchecked checkboxs

To count checked and unchecked checkboxs we gonna use a little trick that will help us for identifying witch cell is checked or not:

1. Link checkbox to empty cell :
   1. 1. right-click the check box and select “Format Control”,
      2. click the “Control” tab,
      3. click in the “Link Cell” field  and select the empty cell in the sheet.

   Now when the status of checkbox changed the cell will change too with True or False value. Do this tips for all your checkbox that you want to count.
2. Count all the checkboxs : for counting all the checkbox we gonna use a function “COUNTA” :
   Select en empty cell and type the function name with the range of cells that are linked with checkboxs

   =COUNTA(F1:F11) // will return total number of checkbox

3. Count checkox with condition: here we gonna use the “COUNTIF” function:

   =COUNTIF(F1:F11,TRUE) // TRUE for checked box and FALSE for unchecked box

Link a checkbox to a cell

As already mentioned, in order to capture the checkbox status (checked or unchecked), you must associate the checkbox with a cell. To do this, proceed with the same step “Link checkbox to empty cell”

To make it easier to identify linked cells, simply select them in an adjacent column which is empty. This way you can hide the linked cells to avoid any problems in your spreadsheet.

Disable a checkbox in Excel

This trick can be done only with VBA code, if you want to know more about VBA in Excel you can follow this link Macros in Excel with VBA code examples.

Also the Checkbox used must be an ActiveX Controls if you want to know whats “ActiveX Controls” you can follow this link How to insert a checkbox in Excel.

First step we gonna open the VBA editor:

1. Right-click the checkbox and select “View Code” : the VBA editor will be opened
2. Paste this code to the VBA editor

   Private Sub CheckBox1_Click()
   Select Case CheckBox1.Value
       Case True: CheckBox1.Enabled = True
       Case False: CheckBox1.Enabled = False: CheckBox1.Value = False
   End Select
   End Sub

   Be-careful change the name of the checkbox with your own one here i used “CheckBox1”.

3. Close the VBA editor and you will see that the checkbox is disabled.

Conclusion

With many other Form Controls you can find in Excel like button ,text…, the Checkbox is mostly used with users and dont forget to take care of the Form Controls and ActiveX when you use them.

Finally, You can use the checkboxes not only for selecting true/false ,check/uncheck items but to create interactive forms in Excel. For example, you could create a survey with yes/no questions and the respondent can click on the checkbox to select their answer.