A check box, also known as a tick box or checkmark box or selection box, is a small square box, where you click to select or select a given option.
Inserting a checkbox in Excel seems like a trivial thing, but it opens up a host of new possibilities for your worksheets that will keep you on track with your goals, schedule, assignments and more.
How to insert checkbox in excel
Like all other form controls, the check box control resides on the Developer tab, which by default does not appear on the Excel ribbon. So, you need to turn it on first.
Below are the steps to get the Developer tab in the Excel ribbon.
- Right click any tab in the Excel ribbon and choose Customize Ribbon. This opens the Excel Options dialog box.
- In the Excel Options dialog box, you will have customized ribbon options. On the right, within the main tab pane, check the Developer option.
- Click OK. This will display the Developer tab as a tab of the rib.
- With the Developer tab now visible, you get a variety of interactive controls.
How to Insert a Checkbox in Excel
Below Given Easy Steps
- Go to Developer Tab –> Controls –> Insert –> Form Controls –> Check Box.
- Drag or Click anywhere in worksheet as per your desire it will apear
- Now create link with excel cell – right-click on the checkbox and select Format Control.
- In the Format Control dialog box, in the Control tab now do the below given following changes
-
- Value: Checked (this makes sure that the checkbox is checked by default when you open the workbook)
- Cell Link: $A$1 (this is the cell linked to the checkbox). You can manually enter this or select the cell to get the reference.
Click OK.
Your checkbox is connected to cell A1, and when you check the checkbox, it will show TRUE in cell A1, and when you uncheck it, it will show FALSE.
Examples of using a checkbox in Excel
Here are some examples where you can use a checkbox in Excel.
Creating an interactive to-do list in Excel
Below is an example of a to-do list that uses a checkbox to complete a task.
Some things are happening in the above example:
- As soon as you check the checkbox for an item / task, the status changes to DON (from to Done), the cell gets a green shade, and the text gets a strikethrough format.
- The value of the cell link for that checkbox changes from FALSE to TRUE.
- The% of As’ Task Completed ‘and Task Task Completed’ numbers (in cells H3 and H4) have been changed based on how many tasks are completed.
The way to make it is as follows:
- Have the activities listed in cells A2: A7.
- Enter the checkbox and place it in cell B2: B7.
- Link these checkboxes to cells E2: E7. There is no way to link all the checkboxes at once. You must manually link each checkbox one by one.
- In cell C2, enter the following formula: = IF (E2, “Done”, “To Be Done”) and drag for all cells (C2: C7).
- In cells C2: C7, apply conditional formatting to give the cell a green background color and strikethrough format when the cell has a value value.
- In cell H3, use the following formula: = COUNTIF ($ E $ 2: $ E $ 7, TRUE)
- This will count the total number of tasks that have been completed.
- In cell H4, use the following formula: = COUNTIF ($ E $ 2: $ E $ 7, TRUE) / COUNTIF ($ E $ 2: $ E $ 7, “<>”)
- This will show the percentage of works completed.
How Creating a Dynamic Chart in Excel
In this case, the checkbox above the chart is connected to cells C7 and C8.
If you check the checkbox for 2013, the value of cell C7 becomes TRUE. Similarly, if you check the checkbox for 2014, the value of cell C8 becomes TRUE.
The data used to create this chart is in C11 to F13. The data for 2013 and 2014 are dependent on linked cells (C7 and C8). If the value in cell C7 is TRUE, you see the value in C11: F11, otherwise you see the # N / A error. The same goes with the 2014 figures.
Now on the basis of which the checkbox is checked, the data is shown as a line in the chart.
Download the dynamic chart template.
Inserting Multiple Checkboxes in Excel
Inserting a Checkbox using the Developer Tab
To insert more than one checkbox, go to the Developer tab -> Controls -> Insert -> Form Controls -> Checkboxes.
Now when you click anywhere in the worksheet, it will insert a new checkbox.
You can repeat the same process to insert multiple checkboxes in Excel.
Note: A checkbox inserted like this is not linked to any cell. You must manually link all checkboxes.
The checkbox will have different caption names, such as check box 1 and check box 2, and so on.
Copy paste the checkbox
Select an existing checkbox, copy and paste it. You can also use the keyboard shortcut (Control + D).
Note: The copied checkboxes are associated with the same cell as the original checkboxes. You must manually change the cell link for each checkbox.
Captions of all copied checkboxes have the same name. However, the backend name will be different (since they are different objects).
Drag and fill cells with the checkbox
If you have a checkbox in a cell in Excel and you drag all the fill handles down, it will make copies of the checkbox. As shown below:
Dynamic-Chart-Series-Selection-Check-box 
Note: All new checkboxes have caption names. However, the backend name will be different (since they are different objects).
All these checkboxes will be connected to the same cell (if you have previously linked the link). You have to manually change all these links one by one.
Delete checkbox in Excel
You can easily delete a single checkbox and delete it and press the delete key. To select a checkbox, you must hold down the control key and the left mouse button.
If you want to remove multiple checkboxes:
Hold down the control key and select all that you want to delete.
Press delete key.
If you have multiple checkboxes scattered in your worksheet, here’s a way to get a list of all the checkboxes and delete them one by one:
Home -> Editing -> Find and Select -> Selection Pen.
This will open a selection pane that will list all items on that worksheet (including checkboxes, sizes, and charts).
- Select all the checkboxes you want to delete (to select multiple checkboxes, hold down the control key while making the selection) and hit the delete key.
- Note that the name of the checkbox here is the name of the backend, not the name of the caption.
