Microsoft Excel Tutorial: Checkboxes arrive in Microsoft Excel
To download the workbook from today: Microsoft Excel Tutorial: Checkboxes arrive in Microsoft Excel Sample Files - MrExcel Publishing
In this YouTube video, Bill Jelen enthusiastically announces the arrival of checkboxes in Microsoft Excel. They reminisce about the challenges they faced with checkboxes in the past, particularly when working on a report card system for a school district.
Jelen demonstrates how to use the new checkbox feature, emphasizing its location on the "Insert" tab. They show how to insert checkboxes into cells, toggle them on and off, and even use the space bar for quick toggling. The video also explores the underlying true or false values associated with checkboxes and how to remove them to revert to regular true and false values.
Some limitations are discussed, such as the inability to use checkboxes in data validation or within pivot tables. The video also highlights a peculiar behavior where hovering near existing checkboxes seems to automatically insert and activate new ones, though the exact rules for this behavior remain unclear.
The speaker attempts to address the question of displaying words next to checkboxes and mentions that some methods like number formatting didn't seem to work, inviting viewers to share their insights in the comments.
The video concludes by noting that if someone without the feature opens a workbook containing checkboxes, they will see the values as true and false. The speaker expresses gratitude to the Excel team for the feature and thanks viewers for watching, leaving them with the promise of more content in the future.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
Table of Contents
(0:00) Walkthrough: Checkboxes in Excel
(0:22) Checkbox icon on Insert Tab
(0:40) Spacebar or mouse to Toggle checkbox
(0:48) Stores True/False in the cell
(0:58) Clear formats to remove checkbox but leave True/False
(1:13) Convert formula results to checkboxes
(1:30) Use conditional formatting to change color
(1:50) Validation drop-down does not show checkbox
(2:09) Will not work in pivot table
(2:40) Empty cells near checkboxes have interesting feature
(3:23) Adding a word next to checkbox?
(3:33) If workbook opened on Excel without feature - True/False
(3:56) Clicking Like really helps the algorithm
To download the workbook from today: Microsoft Excel Tutorial: Checkboxes arrive in Microsoft Excel Sample Files - MrExcel Publishing
In this YouTube video, Bill Jelen enthusiastically announces the arrival of checkboxes in Microsoft Excel. They reminisce about the challenges they faced with checkboxes in the past, particularly when working on a report card system for a school district.
Jelen demonstrates how to use the new checkbox feature, emphasizing its location on the "Insert" tab. They show how to insert checkboxes into cells, toggle them on and off, and even use the space bar for quick toggling. The video also explores the underlying true or false values associated with checkboxes and how to remove them to revert to regular true and false values.
Some limitations are discussed, such as the inability to use checkboxes in data validation or within pivot tables. The video also highlights a peculiar behavior where hovering near existing checkboxes seems to automatically insert and activate new ones, though the exact rules for this behavior remain unclear.
The speaker attempts to address the question of displaying words next to checkboxes and mentions that some methods like number formatting didn't seem to work, inviting viewers to share their insights in the comments.
The video concludes by noting that if someone without the feature opens a workbook containing checkboxes, they will see the values as true and false. The speaker expresses gratitude to the Excel team for the feature and thanks viewers for watching, leaving them with the promise of more content in the future.
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
Table of Contents
(0:00) Walkthrough: Checkboxes in Excel
(0:22) Checkbox icon on Insert Tab
(0:40) Spacebar or mouse to Toggle checkbox
(0:48) Stores True/False in the cell
(0:58) Clear formats to remove checkbox but leave True/False
(1:13) Convert formula results to checkboxes
(1:30) Use conditional formatting to change color
(1:50) Validation drop-down does not show checkbox
(2:09) Will not work in pivot table
(2:40) Empty cells near checkboxes have interesting feature
(3:23) Adding a word next to checkbox?
(3:33) If workbook opened on Excel without feature - True/False
(3:56) Clicking Like really helps the algorithm
Transcript of the video:
Hey, what a cool day. Checkboxes have arrived in Microsoft Excel.
Decades ago, one of my very first paid consultant gigs was a report card system for a school district.
And they needed hundreds of checkboxes per report card, and it was so hard back then.
Now it will be super easy. Let's take a look.
Oh yeah, well, it is really awesome.
It's found here on the insert tab, all the way back on the right-hand side. Insiders Fast, not everyone has it, probably 50%.
Just to the left of text box, you have this checkbox.
And so if we just select a bunch of cells here, and turn on the checkbox, and we get these little items, and we can either turn them on or off.
You can also use the space bar, so space, space, space, toggles them.
If I select those, I can multi-select, and turn them all off.
If we want to see what's really behind there, look up in the formula bar, it's just true or false.
And in fact, we can actually get rid of the checkbox on the home tab under clear formats, and it just goes back to a regular true and false.
So here I have date, quota sales, and whether we met it or not, so C5 greater than or equal to B5.
I can change that whole range into a series of checkboxes by selecting it, and doing checkboxes.
You can also do something cool, let's make all of the checkboxes be red, and then under conditional formatting, new rule, format only cells that contain, and if it's equal to true, then we'll change the font color to green. Click okay, click okay, click okay.
So you can do a little bit of formatting there. I love it.
There are a few things that do not work like I think they should.
If we put it in data validation, so over here, I created two cells, one yes, one no, one true, one false, when you open the validation, you don't actually get to choose from the checkbox list.
I also tried creating a pivot table here, and there's nothing I can do to get that word false to change to a checkbox.
See, it's grayed out, and I tried the old trick of selecting a bunch of cells, and only one of them is in the pivot table, it still won't let me do it.
I even made this into a checkbox, and then moved the pivot table over to see if it would pick it up, and it doesn't.
So that's just not quite there. Maybe they'll do that later, who knows?
You're just getting trues and falses there, and no ability to update it.
The other thing that's kind of unusual here is, if you have a series of trues and falses, and you click somewhere near it just to hover, it seems to insert a checkbox and turn it on automatically.
I don't know what is going on here.
If I come down into this section down here, it's not there. So it has to be near an existing checkbox.
I haven't quite figured out what the rules are for that, like why and where, it just probably has to be in this range, and it's strange... Let's get rid of these.
So now we have some checkboxes here. Yeah, I don't know.
Clearly it's designed to be a feature, I just can't figure out exactly what it's doing.
Also, what if we want to display a word next to the checkbox?
I tried some old tricks with number formatting, and that doesn't seem to be working.
If anyone figures that out, let me know down in the YouTube comments.
And finally, what happens if you send this to someone who does not yet have the feature?
All the checkboxes just changed back to true and false.
My left computer has the feature, this computer does not have the feature, so when I opened the workbook here, it all just changed.
I know you'd want to download this to see if you could get checkboxes, but if you don't have it up in the insert tab, you're just going to get trues and falses.
All right, first off, thanks to the Excel team for this great feature, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Decades ago, one of my very first paid consultant gigs was a report card system for a school district.
And they needed hundreds of checkboxes per report card, and it was so hard back then.
Now it will be super easy. Let's take a look.
Oh yeah, well, it is really awesome.
It's found here on the insert tab, all the way back on the right-hand side. Insiders Fast, not everyone has it, probably 50%.
Just to the left of text box, you have this checkbox.
And so if we just select a bunch of cells here, and turn on the checkbox, and we get these little items, and we can either turn them on or off.
You can also use the space bar, so space, space, space, toggles them.
If I select those, I can multi-select, and turn them all off.
If we want to see what's really behind there, look up in the formula bar, it's just true or false.
And in fact, we can actually get rid of the checkbox on the home tab under clear formats, and it just goes back to a regular true and false.
So here I have date, quota sales, and whether we met it or not, so C5 greater than or equal to B5.
I can change that whole range into a series of checkboxes by selecting it, and doing checkboxes.
You can also do something cool, let's make all of the checkboxes be red, and then under conditional formatting, new rule, format only cells that contain, and if it's equal to true, then we'll change the font color to green. Click okay, click okay, click okay.
So you can do a little bit of formatting there. I love it.
There are a few things that do not work like I think they should.
If we put it in data validation, so over here, I created two cells, one yes, one no, one true, one false, when you open the validation, you don't actually get to choose from the checkbox list.
I also tried creating a pivot table here, and there's nothing I can do to get that word false to change to a checkbox.
See, it's grayed out, and I tried the old trick of selecting a bunch of cells, and only one of them is in the pivot table, it still won't let me do it.
I even made this into a checkbox, and then moved the pivot table over to see if it would pick it up, and it doesn't.
So that's just not quite there. Maybe they'll do that later, who knows?
You're just getting trues and falses there, and no ability to update it.
The other thing that's kind of unusual here is, if you have a series of trues and falses, and you click somewhere near it just to hover, it seems to insert a checkbox and turn it on automatically.
I don't know what is going on here.
If I come down into this section down here, it's not there. So it has to be near an existing checkbox.
I haven't quite figured out what the rules are for that, like why and where, it just probably has to be in this range, and it's strange... Let's get rid of these.
So now we have some checkboxes here. Yeah, I don't know.
Clearly it's designed to be a feature, I just can't figure out exactly what it's doing.
Also, what if we want to display a word next to the checkbox?
I tried some old tricks with number formatting, and that doesn't seem to be working.
If anyone figures that out, let me know down in the YouTube comments.
And finally, what happens if you send this to someone who does not yet have the feature?
All the checkboxes just changed back to true and false.
My left computer has the feature, this computer does not have the feature, so when I opened the workbook here, it all just changed.
I know you'd want to download this to see if you could get checkboxes, but if you don't have it up in the insert tab, you're just going to get trues and falses.
All right, first off, thanks to the Excel team for this great feature, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
