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

Excel Update: Checkboxes in cells!

Watch the Tutorial

Watch on YouTube & Subscribe to our Channel

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.)

Expense Report with Checkboxes

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.

Home Tab Formatting Checklists

Conditional Formatting of Checkboxes

You can also make the formatting of the checkboxes conditional.

Christmas Gift Checklist

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.

Conditional Formatting Menu

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

Equal To Window

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.

Conditional formatting with checklists

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

Simple Lsit with advanced formatting

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.

Inventory list

A Comprehensive Checklist

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

HR Onboarding checklist

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.

Calendar Chart with Checklists

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.

Absence Tracker

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.

Attendance Tracker

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.

Checkboxes with Form or ActiveX controls

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.

Checkboxes in the Quick Access Toolbar

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.

ChatGPT idea list for checkboxes
Click to enlarge

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!

17 comments

Your email address will not be published. Required fields are marked *

  • Posted this comment (Excelambda) but YT did not show it:
    Great Video!!
    I think that the checkboxes were designed On Purpose to be able to manage tri state system representations, not only binary. So, the “deleted” or “ghost” state is part of the design not only for appearance. There are 3 states: ghost(deleted) -> checked -> unchecked.
    Examples of tri state logic reasoning being more versatile than simple binary systems:
    Maybe, Yes, No
    Non binary, Male, Female
    Defective, On, Off
    Not interviewed, Pro, Contra
    Leveled, Up, Down
    Not arrived, Arrived, Left
    Uncertain, Positive, Negative
    Not yet, Passed, Failed
    And so on…..
    Here is a simple function to monitor these states for a checkboxes range:
    YNM(r,y,n,m) YesNoMaybe function

    =LAMBDA(r,y,n,m,IFS(ISBLANK(r),m,r=TRUE,y,r=FALSE,n))

    where r,range and y,n,m are the values we want to show when the checkbox is in it’s respective state: checked (y), unchecked (n), “deleted” or “ghost” (m)
    example : list of students attending an exam (ghost- > not attended yet; checked -> exam attended and passed; unchecked -> attended and failed
    =YNM(B2:B17,”passed”,”failed”,”not attended”)
    Note: The hovering behavior is very welcomed because this certifies the check box still there but in a “maybe” state (ghost or deleted state)

  • Nice to know there is now a way to officially do this in Excel, although I have been using them for years by formatting the cell with the Font: Marlett and when you type an “a” it looks like a checkmark. Then I can count the number of “a’s” or us conditional formatting if the cell equals “a”
    That has been my workaround for a very long time, so it is nice to see it will now look more official and have better functionality. Thank you for sharing the upcoming feature, and look forward to it coming soon outside of Beta.

    • Haha yes! I feel the same way. I didn’t think I’d be this excited about checkboxes, but they have been a lot of fun to play with and create solutions. I’m looking forward to seeing how you use them in your templates at Vertex42.

  • I am very excited to check this out. Checkboxes without the programming overhead should really make it big.
    I don’t have the tool on my Insert tab. When should I expect to find it there? (Office 365)
    Thanks!

    • Hi Jesse,
      I’m not sure on when it will be pushed out of beta. It’s currently only available to a portion of beta users. See the Availability section above for more info.
      Thanks! 🙂

  • I don’t have the feature yet but I’m considering swapping my Form-based checkboxes for this feature to select months for a couple of annual charts I have going. Definitely easier to configure and no alignment issues 🙂

  • Not sure why but when i refer to the value of cell with a checkbox (TRUE or FALSE), vba isnt recognizing it as a worksheet change event.

  • Every time I try to download the files, the download as xls, not xlsx. Does not give me any other option. This is the weirdest thing I’ve ever seen. I have 365 and all my other files are fine. Not sure what the heck is going on

  • Hello, any word on what happens to the checkbox if the row it is in is hidden? I’ve had plenty fun with that over the years 🙂

  • Any idea if these new checkboxes will work on Excel online (i.e., when opening an Excel file in Sharepoint without having to open it in the desktop application)?

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter

Free Excel Training Webinar Modern Power Tools