Microsoft Excel is a powerful program that gives you a lot of ways to work with it. Excel’s Checkboxes are one example of this kind of tool. A checkbox is an interactive tool that lets people choose a choice or not choose it.
How to Get the Developer Tab in the Excel Ribbon?
The first thing you need to do to add the option is to turn on the developer tab on the Excel ribbon. By default, this tab is turned off. Do the following to add the developer tab to the Excel ribbon:
- 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 want to make an Excel checklist, the first thing you’ll need to do is make a list of the chores or other things that will be checked off.
For this case, we’ve made the following list of things to buy at the store.
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 keep track of whether a checkbox is checked or not, you need to connect it to a specific number. Follow these steps to do this:
- 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 an 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 checkmark 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
This post showed you how to add a checkbox to a worksheet in Excel. With the help of the checkboxes, you have also made a dynamic list of things to do.
By taking the Business Analytics with Excel course, which includes Power BI training, you can improve your analytics job by learning powerful new Microsoft Excel skills.
This Business Analytics class will teach you the basics of data analysis and statistics so that you can make decisions based on the data. This training will show you how to use Power BI and explain the statistical ideas that will help you find insights in the data you have and present them using dashboards for executives.
Do you have any questions FOR US? Feel free to ask them in the comments section of this piece. Our experts will answer them as soon as they can.