Insert Checkboxes In Excel 2010

Excel provides very useful Form Controls which enable users to create a control & operate several things simultaneously. You can add Buttons, checkboxes, Labels, Combo-box, Scroll list etc. The real usage of form controls can be attained when you are dealing with colossal datasheet, and you need to invoke several functions and actions in desired order. These controls also abet you, when the data cells interconnect with each other. In this post we will be using Checkbox form control in a scenario where more than two ranges are related with each other.

Launch Excel 2010, and create a datasheet on which you want to link checkboxes with different actions. For instance, we have created an attendance sheet of students, containing fields, Name, and Attendance. We have also included another table in our datasheet that contains fields Total, Present, and Absent.

To start off, we need to make Developer tab apparent on Excel window. In case you don’t find Developer tab, go to File menu, click Options, and in left pane click Customize Ribbon, from right pane enable Developer check-box. Click OK to to see Developer tab on the ribbon.

developer tab

Now we will include checkboxes in the datasheet, in order to populate table entries with single click.  Navigate to Developer tab, from Insert options, click checkbox image present under Form Controls, as shown in the screenshot below.

developer

Upon Click, you will see plus sign pointer, click where you want to add checkbox.

checkbox

Select the text and remove it, and then right-click over it, click Format Control.

right click

The Control tab of Format Control dialog will open-up. By Cell link, select the location in the datasheet where you want to show the check/uncheck status of checkbox, which will be TRUE or FALSE respectively. Click Ok to continue.

check box options

Now we will move checkbox to the end of the Attendance cell, you will notice that the cell it is referring to H2 location, which will change the values TRUE/FALSE.

false 1

Upon enabling checkbox, the value in H2 location will automatically change. We will write formula in Attendance column at location C2, and that will check the value in H2 location,

=IF(H2=TRUE, “Present”, “Absent”)

The first parameter of the formula H2=TRUE, checks the value in H2 that if it is TRUE, keyword Present will be appear in the cell, and if it is False then Absent will appear in the cell.

Attendance present 1

Now follow the same procedure for including checkboxes with all the cells in Attendance field. As you can see in the screen shot below,  that we have created check boxes with the cells, and where the check box is enabled, the corresponding value at H2 column will change to TRUE, eventually through the formula evaluation the Present will appear in corresponding cell present in Attendance field.

complete table

Now we will start populating next table, here we have entered 10 in Total row (as we have 10 students).

next table 1

We will count the occurrence of keyword Present in the table Attendance column. So we will write formula as;

=COUNTIF(C2:C11, “Present”)

The result will be 9, as there is only one student Absent.

present studs `1

Now for checking how many students are absent, we will simple subtract number of students present, from total students. The formula goes like this

=($B$14-$B$15)

It will simply subtract value at B15 (students present) from value at B14 (Total), and yield the desired result.

attendance absent

You will also notice that on enabling/disabling checkbox, it will automatically update all the related info.

complete

You can also check out previously reviewed guides on Using Macros through Buttons & Using Camera Tool in Excel 2010.

قالب وردپرس

About Alyse Kalish

Alyse Kalish
As an Associate Editor for The SalesJobInfo, Alyse is proud to prove that yes, English majors can change the world. She calls many places home, including Illinois where she grew up and the small town of Hamilton where she attended Colgate University, but she was born to be a New Yorker. In addition to being an avid writer, Alyse loves to dance, both professionally and while waiting for the subway.

Leave a Reply

Your email address will not be published. Required fields are marked *