CONTENTS
- INTRODUCTION
- WHY TO USE CHECKBOX IN EXCEL?
- BUTTON LOCATION FOR INSERTION OF CHECK BOX IN EXCEL
- HOW TO INSERT A CHECKBOX IN EXCEL ?
- HOW TO CONTROL A CHECKBOX IN EXCEL ?
- HOW TO EDIT TEXT OF A CHECKBOX IN EXCEL ?
- EXAMPLE : CHECK THE STATUS OF THE PROPOSAL USING THE CHECKBOX STATUS IN EXCEL
- RUNNING THE EXAMPLE AND CHECKING THE RESULT
INTRODUCTION
A CHECKBOX is a visible element which returns a TRUE if checked and FALSE if the checkbox is unchecked.
Checkbox is very useful in many situations such as to manage a TO DO LIST, ATTENDANCE , A CHECKLIST or any situation where we have just two values like a BINARY SYSTEM.
But we have a directly usable option of CHECKBOX in the EXCEL.
A CHECKBOX IS AN ELEMENT WHICH CAN BE USED IN THE SITUATIONS WHERE WE HAVE TWO CHOICES ONLY – INCLUSION AND EXCLUSION. BY DEFAULT, CHECKBOX RETURNS A TRUE IF CHECKED AND FALSE IF UNCHECKED.
In this we’ll learn the ways to insert and use a check box in EXCEL.
WHY TO USE CHECKBOX IN EXCEL?
A checkbox can be used in the following situations :
- When the user needs to choose from two options only e.g. yes or no, true or false, 1 or 0 and so on.
- When we don’t want the user to type anything.
- When we want to create a checklist, attendance , to do kind of lists.
- When we need to get the custom input from the user having two options only.
The picture above shows a demonstration of the use of CHECKBOX in EXCEL.
It is just a representation but we can extract the analytical data from the table too. In the examples we’ll explore more possibilities from the checkboxes.
BUTTON LOCATION FOR INSERTION OF CHECK BOX IN EXCEL
The CHECKBOX option is available under the DEVELOPER TAB. The DEVELOPER TAB is not visible by default , so it is important to learn the way to make it visible in your Excel .
CLICK HERE TO LEARN THE PROCESS OF MAKING THE DEVELOPER TAB VISIBLE IN YOUR EXCEL.
After the DEVELOPER TAB is VISIBLE.
Go to DEVELOPER TAB and click INSERT drop down and choose CHECKBOX under the CONTROLS SECTION.
The following picture shows the controls.
HOW TO INSERT A CHECKBOX IN EXCEL ?
STEPS TO INSERT A CHECKBOX IN EXCEL :
- Go to DEVELOPER TAB and click INSERT DROP DOWN.
- Choose CHECKBOX from the form controls.
- The cursor will change to the + shape.
- Draw the checkbox anywhere , where you want to insert it.
- The following picture depicts the process.
This was the procedure to insert a checkbox in EXCEL which was quite simple.
Let us now take an example to learn the use of Check box and its possibilities. IMPORTANT: For controlling or reading the state of the checkbox, there is a LINK CELL which we’ll discuss in the next section.The Link cell will show TRUE if the checkbox is checked and FALSE if the checkbox is unchecked. We make use of the link cell to perform further calculations. We can always change the CHECKBOX TEXT to anything we want, even if we want it to be empty.
HOW TO CONTROL A CHECKBOX IN EXCEL ?
We control a checkbox in Excel by linking it to a cell.
FOLLOW THE STEPS TO LINK THE CHECKBOX TO A CELL.
Carefully RIGHT CLICK the check box.[ THE TEXT OF THE CHECKBOX TO BE PRECISE ].
The following picture shows the scenario.
- After clicking the FORMAT CONTROL,go to CONTROL TAB.
- Go to CELL LINK FIELD for the selection of link cell.
- Put the cell address in the CELL LINK FIELD highlighted in the picture below. Alternatively, you can click on the arrow on the right end of CELL LINK FIELD and click it, it’ll let you select the cell using the mouse.
- Click OK.
- Look at the picture below for reference.
- The cell has been linked.
THE LINKED CELL STORES THE STATUS OF THE CHECKBOX.`
HOW TO EDIT TEXT OF A CHECKBOX IN EXCEL ?
We already learnt to insert a check box and linking the checkbox to a cell.Now , let us learn to edit the caption of a checkbox. [ Caption is the text sticking to CHECKBOX ].
FOLLOW THE STEPS TO EDIT THE CAPTION OF A CHECKBOX IN EXCEL RIGHT CLICK the caption of the CHECKBOX.Choose EDIT TEXT from the menu.
As we click EDIT TEXT, the caption or text will become editable.
Edit the caption as per desire.
Press Enter.
EXAMPLE : CHECK THE STATUS OF THE PROPOSAL USING THE CHECKBOX STATUS IN EXCEL
SCENARIO
A company has floated a contract and applications have been received.
There are 10 criteria in total and the applicants qualifying more than 50% criteria , will have their proposals accepted.
SOLUTION PLANNING
We have created a form containing the checklist of 10 criteria.
We’ll find out the total number of checked CHECK BOXES and compare if the number of checked boxes is more than 50% i.e. 5.
If the number of checked boxes is greater than 5, the proposal will be accepted otherwise rejected.
The following picture shows the scenario
SOLUTION
FOLLOW THE STEPS TO CHECK IF THE PROPOSAL IS ACCEPTED OR REJECTED
- Select the FINAL STATUS cell.
- Enter the formula as =IF(COUNTIF(F53:F62,”TRUE”)>5,”ACCEPTED”,”REJECTED”).
- Press ENTER.
- The result will appear as ACCEPTED or REJECTED as per the criteria fulfilment .
EXPLANATION
Let us try to understand the formula used.
We used the formula as
=IF(COUNTIF(F53:F62,”TRUE”)>5,”ACCEPTED”,”REJECTED”)
The theoretical explanation of the formula is
IF , NUMBER OF COUNTS OF TRUE [i.e. checked CHECK BOXES ] is greater than 5 i.e. 6 or more , the formula will return ACCEPTED otherwise REJECTED.
IF function is the outermost function.
The first argument in the IF FUNCTION is another function which will return TRUE or FALSE based upon the result of COUNTIF function result.
COUNTIF function has the first argument as H10:H19 which contains the range of the data. i.e. Check boxes.
The second argument of the COUNTIF function is the criteria, we entered TRUE in this which means , it’ll count the TRUE i.e. checked boxes only.
The result will be number of selected Checkboxes.
The second argument of the IF FUNCTION is ACCEPTED, which will be returned if number of selected CHECKBOXES is greater than 5 otherwise REJECTED will be returned.
RUNNING THE EXAMPLE AND CHECKING THE RESULT
The following animated picture shows the usage of the example.We make a random selection of the criteria and we can see that the status converts to ACCEPTED when the selected criteria is greater than 5 i.e. 6 or more.
The status becomes REJECTED if fulfilled criteria is 5 or less.
In this article, we learnt about the way to INSERT CHECKBOXES in EXCEL, how to use a checkbox in EXCEL, how to link a cell to Checkbox in excel and how to edit the caption of the checkbox.
