What is Checkbox in Excel?
Checkbox is a developer tool available in excel which is used to place a checkbox in a cell by which we can identify whether a task has been completed or not, in the checkbox when ticked it shows a tick mark which generally means the task is completed and returns a true value while an empty checkbox returns a false value, checkbox is present in the insert section of the developer’s tab.
In this checkbox tutorial, we will discuss how to make a checkbox in Excel and use the checkbox results in formulas to create an interactive checklist, to-do list, report, or graph.
Steps to Enable the Developer Tab.
The first thing you need to do is make sure the Developer tab is enabled in your Excel ribbon.Developer Tab Is Enabled In Your Excel Ribbon.Enabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu. It should be visible like the below one.
If not, please enable the Developer tab by following the below steps.
- Step 1: Go to FILE
- Step 2: Click on options.
- Step 3: Go to Custom Ribbon and make sure the developer tab checkbox is ticked.
- Step 4: Now, you can see the develop tab on your ribbon.
How to Insert a Checkbox in Excel?
In order to insert Checkbox in excel, follow the below steps.
- Step 1: Go to Developer Tab – Controls – Insert – Form Controls in ExcelForm Controls In ExcelExcel Form Controls are objects which can be inserted at any place in the worksheet to work with data and handle the data as specified. These controls are compatible with excel and can create a drop-down list in excel, list boxes, spinners, checkboxes, scroll bars. – CheckBox.
- Step 2: Draw anywhere in your worksheet.
- Step 3: Now right-click on the checkbox select Format Control
- Step 4: In the Format Control dialog box, in the Control tab, make the following changes: Select the Checked option and give a cell link to B1.
Now your first checkbox in excel is ready.
Since your checkbox in excel is linked to Cell B1, if you click the checkbox, it will show as TRUE, or else it will show as FALSE.
Checkbox Example – Create Your Marriage Check List
Assume that you are marrying in a couple of months. You need to do a hell lot of work, and you may tend to forget more often. To create a checklist in excel to track all your work to stay up to date.
- Step 1: Create a Checklist first.
- Step 2: Insert CheckBox in excel from the developer tab.
- Step 3: Draw that in the status column.
- Step 4: Right click > Edit Text > Delete the text.
- Step 5: Right-click once again and select format control an application below settings, as shown in the image.
- Step 6: Drag this to all the remaining cells.
- Step 7: Right-click on each checkbox and give respective cells as a link. Do this for all the cells.
As the value of a cell changes TRUE/FALSE on click, we can use it now.
- Step 8: Apply Conditional Formatting to highlight all the task that is completed.
- Step 9: Select the range of topics with the checkboxes. In my case, I selected A2:C23.
- Step 10: Go to Home tab > Styles group > click Conditional formatting drop-down and select a new rule from the menu.
- Step 11: From the dialogue that appears, select the last item in the list in the top half of the dialogue box that says, “Use a formula to determine which cells to format” and put this formula in the range bar.
- Step 12: Click on Format in Excel and select the color you want to highlight.
- Step 13: Click on OK. Not if your checkbox in excel is ticked, then it will highlight the task with green color.
- Step 14: Hide TRUE – FALSE values.
- Step 15: Select the column that includes TRUE or FALSE value and press Ctrl + 1.
- Step 16: Select Custom and apply the below code.
- Step 17: Now, your TRUE or FALSE will not be visible.
Create an Interactive Chart Using CheckBox in Excel
We can create an interactive chart in excel using CheckBoxes.
- Step 1: Create a below data in your excel.
- Step 2: Create Checkboxes for all the years. Give each year a different cell link. 1st image is for 2015, 2nd is for 2016, 3rd is for 2017 & 4th is for 2018.
This is how looks after 4 checkboxes.
- Step 3: Rearrange the data, as shown in the below picture.
- Step 4: Select the rearranged data and apply a column chart.
- Step 5: Select each year column bar and change it to line chart except for 2018.
- Step 6: If you uncheck any of the four checkboxes, it will not show the graph for you.
How to Delete Checkbox in Excel?
You can easily delete a single checkbox in excel by selecting it and pressing the delete key. To select a checkbox, you need to hold the Control key and then press the left button of the mouse.
From the home ribbon, also you can delete the checkboxes.
Go to Home –> Editing –> Find & Select –> Selection Pane.
As soon as you click on that Selection Pane, that will list all the objects on that worksheet (including checkboxes, shapes, and charts).
Select the checkboxes you want to delete and hit the delete key. In order to eliminate confusion, give a proper name to all the checkboxes.
This has been a guide to what is CheckBox in Excel. Here we discuss how to Insert a CheckBox and to create an interactive chart using CheckBoxes along with practical examples and downloadable templates. You may also look at these useful functions in excel –