Checkboxes

The goal of this tutorial is to learn how to insert checkboxes in Microsoft Excel using two different methods: checkbox cell controls and legacy form controls. We will also explore how to use checkboxes in formulas, conditional formatting, and how to activate the developer tab for legacy form controls.

Video

Step-by-step

Exercise 1: Inserting Checkboxes using Checkbox Cell Controls

Imagine you want to track the status of some employees during their onboarding process. Specifically, you want to track if they have completed their W-4, I-9, and HMO forms.

So, you quickly put together a worksheet like this:

To insert the checkbox, select the cell (or cells) where you want to insert the checkbox. In this case, we’ll select C11:

Go to the Insert tab, navigate to the Cell Controls group, and select the Checkbox command:

Note: the Checkbox Cell Control is NOT available in all Excel versions. At the time of this writing, it is rolling out to Excel 365 users.

A checkbox will now appear in the selected cell:

Yay! That was easy 🙂

Now, we need to understand that the underlying stored value of the checkbox is a Boolean TRUE/FALSE value (checked or unchecked, respectively).

To confirm this, check the checkbox and observe that the stored value shown in the formula bar is TRUE:

Uncheck the checkbox and the stored value is FALSE:

We can easily fill the checkboxes down and right:

Since the stored values are Boolean TRUE/FALSE values, we can reference them in formulas.

For example, we can write a formula to check if all the checkboxes are checked.

In the Ready column, cell F11, we can write the following:

=AND(C11:E11)

We fill this formula down through the Ready column.

Now, when all boxes for an employee are checked, the formula shows TRUE. Otherwise, it shows FALSE.

What is interesting is that since the formula results are also Boolean TRUE/FALSE values, we can actually create checkboxes in those cells as well. We select the formula cells and Insert > Checkbox:

Since they are formulas, Excel doesn’t let the user check them. They are checked/unchecked automatically based on the formula results.

Exercise 2: Applying Conditional Formatting to Checkboxes

In this exercise, let’s assume you have a list of projects and their responsible employees. You want to mark the project as complete by checking a checkbox.

So, you enter the project information in the worksheet, including inserting checkboxes into the completion status column, like this:

It is easy to check the Complete checkbox for any project. But … we want to use conditional formatting to highlight the projects.

We could highlight just the Complete column or we could highlight the entire row. The set up is slightly different depending on the option, so we’ll take them one at a time.

First, let’s highlight just the Complete column. Begin by selecting the Complete column, and then navigate to Home > Conditional Formatting > Highlight Cell Rules > Equal To. In the resulting dialog, enter TRUE if you want to highlight the checked boxes and select your formatting:

Click OK, and bam:

On the other hand, if you want to highlight all columns, and not just the Complete column, begin by selecting the range, like this:

Navigate to Home > Conditional Formatting > New Rule, and select Use a formula to determine which cells to format in the resulting dialog. Enter a formula that points to the first checkbox cell using an absolute column relative row reference, like =$D10:

After selecting your desired Format, click OK and apply the formatting and bam:

But … what if your version of Excel doesn’t have the Insert > Cell Controls > Checkbox command? Well, no problem, you can still use the legacy form controls checkbox. Let’s cover that next.

Exercise 3: Inserting Checkboxes using Legacy Form Controls

If your version of Excel doesn’t have the Insert > Cell Controls > Checkbox command, you can use legacy form controls instead.

First, we need to activate the Developer tab so it appears in the Excel ribbon. To do so:

• Go to the File tab
• Select Options
• Click on Customize Ribbon
• Check the box next to Developer
• Click OK

Navigate to Developer > Insert > Form Controls, and select the Checkbox from the list of controls:

Now, click anywhere on the worksheet to add the checkbox.

Note that this type of checkbox floats above the grid in the drawing layer, so you can click and drag to place it anywhere.

So, what happens when you check the checkbox? Nothing … until you link it to a specific cell. To do so, right-click the checkbox and select Format Control. In the result dialog, specify which cell should be updated when the user checks or unchecks the box:

Click OK to close the dialog. And now, the specified cell is updated when you check the checkox:

Note: if you change the value in the cell manually, Excel will update the checkbox accordingly.

You can also change or remove the label next to the checkbox by right-clicking it and selecting Edit Text.

You can use this linked cell in Excel formulas or apply conditional formatting, similar to what was done with the checkbox cell controls.

Conclusion

Now that you’ve learned how to insert checkboxes, use them in formulas and conditional formatting, and how to activate legacy form controls, you can easily track data, create interactive worksheets, and enhance your Excel projects.

If you have any alternatives, improvements, or questions … please share by posting a comment below … thanks!

Sample File

Frequently Asked Questions

Can I insert checkboxes in older versions of Excel?

Yes, while the Cell Controls Checkbox command is available starting with Excel 365, the legacy form controls have been available for decades. Just remember to turn on the Developer tab as shown in Exercise 3 above.

How can I use checkboxes in formulas?

You can use checkboxes to supply Boolean values (true or false) to formulas and function arguments. For example, you can use the AND function to check if all checkboxes in a range are checked, or you can use checkboxes as arguments in other formulas that require Boolean values.

Can I apply conditional formatting to checkboxes?

Yes, you can apply conditional formatting to cells with checkboxes. You can format the cells based on the true or false value of the checkboxes. Additionally, you can apply conditional formatting to entire rows based on the checkbox status using relative row and absolute column cell references in the conditional formatting formula as demonstrated in Exercise 2.

What if I can’t find the Insert > Cell Controls > Checkbox command?

If this command is not available in your version of Excel, you can activate the Developer tab and use legacy form controls to insert checkboxes, as shown in Exercise 3.

Can I use checkboxes in other features of Excel, such as filtering?

Yes, you can use checkboxes in various Excel features, including filtering and other data manipulation tasks. For example, you can filter a list to show only the rows where checkboxes are checked (TRUE) or unchecked (FALSE). To do so, select any cell in the range and use the Data > Filter command. Select the dropdown from header of the column that contains checkboxes.

Can I link Form Control Checkboxes to other cells in the workbook

While Form Control Checkboxes support a single Cell Link reference, you can write formulas in other cells to copy or reference that value. For example, if the checkbox cell link is F10, you can write formulas in any other cells that retrieve the value in F10 by using a simple formula like =F10. That way, one checkbox can update values in many cells if needed.

How can I count the number of checked checkboxes in Excel?

To count the number of checked checkboxes in Excel, you can use the COUNTIF function. Assuming the checkboxes are in the range A1:A10, you can use the formula =COUNTIF(A1:A10,TRUE) to count the number of checkboxes that are checked (returning a TRUE value).

Can I use checkboxes to create interactive dashboards in Excel?

Absolutely! Checkboxes can be used to create interactive dashboards in Excel. By linking checkboxes to certain actions or calculations, you can dynamically update the display of data or perform specific tasks based on checkbox selections. For example, you can show or hide specific charts or tables depending on the checkboxes that are checked or unchecked.

Remember, checkboxes are versatile tools in Excel that offer various possibilities for managing and manipulating data effectively.