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.

   How to insert checkbox in Excel?
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.