A checkbox is an interactive tool used in many web programs for choosing or un-choosing between possible options. It is found in many online programs for use such as online forms and surveys.
Just like how Excel or any program provides checkboxes for users to make selections, you can also create checkboxes to build interactive dashboards, checklists and even dynamic charts.
In this tutorial, you will learn how to create an interactive checkbox and multiple checkboxes. The following topics will be covered in this tutorial:
- How to Add the Developer Tab to the Ribbon in Excel
- How to Create/Inset a Checkbox In Excel
- How to Create/Insert Multiple Checkboxes in Excel
- How to Delete a Checkbox in Excel
- How to Stop a Checkbox from Moving Around in Excel
- How to Change The Caption Name of a Checkbox
Before a checkbox can be inserted in excel, you will need to have the Developer tab enabled in your workbook. If you do not see the Developer tab in your workbook, do not fret. Just keep on reading!
#1. How to Add Developer Tab to the Ribbon in Excel
To insert a checkbox in Excel, the Developer tab must be enabled and displayed in your workbook ribbon. It is usually not displayed by default so it will need to be added by you.
In the developer tab, you will find the checkbox control that you need to use to insert the checkbox.
To do this, there are steps you need to follow. Here are the steps below:
- Right-click on any of existing tabs in the Excel ribbon and select ‘Customize the Ribbon’ to go to Options dialog box
use the shortcut below to open the Options dialog box:
Click on File tab -> Click on Options -> Select ‘Customize the Ribbon‘
2. In ‘Excel Options’ dialog box, under ‘Main Tabs’ under look for ‘Developer’ and check the box.
Now, you will have the Developer tab visible in your workbook. You can see that the first picture before had no Developer tab and after following the steps, it has been added to my workbook ribbon.
You can now access the selection of interactive controls.
#2. How To Create/Insert a Checkbox in Excel
When you have added the Developer tab to your workbook ribbon, you can now go ahead to inset an interactive checkbox.
Here are the steps you should follow:
- Click on Developer Tab -> Insert -> Form Controls –> Checkbox
2. Click on anywhere in the worksheet to insert the checkbox.
The next thing you will need to do is to link the checkbox to a specific cell in the worksheet. To do this:
3. Right-click on the checkbox and select ‘Format Control’
4. In the Format Control dialog box, click on the ‘Control’ tab.
5. Select ‘Checked’ under Value (This will ensure that the checkbox is checked by default when the workbook is opened).
6. Type this formula: $A$1 in Cell Link. (This is the cell the checkbox will be linked to).
7. Click Ok.
The checkbox now, has been linked to cell A1. When you check the box, TRUE will be displayed in cell A1, and when you uncheck the box, it will change to FALSE.
#3. How to Create/Insert Multiple Checkboxes in Excel
When you want to create multiple checkboxes, there are various ways you can do this.
#1: Using the Developer Tab
a. Go to Developer Tab -> Click on ‘Insert’
b. Under ‘Form Controls’, click on the checkbox icon
c. Now click on any location in the worksheet to insert a new checkbox
Now, you will have to repeat this same process to insert multiple checkboxes in the worksheet.
It should be noted that:
- The checkboxes are not linked to any cell, so you will need to link all the checkboxes manually.
2. The checkboxes will have different caption names assigned to each checkbox. i.e Checkbox 1, Checkbox 2, Checkbox 3, etc. You may rename the checkboxes if wish to.
#2: By Drag and Filling the Cells
The second option is to drag and fill to the other cells.
After you have positioned the first checkbox in the cell location that you want, you can drag and fill down to copy and paste the checkbox in the other cells as it is shown below:
It should be noted that:
- All the checkboxes have the same caption name i.e ‘Checkbox 1’. However, the program recognizes each checkbox as separate objects and so in the backend, they are named differently.
- If you have already linked the first checkbox to a cell, all other checkboxes will be linked to the same cell. So you will need to change the links to each checkbox manually.
#4. How to Delete Checkbox in Excel
If you have created more than the required number of checkboxes and you need to delete any checkbox you have inserted into your worksheet, then you can easily delete a checkbox by:
- Selecting it and pressing the delete key.
To select the checkbox, hold down the Control key and press the left button of the mouse to select the checkbox.
To delete multiple checkboxes, you will need to hold down the Control key and select all the checkboxes to be deleted. Then press the delete key.
If you have several other unwanted checkboxes scattered all over your worksheet, you can delete all at a go by selecting a list of all the unwanted checkboxes and deleting them.
To do this:
- Go to Home -> Editing -> Find & Select
2. Select ‘Selection Pane’ to list any objects (Checkboxes, charts and shapes) in the worksheet
3. Select all the checkboxes you want to delete. Hold the control key down while selecting.
4. Press the ‘Delete’ key.
Note that the caption names of the checkboxes here are names given in the backend and not the caption names you changed.
Also, only the objects in an active worksheet are displayed in the selection pane.
#5. How to Stop Checkbox from Moving Around
When using shapes and objects in Excel, it is a common issue that the shapes and checkboxes are usually affected. When resizing cells or hiding/deleting rows and columns, the checkbox moves position.
To stop the checkbox from moving around whenever you resize, hide or delete cells, follow these steps:
- Right-click on the checkbox and select ‘Format Control’
2. In Format Control dialog box, select ‘Properties’ tab.
3. In Properties tab, select ‘Don’t move or size with cells’.
4. Click Ok.
Now you will see that the checkbox does not move around when you resize, add or delete cells.
#6. How to Change the Caption Name of a Checkbox
When a checkbox has been inserted into an Excel worksheet, it comes along with a text (such as Check Box 1, Check Box 2, e.t.c) next to the box.
This text next to the box is referred to as the ‘Caption Name’ of the checkbox.
So, to edit or change the name of the checkbox,
- Right-click the box and select ‘Edit Text’ option
Although you can see the changed name of the checkbox, however in the backend, Excel still recognizes it as Check Box 1.
To see the name Excel uses for a checkbox in the backend,
- Select the checkbox and look at the Name Box field
If you want to change the backend name,
- Select the checkbox in the worksheet and type the name in the name box. The same rules of ‘named ranges’ applies.
I hope you found this tutorial useful. Kindly share your thoughts in the comment section.