New Checkboxes in Excel

Bottom Line: Take a look at a new feature that allows you to insert checkboxes in cells, and how to use them with conditional formatting, formulas, and more.

Skill Level: Beginner

Watch the Tutorial

Download the Excel File

You can use the same workbook that I use in the video by downloading this file:

A Long-Awaited Feature

I'm excited to announce Excel's new feature that allows you to insert a checkbox into any cell quickly and easily. This is a capability that Google Sheets has had for some time.

I've heard people say that they will choose Sheets over Excel when their project includes checklists, so it's great that we no longer have to factor that into the decision on which app to use.

The new checkboxes feature in Excel is simple to use, and I think it will be popular among Excel users. It also has a “ghosted” feature that might make it better than the checkboxes in Google Sheets.

Availability

Currently, the new checkboxes feature is only available for a portion of the Beta channel of Microsoft 365, but hopefully, it will roll out to the masses soon. Be sure to subscribe to our YouTube feed to receive an update when that happens.

Add a Checkbox

The process for inserting a checkbox is really easy. Select the cell where you want to insert it, go to the Insert tab, and click the new option that says Checkbox. If you have multiple cells selected, they will each receive a checkbox.

When you add a checkbox, it is unchecked by default, and the value of the cell is FALSE. When you check the box, the value changes to TRUE.

And you don't necessarily have to click the checkbox with your mouse to check or uncheck the box. The Spacebar on your keyboard acts as a toggle to check or uncheck it.

Use Checkboxes in Formulas

Aside from simple checklists like the one pictured above, there are other great uses for this new feature. For example, you can include the use of checkboxes for formulas so that if a checkbox is checked, it will return a different outcome than when it's unchecked. In the data below, if the box in the Split column is checked, the Amount will be divided in half using the IF Function. (Check out this tutorial for more information on the IF Function.)

Of course, this is just a simple formula, but you could make more complex formulas that alter the outcome based on a checkbox.

This ability can make your worksheets more interactive and your users don't have to worry about the math behind the formulas. They can simply check a box and get the results they need.

Formatting Checkboxes

You can change the look of a checkbox using the same controls you would use for font. Make the checkboxes a different color or size, or change the alignment within the cell using the formatting options found on the Home tab.

Conditional Formatting of Checkboxes

You can also make the formatting of the checkboxes conditional.

In this example, when I click a checkbox, it turns red. To make this happen, I've selected my checkboxes and clicked on Conditional Formatting on the Home tab. Then I went to Highlight Cells Rules, then Equal To.

Then I typed the word “true,” chose the Red Text option, and hit OK.

Now, whenever I check the checkbox, the value turns to TRUE, and the checkbox instantly becomes red.

You can also use a checkbox with conditional formatting to change the look of other cells. For example, items that are checked in the list below appear grey in color. Watch the video if you're interested in a step-by-step explanation of how that's accomplished.

Here's an example with more advanced conditional formatting, highlighting the next task in line:

This tutorial explains how to do that.

Other Ways to Use Checkboxes

Here's a quick rundown of other ways to use checkboxes.

An Inventory List

As items in inventory are counted, they can be checked off the list.

A Comprehensive Checklist

Multi-step lists benefit from checkboxes just as much as simple lists.

A Calendar Chart

You can link your checklist to a calendar chart so that days on the calendar are highlighted when you've checked the corresponding date in the checklist.

Here's how to how to create a calendar chart.

Interactive Calendar

You can even use conditional formatting so that when a weekly goal is met, it highlights the entire week, or in the case below, it activates a donut emoji to signify a reward is earned.

For help on how to enhance a calendar chart to be more interactive, you can check out this tutorial: Interactive Calendar Chart with Weekly Goals.

Attendance Tracker

Take attendance by clicking the button when someone is present.

The Ghosted State of Checkboxes 👻

In my opinion, Excel's new checkbox feature is better than the checkboxes you find in Googe Sheets because Excel offers a third state for checkboxes. In addition to being checked or unchecked, the boxes can also be ghosted. That means they are invisible until you hover over them.

How do you place checkboxes into a ghosted state? Simply delete them. The value of those cells will be empty, but if you hover over a ghost checkbox, you will see it in grey, and you can click on it to resurrect it and then check the box. Click it again to uncheck it. Or delete it again to make it a ghost.

If you want to remove the ghost boxes completely, you can choose the Clear Formats option on the Home tab.

Totaling Checked Checkboxes

A common question regarding checkboxes is how to count or total the number of checkboxes that are checked.

A simple sum formula will not work, since it's trying to add up TRUE and FALSE values. To fix this, we multiply the values by 1. That will return a 1 for every TRUE and 0 for every FALSE.

Google Sheets allows you to change the TRUE and FALSE values to 1 and 0, so that's one area where it has a small advantage over Excel when it comes to checkboxes.

Comparing New Checkboxes with Form and ActiveX Checkboxes

Prior to this capability, you could create checkboxes using Form or ActiveX controls. There are nuances between the two that I won't go into, but the process of creating checklists was much more involved. You would insert each checkbox from the Developer tab, and they would not be associated with a particular cell but would float on top of the sheet. You would then link the checkbox to a cell so that you could work with a true/false value from there.

One instance where you may want to use the old Form/ActiveX controls is if the floating format works to your advantage. For example, if you are moving lots of components around on a dashboard, a checkbox that isn't anchored in a particular cell might be helpful.

If you don't want to wait for the new Checkbox feature to be released and you'd like to create a form using Form/ActiveX controls, this tutorial will show you how: Enhance Your Checkboxes with Conditional Formatting in Excel

Keyboard Shortcut

As of right now, there isn't a dedicated keyboard shortcut for this new feature. That may change before they roll it out to more channels. But one alternative is to add the button to the Quick Access Toolbar (QAT), and then you'll be able to use the Alt command (plus whatever number you've positioned it in the QAT) to quickly add checkboxes.

More Potential Uses

I asked ChatGPT for help finding more inspiration for checkbox uses, and together we came up with these 20 ideas. They can also be found in the Excel file attached at the top of the tutorial.

Do you see yourself using any of those ideas? What else might you use checkboxes for? Leave a comment and let me know your thoughts and suggestions for this new feature!

Conclusion

In summary, checkboxes make our files interactive and easier to use, and they're also a lot of fun! This new way of adding checkboxes is much easier compared to the old Form/ActiveX controls.

When combined with conditional formatting or integrated into formulas, checkboxes can open up a lot of possibilities for creating useful spreadsheet-based applications. I'm looking forward to hearing how you'll put them to work!