Excel - Checkbox

---

---

Microsoft Excel facilitates Active X controls, such as Buttons, ListBoxes, Textboxes, and Combo Boxes. In this tutorial, we will learn about the basic concept of the Checkbox in Microsoft Excel.

Project managers can easily determine the completion of critical tasks through the checkbox control. For example, you have a list of aircraft in a worksheet that will be delivered in the different regions. If you select the checkbox, the aircraft is delivered otherwise, it is not delivered.

Insert a Checkbox in Microsoft Excel

Below are the simple steps that you can employ to insert a Excel checkbox.

Step 1 − First, we have a dataset comprised of two columns titled Patient Name and Diabetes(Yes/NO). Navigate to the Developer tab and expand the Insert option from the Controls group. In the Diabetes column, we must insert a checkbox inside a cell range B2:B7 corresponding to an individual patient name.

Note − If the Developer tab is not found in the Excel Ribbon, you may right-click on any part of the Excel tab and select the "Customize the Ribbon" option. Another window, "Excel Options," will appear. You can tick the Developer checkbox under the Customize the Ribbon and hit the OK button.

Step 2 − Afterward, select the Checkbox(Form Control) under the Form Control section and place it into a targeted cell.

Step 3 − Select the B2 cell to insert the excel checkbox and align the checkbox size in this cell.

Step 4 − Right-click the inserted checkbox, choose the Edit Text option, and remove the default text.

Step 5 − You may copy and paste the Excel checkbox from one cell into the other cells. Drag the + sign at the bottom right corner of the B2 cell to copy this checkbox and drop it into the B7 cell. Therefore, the checkboxes are copied in the remaining cells from B3 to B7.

Steps to Delete a Checkbox in Mircosoft Excel

Given below are the steps you can use to remove a checkbox from the specific cell −

Step 1 − First, you can move to the Home tab, click on the Find & Select option under the Editing group, and choose the "Select Objects" option.

Step 2 − Now, you can select the B4 cell checkbox and press the "delete" key to delete the checkbox.

Therefore, the checkbox we inserted in the B4 cell has been deleted.

Linking a Checkbox to the Adjacent Cell

Step 1 − First, you can select checkbox B2, right-click on it, and select the Form Control option from the drop-down menu.

Another "Format Control" Window will open. You can select the Control tab, type B2 in front of the "Cell link:" label, and finally click on the OK button. Here, the B2 cell is linked to the checkbox.

After that, depending upon your choice True or False will be populated when you select/deselect the checkbox.

Therefore, the checkbox is unticked in the B2 cell, so the "FALSE" is showcased in this cell. You can interpret that the Animesh patient doesn’t have Diabetes.

Step 2 − Repeat the same step for the remaining cells and specify the corresponding cell references in the Cell link section, such as B3, B4, B5, B6, and B7. If you select the checkbox, TRUE is populated in the cell; otherwise, FALSE is showcased. In this example, we can interpret that Mehak, Anant, and Dhruv are diabetes patients.

Excel Checkbox Formula

The formula helps us identify the checkbox's current status. You may enter the formula =IF(B2,"Checked", "Unchecked") in the D2 cell and press Enter. Here, the B2 checkbox is not selected.

Therefore, the Unchecked message will be returned.

Similarly, enter the formula =IF(B3,"Checked", "Unchecked") in the D3 cell.

As you can observe in the screenshot, the checkbox is selected in the B3 cell. Therefore, the IF formula will return the CHECKED.

Excel Checkbox Conditional Formatting

The task is to apply the conditional formatting to the checkbox. If you select the Excel checkbox, the color of the Patient's Name will be highlighted. Otherwise, it will remain the same.

Step 1 − First, select the range A2:A7, then go to the Home tab, expand the "Conditional Formatting" option under the "Styles" group, and select the New Rule from the drop down list.

Step 2 − Another dialog box, "New Formatting Rule," will appear. First, select the "Use a formula to determine which cells to format" option in the "Select a Rule Type:" section. After that, you can type the formula "=$B2=True" in the "Format values where this formula is true:" section. Then, click on the Format button.

Step 3 In the Format Cells window, navigate to the Fill tab, choose Green from the given color list, and then click the OK button.

Step 4 − Finally, click the OK button.

Therefore, the patient names are highlighted if the checkbox status is True, done through the Checkbox conditional formatting.