How to Insert a Checkbox in Excel

Inserting a checkbox in Excel is an easy task, the checkbox control is available in the Excel developer tools option. Checkbox and other controls like drop downs can be quite helpful while designing forms in Excel.

These controls prevent users from entering some unwanted data in your forms and hence they are preferred to traditional textboxes.

Today in this post, I will be guiding you on how to insert a checkbox in Excel. After that we will also see an example of how checkboxes can ease the data analysis tasks.

How to add a Checkbox in Excel:

As I have foretold that checkbox control is present in the Excel developer tools. But this developer tools option is absent in the Excel ribbon by default. So first of all, we need to make this option available in the Excel top ribbon and after that we can use the checkbox control. Below is a step by step procedure for adding a checkbox to Excel:

  • With an Excel sheet opened, navigate to File > Options > Customize Ribbon tab.

Activating Developer Tools Option

  • Now choose “Developer” from the left pane, add it to the “All Tabs” pane on right side and click the “OK” button.
  • After this you will be able to see a “Developer” tab on your excel ribbon. Inside the “Developer” tab click on the “Insert” dropdown and select the “Checkbox” control as shown.

How to Insert a Checkbox in Excel

  • Now, you can draw a checkbox anywhere on your excel sheet.

CheckBox Format Control Options

  • Next, you can customize your “checkbox” using the “Format Control” option.

Format Control Cell links

  • In the ‘Format Control’ window you can choose the default check or default uncheck type of checkbox. In the ‘Cell link’, select the cell in your datasheet where you want to show the check/uncheck status of checkbox. The check/uncheck status of checkbox is represented by ‘True/False’ respectively.

Recommended Reading : Selecting All Checkboxes using a Single Checkbox in Excel

Example of how to use Checkboxes in Excel:

Checkboxes can be used in making various types of forms in Excel. Below I have given an example of how you can use checkboxes for checking and entering the availability of various items in a grocery store.

Inserting a Checkbox with example

In the above example, I have created a list with Grocery Items, with a checkbox against each one them. The checkbox indicates the availability status of the item. As soon as the item is checked a label “Available” gets populated in-front of it and for unchecked checkboxes a label “Out of Stock” is shown.

This is done simply by using checkbox built-in functionality and if statements. To accomplish this first I have inserted a checkbox in the sheet and then I have selected its ‘cell link’ as the corresponding cell in range “E:E”.

For instance the ‘Cell link’ for checkbox at “B3” cell is “$E$3”. And similarly the ‘Cell link’ for checkbox at “B9” is “$E$9”. This means that when “B3” checkbox is checked the value at “E3” cell will change to “True” otherwise the value will be “False”.

Secondly, I have used an if statement [like: =IF(E2=TRUE,"Available","Out of Stock")] in front of these cells. The job of this if statement is simply to read the value of corresponding cell in “E:E” range and if its value is “True” then it displays a message “Available” otherwise the message will be “Out of Stock”.

For instance, if the checkbox at ‘B6’ is checked so the value at ‘E6’ will be “True” and hence the value at ‘C6’ will be “Available”.

Checkbox example Demo

Later, I have used a Excel Countif Function to find the total number of available items. =COUNTIF(C2:C11,"Available")

And a similar COUNTIF Function is used for finding the total number of items unavailable:

=COUNTIF(C2:C11,"Out of Stock")

Bonus Tips:

  • To insert multiple checkboxes in your worksheet add one checkbox manually. And then select the cell where you have just added the checkbox, drag the fill handle over the range of cells where you need to have the checkboxes and the checkboxes will be added in the range.
  • For deleting multiple checkboxes at once. Navigate to ‘Home’ Tab > ‘Find and Select’ > ‘Go To Special’. Now in the ‘Go To Special’ window, select the ‘Objects’ radio button and click ‘Ok’. This will select all the check boxes in the sheet, and now simply press the delete key from the keyboard to delete them.
  • Note: Above method will delete all the objects (like: checkboxes, buttons, combo-boxes, etc.) from your active sheet. If your worksheet contains other objects apart from checkboxes then to delete them, navigate to ‘Home’ Tab > ‘Find and Select’ > ‘Selection Pane’. Select the objects the objects you want to delete in the ‘Selection Pane’ and finally press the delete key.

So, this was all about inserting and using a checkbox in Excel. Please feel free to share any comments or queries related to the topic.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.
You can find Ankit on FaceBook or Google Plus

Comments

  1. Hi Ankit,

    You have no idea how much value you are spreading in the world, where anyone can use your website to do something new and amazing with excel, and go home happy.

    Keep up the good work buddy.
    Sudeep

  2. Tiffany White says:

    Thank you for your explanation of checkboxes. You really make it easy to understand.

    I was wondering if there was a way to only have the items that are checked to be on a final list. I am making a price sheet and have a master list of the prices but not all items are used for the final sales sheet, only the items that are checked need to be on the final list. Is this possible?

    Thank you!
    Tiffany

    • Hi Tiffany, Yes it is very much possible. You can do this by cleverly using IF Statements with ‘cell link’(i.e. check value) .
      Please check your email as I have sent you a sample spreadsheet to solve this problem.

  3. This was awesome!!!!!!

  4. cheraine says:

    Heey!

    Thanks for the tut, I have a question. Now you have a bunch of checkboxes but can you make a checkbox above the entire row that select or deselect the entire row of checkboxes?

    gr Cheraine

    • Hi Cheraine, its a good question. For this you need to write a small macro on the click event of the master checkbox. I have created a spreadsheet for doing this please download it [here]. I will also come up with a detailed post on this. So, keep visiting. :)

  5. Toye Omilabu says:

    How can I group checkboxes and how can I make it such that only one checkbox can ticked/selected at a time from the group

    • Hi Toye, In such a case I would suggest you to go for radio buttons. The fundamental difference between checkboxes and radio buttons is that: Checkboxes allow users to choose multiple options from a group (i.e. they can check multiple checkboxes), while radio buttons allow user to choose exactly one item from a group.
      Radio buttons are also available in the Developer Tab. Hope it helps :).
      Please let me know in case you need any other help from my end.

  6. shafira says:

    Awesome Ankit!!!

  7. Rajiv Vishwa says:

    Tutorial is great but better that is the follow up you do on each and every comment.
    Kudos for that.

  8. Hi I’d like to add checkboxes too but I realize that my 2007 version does not have the ‘customize ribbon’ option. Does that mean I can’t add checkboxes? Thanks in advance!

  9. Hi I’ve found the answer. Just in case anyone has the same problem, go to Excel Options, under ‘Popular’, check ‘Show Developer Tab in Ribbon’. Thanks for the tutorial Ankit! Now I’m off to creating my checkboxes.

  10. I copied a table of data with included checkboxes — which I don’t need or want — so I simply want to delete them. It’s not obvious or easy — they won’t go away. I’ll take your suggestions for insertion; and hopefully find a way to deactivate and remove them. Thanks.

    • Hi Jim, there is a much easier way to delete multiple check boxes:
      1. First on the Home Tab navigate to ‘Find and Select’ > ‘Go To Special’.
      2. Now in the ‘Go To Special’ window, select the ‘Objects’ radio button and click ‘Ok’.
      3. This will select all the check boxes in the sheet, and now simply press the delete key from the keyboard to delete them.
      Hope it helps :)

  11. Hi Ankit.

    Very informative guide, thank you.
    A very random way to add a Tick or Cross to a Cell, is to change the Font of the Cell to Windings2. An “O” makes a Tick, and a “P” makes a Cross. You’re welcome to add that to your guide.

    Normal Wingdings also have a Tick and a Cross, but the characters are “ü” and “û”, which is a bit harder to just type in.

    Thank you again for the awesome guides!

  12. Ankit, what is the difference between a “Form Control” or an “Active-X Control”?

    Currently i’m using the last column on a spreadsheet as a way to set off certain equations. So that if in F5 it says “Credit” it applies it to the credit card payments column, if it says “Cash” to the cash column, and lastly “check” to the check column. Most of the columns have large nested if statements in them that follow different rules on how to sum and apply to current balances for our customers. Originally if I had known how to use check boxes I would have done that and am still considering moving it back to that solution. The problem I come across though is that a check box only allows me two options (True/False) I would need more then one check box to let there be all 3 options correct? (And once I need two it would be easier to explain as 3) This seems to start becoming more cumbersome and space consuming where as my current solution is only taking up a single cell. Are there any other Form Controls that are as useful as the check box that would accomplish this task in a non intrusive manner?

    • Hi Madds,

      The main difference between Form Controls and ActiveX controls is that:
      Excel responds to a Forms control only after the user finishes interacting with it where as it continuously responds to an ActiveX control. And you can use form controls on worksheets as well as on charts but ActiveX controls can only be added on worksheets.

      And for your case, I think you can try using something like excel drop-downs or combo-boxes.
      As they have multiple states (values).

  13. Hi Ankit,

    I have to create a list of hundreds of checkboxes.
    To do it faster than one by one, I copy the cell containing my first checkbox and paste it in all my lines.
    But… all these boxes are linked (checking one make all of them getting checked :/ )

    Do you have a tip to create multiple independant check boxes in a quick way ?

    Thanks a lot for your work

    A

    • Hi Malus,
      Instead of copying the checkboxes you can try, selecting the cell where the checkbox is present. And then drag the fill handle over the range of cells where you need to have the checkboxes.
      Hope it helps :)

      • danielle says:

        I was able to successfully copy multiple checkboxes using the suggested method, but now all of the checkboxes are “linked” to the same reference field.

        • Hi Danielle,
          That is because before dragging the first checkbox you would have already added its cell link.
          But, no worries! Use the below macro and it will correct the cell links.

          Sub Modify_Cell_links()
          Dim CB As CheckBox
          Dim Row As Integer
          Dim Clm As Integer
          Row = 4 'row number of first linked cell
          Clm = 5 'column number of first liked cell
          For Each CB In ActiveSheet.CheckBoxes
          CB.LinkedCell = ActiveSheet.Cells(Row, Clm).Address
          Row = Row + 1
          Next CB
          End Sub

          Please note that here you need to change the values of ‘Row’ and ‘Clm’ variables.
          For instance, if for your first checkbox you want the A5 as cell link, then ‘Row’ should be 5 and ‘Clm’ should be 1.

          Hope it Helps :)

  14. This is Simply AWESOME!

  15. DAN SOUL says:

    Hi Ankit,
    How do i get a sum of the number of checkboxes that are checked? I saw your example above but i dont have an IF statement associated with the checkbox, only a CHECKBOX.
    Thanks

    • Hi Dan,
      For getting the sum of checked checkboxes you can simply use the count if function as:
      =COUNTIF('Range of cell links',"True")
      Where ‘Range of cell links’ indicates the cells that contain the cell links to your checkboxes.
      Hope it helps :)

      • DAN SOUL says:

        Hi Ankit,
        This did help, but the problem im running into is i have a series of check boxes in a row, like 100 of them. So i ran into the problem with the true false function since i have multiple series of check boxes.

        • Hi Dan, I guess this should not be an issue unless you have used a single cell as the ‘cell link’ for all the checkboxes present in a row.
          So, what I suggest is like:
          1. First insert the checkboxes horizontally (i.e. in a single row).
          2. And then for cell links use the cells in the following row, under corresponding checkboxes.
          3. So after this you will have checkboxes in first row and their corresponding cell links below them.

          This will help you to apply the formula and will give correct results. :)

      • DAN SOUL says:

        Thanks for your help! appreciate it!

  16. Hi Ankit

    Is there a way to get the sum of checked checkboxes if there is no cell link? My co-worker created the spreadsheet so I’m not sure what method he used to create the checkboxes and I know very little about excel.

    Thanks.

    • Hi Liz,
      Yes their is a way, but I think updating the ‘cell link’ property of already existing checkboxes would be much simpler.
      Anyways that’s your choice, so here is the second way to do this.

      1. Press Alt + F11 to open the VBA editor. Next navigate to ‘Insert’ > ‘Module’.
      2. Now in the Editor paste the following code:

      Sub Find_Checkbox_State()
      Dim CB As CheckBox
      Dim Checked_box As Integer
      Checked_box = 0
      For Each CB In ActiveSheet.CheckBoxes
      If CB.Value = 1 Then
      Checked_box = Checked_box + 1
      End If
      Next CB
      MsgBox "Currently " & Checked_box & " checkboxes are in Checked State."
      End Sub

      3. Run this code using ‘F5′ key and it will give you the number of checked checkboxes in the Active Sheet.
      4. Alternatively, you can also add this macro on the click event of a button for making it easier to use.

      Hope it helps :)

  17. Thank you so much for this tutorial! My only question: how do I get rid of the “Check Box 1″ text label after the checkbox?

  18. Hi Ankit,

    Could you please tell me the Excel Vb script to Delete a Checkbox?
    Eg: I would like to delete a checkbox from the cell “C38″ which is also linked to the same cell.
    I have got the code to delete multiple checkboxes, but just cant seem to delete 1 particular checkbox :(

    ‘Code to Delete all checkboxes in a sheet
    For Each vShape In ActiveSheet.Shapes
    vShape.Delete
    Next vShape

    • Hi Tanu,
      In your case the below mentioned code will work.

      Sub DeleteCheckbox()
      For Each vShape In ActiveSheet.Shapes
      If vShape.TopLeftCell.Address = "$C$38" And vShape.FormControlType = xlCheckBox Then
      vShape.Delete
      End If
      Next vShape
      End Sub

      I have tested the code myself and it works.
      Please let me know in case you face any issues.
      Hope it helps :)

Speak Your Mind

*

Privacy Policy | Disclaimer | About