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.
- 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.
- Now, you can draw a checkbox anywhere on your excel sheet.
- Next, you can customize your “checkbox” using the “Format Control” option.
- 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.
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”.
Later, I have used a Excel Countif Function to find the total number of available items.
And a similar COUNTIF Function is used for finding the total number of items unavailable:
=COUNTIF(C2:C11,"Out of Stock")
- 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.