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.
Checkbox in Excel (Table of Contents)
- Check Boxes in Excel
- How to Insert a Checkbox in Excel?
- Create an Interactive Chart Using CheckBox in Excel
- How to Delete Checkbox in Excel?
What is Checkbox in Excel?
Checkbox or tick box or check mark box or selection box is a little square box where you click to select or deselect a given option.
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.
To insert a checkbox in Excel, you first need to have the Developer tab enabled in your workbook.
Steps to Enable the Developer Tab.
The first things 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.
- Go to FILE
- Click on options.
- Go to Custom Ribbon and make sure developer tab checkbox is ticked.
- 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 – 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 Checked option and give 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 cell 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.
- Select the range of topics with the checkboxes. In my case, I selected A2:C23.
- Go to Home tab > Styles group > click Conditional formatting drop-down and select a new rule from the menu
- 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.
- Click on Format and select the color you want to highlight.
- Click OK. Not if your checkbox in excel is ticked then it will highlight the task with green color.
Step 9: Hide TRUE – FALSE values.
- Select the column that includes TRUE or FALSE value and press Ctrl + 1.
Step 10: Select Custom and apply below code.
Step 11: Now your TRUE or FALSE will not be visible.
Create an Interactive Chart Using CheckBox in Excel
We can create an interactive chart using CheckBoxes.
Step 1: Create a below data in your excel.
Step 2: Create Check boxes 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 –