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. 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 Excel – 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 on 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 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 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 –