Microsoft Excel is a powerful tool that provides the user with lots of interactive options. One such feature is the Checkboxes in Excel. A checkbox is an interactive tool that allows users to select or deselect an option.
How to Get the Developer Tab in the Excel Ribbon?
The first step in inserting the checkbox is to turn on the developer tab on the Excel ribbon, hidden by default. To add the developer tab to the Excel ribbon, do the following:
- Right-click anywhere on the ribbon and select Customize the Ribbon.
- Under Customize the Ribbon, check the Developer box and click OK.
How to Insert a Checkbox in Excel?
If you are creating an Excel checklist, the first step will be to make a list of tasks or other items for which the checkboxes will be inserted.
For this example, we’ve created the following grocery list.
To insert a checkbox in Excel, execute the following steps:
- Step 1: Go to Developer Tab ---> Controls ---> Insert ---> Form Controls ---> Check Box.
- Step 2: Click in the cell where you want to insert the first checkbox (F4 in our example).
- Step 3: Position the checkbox by dragging it. To remove the text "Check Box 1", right-click the check box, select Edit Text in the context menu, and then delete the text.
Your first checkbox is ready.
Copy the Checkbox to other Cells
Drag the checkbox cells to all the below cells to copy them.
Link a Checkbox to a Cell
To capture the status (checked or unchecked) of a checkbox, you need to link the checkbox to a particular cell. To do this, execute these steps:
- Right-click on the checkbox and select Format Control.
- In the Format Control dialog box, make the following changes, and click OK.
- Value: Checked. This makes sure that the checkbox is checked by default when you open the workbook.
- Cell Link: $H$4. This is the cell linked to the checkbox. You can also enter it manually or select the cell to get the reference.
Now your checkbox is linked to the cell. In the linked cells, TRUE appears for selected checkboxes and FALSE for cleared checkboxes.
Creating an Interactive To-Do-List in Excel
Below is the example of a To-Do-List that uses the checkboxes to mark the task complete.
A couple of things will happen in the example.
- As soon as you check-mark the list, the status will change to DONE from TO BE DONE.
- The value of the cell linked to that checkbox changes from FALSE to TRUE.
- It reflects the changes in the Task Completed and % Of Task Completed cell.
Follow these steps to make this interactive To-Do-List:
- List all the activities from G5:G9.
- Insert the checkboxes from H5:H9.
- Link the checkboxes to cell J5:J9. You will have to manually link each checkbox one by one.
- In the cell I5, enter the following formula: =IF(J5,” Done”,” To Be Done”) and drag for all the cells below.
- In the cell H11, enter the following formula: =COUNTA(G5:G9) to count the total number of tasks.
- In the cell H12, enter the following formula: =COUNTIF($J$5:$J$9, TRUE).
The Next Step
In this article, you have learned how to add a checkbox to your Excel worksheet. You have also created an interactive To-Do-List with the help of the checkboxes.
Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel course, which includes Power BI training. This Business Analytics course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.
Do you have any questions FOR US? Feel free to mention them in this article’s comments section, and our experts will answer them for you at the earliest!
FAQs
1. What is a checkbox in Excel?
A checkbox in Excel is a form control that allows the users to select or deselect an option. Checkbox controls are often used to create interactive worksheets, such as surveys or checklists.
2. How do I insert a Checkbox in Excel?
Below are the steps to insert a checkbox in Excel:
- Go to the "Developer" tab (if not visible, enable it in Excel settings).
- Click on the "Insert" drop-down menu and select "Checkbox" from the "Form Controls" section.
- Draw the checkbox in the desired cell on your worksheet.
3. How do I use a checkbox in Excel formula?
You can use a checkbox in a formula in Excel to test whether the checkbox is checked or not. To do this, use the ISCHECKED function. The ISCHECKED function returns a value of TRUE if the checkbox is checked, and a value of FALSE if the checkbox is unchecked.
For example, the following formula will return the value 1 if the checkbox in cell A1 is checked, and the value 0 if the checkbox is unchecked:
=IF(ISCHECKED(A1), 1, 0)
4. Can I use a Checkbox for multiple options in Excel?
No, a single checkbox in Excel is designed for binary options, i.e., it can be checked (selected) or unchecked (deselected). If you need multiple options, consider using option buttons or other form controls.
5. How do checkboxes work in Excel?
Add the Developer Tab to the Top Ribbon of your Excel sheet, select Insert, Form Control, and then Checkbox. Select the cell where you want to insert the checkbox and right-click on the checkbox to edit the text and sizing.
6. How do I link a Checkbox to a cell in Excel?
To link a checkbox to a cell;
- Right-click on the checkbox
- Select "Format Control"
- Go to the "Control" tab, and choose the cell link where the checkbox state (TRUE or FALSE) will be displayed
7. How to delete checkbox in Excel?
To delete a checkbox in Excel, on the Home Tab, click Find & Select and click Select Objects. Now, click on the checkbox objects you want to delete. Press on the Delete key to clear boxes on the keyboard.
8. How to align checkboxes in Excel?
On the Quick Access Toolbar, click on the Align dropdown option. Select from the Top, Bottom, Left, and Right Align.