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:
- Go to Developer Tab ---> Controls ---> Insert ---> Form Controls ---> Check Box.
- Click in the cell where you want to insert the first checkbox (F4 in our example).
- 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).
Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!
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 certification 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!