The checkboxes work like switches. If you On the switch of a machine, it should start working and in the Off state, it should stop.
Similarly, you may turn a checkbox “On and Off”; precisely True/False.
For example, display Pass as the checkbox is ticked and Fail as unchecked in a student result sheet.
In this tutorial, I am going to show you how to create checkbox (also called tick box) in the Excel sheet. This is followed by a few examples of using it for different Excel tables, so you can see checkboxes in action.
Steps for inserting checkbox into Excel sheet
The checkbox is a form control in Excel that you may place over a cell.
- To place a checkbox in a sheet, go to the “Developer” tab.
- If the Developer tab is not visible, this is how you may configure it to display.
Excel 2010 and above
Go to File –> Options –> Customize Ribbon. There, you may see the “Developer” checkbox – Tick this and press OK.
Excel 2007
Go to Microsoft Icon on the top left –> Excel Options –> Popular –> “Show Developers tab in the Ribbon”.
Enable this option as shown below:
Under the Developer tab, press Insert and look for the checkbox as shown below:
After clicking the checkbox under the form control, click in the area of the Excel sheet (in a cell) where a checkbox should be inserted. See the figure below:
Moving the checkbox
After a checkbox is placed in the Excel sheet, you may move it by selecting that checkbox.
For selecting a checkbox, bring the mouse over the checkbox area (not the cell) and right click there. The checkbox should be selected.
Changing the caption of the checkbox
The default text is not something that you want in your Excel table.
For modifying the caption/visible text of the checkbox, right click in the checkbox and press the Edit Text option as shown below:
Copying Checkboxes to other cells
If you have a large Excel sheet with plenty of data and you require using checkboxes then creating a checkbox one by one can be a tedious task.
You may also copy the checkbox across a range of cells by using the mouse.
- For that, select the cell that contains a checkbox.
- Move the mouse pointer towards the right bottom of that cell until a solid line + sign appears.
- Now, drag the fill handle to the range of cells where you want to copy that checkbox.
See the figure below:
You can see, we displayed checkboxes for showing the status of a product whether it is “In stock” or not.
The ticked state means the specific product is in stock. The unchecked state means “Out of stock“.
Formatting the checkboxes
You may change the look of the checkbox by using “Format Control” option.
To access that, select the checkbox by right-clicking it and press “Format Control”:
The “Format Control” dialog should appear that enables you to customize the:
- fill color
- Line style (color, Style, Dashed, Arrows under the “Colors and Lines” tabs)
- Similarly, you may press the size tab to change the height, width, and scale of a checkbox.
You may play with other tabs for making a checkbox as per your need and liking.
Working with checkbox values
As mentioned earlier, the Excel checkboxes work in TRUE/FALSE states.
If a checkbox is ticked, it is taken as TRUE while for the unchecked state, it is FALSE.
This can be useful for making checkboxes more interactive.
I will show one such example after this section, first have a look at how TRUE/FALSE states can be reflected in cells.
See the following graphic:
You can see, a few cells are displaying TRUE that are checked and others FALSE text which are unchecked.
This can be done easily by following these steps.
Right-click the checkbox for which you want to display TRUE/FALSE and select the “Format Control” option.
Select the Control tab in the Format Control dialog box.
You can see the Cell link text box as shown by the red arrow below:
There, I used $D$6 cell. That means the ticked/un-ticked state values (TRUE/FALSE) will display in the same cell where I created the checkbox.
You may enter any other cell as per your requirement and press OK.
Now tick/untick the checkbox and see the updated value in the linked cell.
Using the TRUE/FALSE value for a more practical example
This TRUE/VALUE in the cell can be very useful.
For example, in our example sheet, we have a Status column that tells whether a product is In Stock or Out of Stock.
For the demo, I have created another column containing checkboxes. See the image below:
As a checkbox is ticked, the adjacent Status cell is updated by “In Stock” text. For the unchecked state, it displayed the “Out of Stock” value.
So how is it done?
I simply checked the value of the respective cell that contained a checkbox and applied this formula to the Status column cells.
For example, in E3 cell, the following formula is used:
=IF(D3=TRUE,"In Stock","Out of Stock")
In E4:
=IF(D4=TRUE,"In Stock","Out of Stock")
and so on.
By using the IF function, we checked the value of the checkbox and set the text to TRUE/FALSE values.
Creating an Excel checklist example
In this example of using checkboxes, I have created a rough checklist of website software development phases.
- The Column A cells list the tasks e.g. Planning, Analysis, Design, etc.
- Column B displays a checkbox for each task. If a checkbox is ticked, that means the task is completed. Otherwise, the task is incomplete or not yet started.
- Column C is used to store TRUE/FALSE values for each respective checkbox. I have hidden this column as displaying it does not serve any purpose. We need its values that are still available.
- Based on ticked and unchecked checkboxes, I have listed the total tasks completed and incomplete by using a formula.
Have a look at the table and formulas below:
You can see, in the task checklist, 7 are ticked so Completed tasks are displayed as 7. The Tasks Remaining as 6 while the total number of tasks is 13.
The following formulas are used in B17, B18, and B19 for displaying the result based on the Excel checklist:
D17 formula for Total tasks:
D18 Formula for completed tasks:
D19 formula for remaining tasks:
You may notice we used C2:C15 ranges while this is not visible in the above image. As you tick/untick the checkbox, the result will be updated automatically for complete/remaining tasks.
How to delete checkboxes
For deleting checkboxes one by one, you may right-click on a checkbox and press delete.
However, if you want to remove more than one checkbox at single operation – follow these steps.
Under the Home tab look for “Find & Select”. As you press it, click on “Go to Special”.
The “Go to Special” dialog should appear where select the “Objects” and press OK.
It should select all checkboxes and any other objects in the Excel sheet. Press the delete button on the keyboard and you are done.
Note: For removing selected checkboxes, you may also do this
- Press the “Find & Select” and press the “Selection Pane” option located at the bottom.
- You can see a list of all objects including checkboxes.
- Select the checkboxes you want to remove and press the Delete button.