Excel Checkbox (Insert, Format & Remove)

In this article, we will look at a variety of checkbox-related topics in Excel.

By reading this article, you will gain several benefits. First, you will learn how to add checkboxes in Excel without using the Developer tab, which will help you overcome any technical difficulties. Second, you will find out how to format checkboxes according to your preferences, making them visually appealing and easy to use.

Additionally, you will discover how to link checkboxes to specific cells, which will automate processes and improve the organization of data. Lastly, we will explore using conditional formatting with checkboxes to visualize data and make informed decisions.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

How to Add a Checkbox in Excel

Here, we will demonstrate how to add a checkbox in Excel.

• First of all, you need to have the Developer tab showing on your ribbon.
• Go to the Developer tab and click on the Insert option to select the Check Box from the Form Controls.

How to Add Check Boxes without Developer Tab in Excel

In this method, we will not use the Developer tab.

• Firstly, press Alt + F11 to open the Microsoft Visual Basic for Applications window.
• Secondly, go to the Insert tab and select Module.
• Enter the code in the Module that appears:

Sub Add_Checkbox()
Dim x As Range
Dim y As checkbox
Dim yRange As Range
On Error Resume Next
Set yRange = Application.InputBox(Prompt:="Select range", Type:=8)
If Err.Number <> 0 Then Exit Sub
On Error GoTo 0
For Each x In yRange
Set y = yRange.Parent.CheckBoxes.Add(0, 1, 1, 0)
With y
.Top = x.Top + x.Height / 2 - y.Height / 2
.Left = x.Left + x.Width / 2 - y.Width / 2
.Locked = False
.Caption = ""
.Name = x.Address
End With
Next x
End Sub

• Press Ctrl + S to save the code.
• After that, press Alt + F8 to open the Macro window.
• Run the code by selecting it from the Macro window.

• After running the code, an input box will appear.
• You need to select the range where you want to add the checkboxes.

• This is what the finalized table looks like.

How to Format Checkbox in Excel

Resizing a Checkbox

You can resize it to a specific height and width by using the procedures listed below.

• Select the Format Control option from the context menu when you right-click on the checkbox.
• Select Size and Rotate under the Size option.
• To fix the checkbox’s height and width, enter the values.

Renaming a Checkbox

First, right-click, choose Edit Text, and then type new text after deleting the default text.

Fixing the Position of a Checkbox

You can fix a checkbox position using the following steps:

• First, right-click on the checkbox, go to Format Control, and select the Properties Tab.
• Select Don’t move or size with cells under the object’s positioning.
• After selecting this option, the checkbox will remain fixed regardless of column or row expansion.

Hide or Unhide a Checkbox

You can hide or unhide a checkbox by using the following steps:

• To access the selection pane, utilize the shortcut key Alt + F10.
• A small eye icon is situated to the right of each checkbox label.
• Click on the icon to hide a checkbox, and click it again to restore its visibility.

How to Link a Checkbox to a Cell in Excel

Here, we will demonstrate how to link a checkbox to a cell in Excel.

• Initially, right-click over the checkbox and choose the Format Control option from the Context Menu.
• Type or select the cell ($D$5) in the space of the Cell Link option from the Control tab where you want to link with the checkbox.
• The default value of Checked is TRUE, and FALSE is for Unchecked.
• Click on OK.

• As a result, the D5 cell is connected to the C5 cell’s checkbox. For instance, if you check the box, TRUE will soon appear in the D5 cell.

How to Use Conditional Formatting with Checkboxes in Excel

Here, we will demonstrate how to format Excel cells with strikethrough using checkboxes.

• Firstly, select the cell that has the home office checklist and navigate to the Home tab as in the image below.
• Under Conditional Formatting, click on New Rule.

• Enter the cell reference that you inserted earlier in the new window and choose Use a formula to determine which cells to format.
• Click on Format.

• Check the Strikethrough box under the Font tab.
• Click OK after viewing the preview.

• Accept the preview by clicking OK.
• The value in cell B5 should be highlighted when the checkbox in cell C5 is selected.
• Similarly, you can follow the above steps for the other checkboxes.

Examples of Using Checkboxes in Excel

1. How to Create an Interactive Checklist in Excel

Utilizing the Conditional Formatting tool, we create an interactive checklist in this section.

2. Add Checkbox to Create an Interactive Checklist with Summary

In this example, we use checkboxes in Excel to create an interactive checklist with a summary.

How to Print a Checkbox in Excel

To enable the print option, follow these steps:

• First, right-click on the checkbox, choose Format Control, and go to the Properties Tab.
• Put a checkmark in the Print Object option.

How to Delete Checkbox in Excel

There are two methods to delete a checkbox. The first method is to select a checkbox and press the delete key. The second method involves using the selection pane to delete them.

• To access the selection pane, navigate to the Home Tab, click on Editing, then Find & Select, and finally, Selection Pane.
• Inside the selection pane, you will find a list of all the checkboxes used in your worksheet. You can select them individually or select multiple checkboxes simultaneously by holding down the control key.
• Once you have made your selection, simply press the delete key to remove them.

Things to Remember

• When adding a checkbox to a worksheet or linking multiple checkboxes, we need to keep certain things in mind.

1. To insert checkboxes in your worksheet, you must enable the Developer tab.
2. When we have dragged the Fill Handle down to add the checkboxes. In this case, double-clicking on the Fill Handle will not work.
3. To delete a checkbox, you need to put the cursor on the checkbox, right-click, and then press Delete from the keyboard.

• It should be noted that whenever working with VBA code you must save the file as Excel Macro-Enabled Workbook. Otherwise, the file will not work.

Frequently Asked Questions

1. How do I change the size of a checkbox in Excel?

To change the size of a checkbox in Excel, right-click on the checkbox and select Format Control. You can adjust the height and width settings in the Format Control dialog box to resize the checkbox.

2. How can I count the number of checked or unchecked checkboxes in Excel?

To count the number of checked or unchecked checkboxes in Excel, you can use formulas such as COUNTIF or SUMPRODUCT and the cell range containing the linked checkbox values. For example, to count the number of checked checkboxes, you can use the formula: =COUNTIF(A1:A10, TRUE), assuming the checkboxes are linked to cells A1 to A10.

3. Can I customize the appearance of a checkbox in Excel?

Yes, you can customize the appearance of a checkbox in Excel. Right-click on the checkbox and select Format Control. In the Format Control dialog box, you can modify various settings such as size, color, font, and more to customize the appearance of the checkbox.

Conclusion

In conclusion, Excel’s checkboxes are a useful feature that let users make dynamic and interactive spreadsheets. They offer a visual representation of binary options like true/false or checked/unchecked choices.

Users can easily manipulate data and take different actions based on the checkbox state by connecting checkboxes to cells. Checkboxes in Excel provide flexibility and versatility for a variety of tasks, including the creation of forms, surveys, and conditional formatting.

The availability of customization options raises the checkboxes’ aesthetic appeal and usability.

Excel Checkbox: Knowledge Hub

<< Go Back to Form Control in Excel | Learn Excel