Adding a checkbox in Microsoft Excel
To add a
checkbox (check box, tickbox, tick box) column in
Microsoft Excel, you will need to first enable the developer ribbon
in Excel, if it is not already enabled. To do so, take the following steps in
Excel (the steps were written for Excel 2010 and 2013, but may be similar for
other versions):
-
Click on File and select Options.
-
Click on Customize Ribbon, then click on the checkbox next to
Developer on the right-side of the window in the Main Tabs
section, and then click on OK.
-
Click on the Developer tab that will then appear at the top of the
Excel window.
-
On the Developer tab, click on the Insert button, which
appears to the right of COM Add-ins and beneath Data.
Click on the check box button that appears in the Form Controls
section.
-
Click in the cell where you wish the checkbox to appear. A rectangle will
appear with a checkbox within it and text, e.g., "Check Box 1", to the right
of the checkbox.
You can position and size the rectangle within the cell using
the mouse. You can delete the text, if you wish, by clicking on the text and
then using the cursor keys and backspace to remove the text. Or you can replace
the text with other text, if you prefer.
-
When you are satisfied you have the checkbox positioned in an appropriate
cell and positioned appropriately within that cell, you can click on another
cell to finalize the placement of the checkbox. If you then would like to
copy the checkbox to other cells, you can click on the bottom, right-hand
corner of the cell in which you have placed the checkbox and then drag
downwards with the mouse to copy the checkbox into other cells beneath the
current one.
You can then click in a checkbox to put a
checkmark (check mark, tick) in the box.
You can also change the value in some other cell to be TRUE or FALSE based
on whether the checkbox is checked. E.g., suppose,
Dexter is tracking his
bucket list in an Excel spreadsheet.
Dexter has 10 items on
his bucket list and there's a Completed column (column B) and
a Done column that holds the checkboxes. If I wanted to have "TRUE"
placed in a cell in column B if the checkbox is checked and "FALSE" placed
there if the box is unchecked, I could take the following steps:
-
Right-click on the first checkbox and choose Format Control.
-
In the Format Control window, you will see
radio
buttons for checked, unchecked, and mixed.
If the checkbox I've selected is in cell C2 and I want cell B2 to be
set to "TRUE", I can select "Checked" and put B2 in the
Cell link field.
-
When I click on OK, I would then see
TRUE appear in
cell B2, if the cell C2 checkbox is checked.
If I uncheck the check box, the value will change to False.
I can then follow a similar procedure for the other checkboxes.
Granted in the simple example above, there might not be much value in
having "TRUE" for a cell in a Completed column right next to a
checked checkbox, but you could use the same technique to change the value
of a cell in another sheet, e.g., I could use Sheet2!A2 for the
cell reference to change a cell's value in Sheet2 rather than the current
sheet, Sheet1 in this example. Note: if you change the cell reference, the
value stored in a referenced cell won't automatically go away. It will
remain there until you manually remove it.
You could use the value set in the "cell link" cell to perform some
calculation. E.g., if I set B2 to be TRUE if cell C2 is
checked, I could check the value of B2 to determine if it is "TRUE" or
"FALSE" in a formula elsewhere in the workbook.
