- Got Sheet
- Posts
- Change Excel Checkbox Values
In partnership with
In today’s email:
Setting up checkboxes in Excel
What values they can hold
How to customize the values
Use in a grade sheet
TRIVIA
What was Excel's original code name during development? |
MAIN ARTICLE
Video Walkthrough
Checkboxes in Excel
We’ve had access to checkboxes in Google Sheets for years, but Excel finally caught up last year.
Previously, you’d have to use form controls to add some clunky checkboxes in Excel. It worked, but it wasn’t a great experience.
Now, though, all it takes is a quick trip to the insert tab of the ribbon and then you select checkbox.
What Does it Mean?
A checkbox, by default, simply holds the value TRUE, if it’s checked, or FALSE if it’s not checked.
It can be used for a lot of things and provides a simple way to add helpful interactivity to your sheet.
By tying in conditional formulas, you can toggle on or off text or conditional formatting that is tied to a checkbox.
A simple example would be a conditional formatting rule that applied orange fill when the condition =F9 was met. Since the checkbox is in F9, it will be TRUE when checked, causing the condition to be met.
Other Values for Excel Checkbox
What if we want it to be another value, though? In Google Sheets, we can actually natively change this from TRUE and FALSE to a custom value in the data validation menu.
In Excel, this feature is not built in.
But we can still manipulate our results to the same effect. See the example below.
If we have a grade book where we want to give 50 points per checkbox, a simple formula will do the job nicely in our total column.
We simply count up all the checkboxes that are checked using a COUNTIF function and then multiply the result by 50:
=COUNTIF(B2:F2,"TRUE")*50 I did even more to the Excel sheet in our video example, check out the full tutorial here (4.5 minutes).
NEXT STEPS
Whenever you’re ready, here’s how I can help:
Business tech stack (FREE)
My recommendations for software and tools I use to create content and run my online business.Sponsor Got Sheet
Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.Personal budget tool
As a Got Sheet subscriber, I want you to have a 75% discount on the personal budget I built 10+ years ago and am still using to this day.YouTube
If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials
Reply