We'll start with how you can insert a check box into your worksheet, and then we'll look at some
of the nifty things that you can do with them together with functions. Let's check this out.
Here I am in Excel, and if you'd like to follow along today, feel free to set up a worksheet
that looks just like this. To add a check box, first off, we need to add an additional tab up
above on the ribbon. To do that, hover over any existing tab and right-click. Within this menu,
let's click on customize the ribbon. This opens up Excel options and over on the right-hand side,
you can turn on or off all of the different tabs that appear as part of the ribbon. And
right in this list, you should see an option for a developer. Let's check this box and then down
below click on okay. This is now added a new tab up on top titled developer. Let's click into that,
and we have all of these different options here. We just want to insert a very simple check box. In
the center under the controls category, let's click on insert, and here we see all sorts of
different form controls and there's the check box. Let's click on that. Now that I've selected check
box, I can now drag a check box anywhere on this worksheet. Now I want to add a list for all of
these different cookies to see if we've finished baking them. So here I'll draw a check box and
I'll place it right there. The check box is currently selected. I can click on it and here I
can reposition it. If I expand the check box, you also see that there's text next to the check box.
The one downside of this text is you can't format it, although you can edit what the text says.
Now for now, I'm just going to delete the text and I just want a check box next to this item.
When I click on the check box, you'll notice that it checks, and here when I click on it again,
it'll uncheck. I could toggle the check state, but let's say you want to select the check box.
Maybe you want to reposition it, or maybe you want to move it somewhere else on the sheet.
You can right click with your mouse button on it and that'll select the check box. Alternatively,
you can also press the control key and then click on the check box and that will also select it. Now
that I have the check box selected, here I'll reposition it so it sits right in the middle.
To copy the check box down to multiple items, in the bottom right-hand corner, I can click on
the fill icon and here I could pull it down and that gives me another check box. Alternatively,
I can click into this cell and here I'll drag down and I can press control D and that'll also copy
the check box all the way down. Now, if there's a check box you don't want, here I could right
click on it and I could cut, or I could press the delete key on my keyboard. You could also press
control to select it and then hit delete, and that's one way to remove a check box from your
sheet. Now that we have all of our check boxes, if you just wanted a visual list of check boxes,
well, then your job is all done, but chances are, you want to know the state of the check box and
you can do something with it, say in a function. Press the control key and then click on the first
check box. Now the check box is selected. Let's right click on the check box. This opens up a
context menu and down at the very bottom, we have the option to format the control. Let's
click on that. This opens up the format control and up at the very top, I can define the default
state of the check box. Now, currently it's set to unchecked. So, when you load this sheet,
you'll see that all the check boxes are unchecked, but I could also change that to checked and that
will become the default state. So, if I load this sheet, you'll see all the check boxes are checked.
You can also set it to a mixed state. Down at the bottom, you can also toggle on or off 3D shading,
and that affects the visual appearance of this check box. The true power of check boxes comes
from the ability to link the state of the check box to a cell. Let's click in here and then select
this icon and let's link the check box to cell C2. I'll click over there and up above we see
C2 in the format control. I'll expand this again, and then let's click on okay. Now it looks like
nothing changed, but look at this. When I check this box, let me deselect it. When I click on the
box, you'll see now that cell C2 shows true. And when I uncheck it, it now shows false. I've now
linked my first check box to a cell, but I also need to go through and link all of these other
check boxes to a cell, and I'll show you a few different ways that you could do this. I'll press
the control key and click on the second check box. Now I could right click and go down to format
control, but an even quicker way is we could press control 1 on the keyboard and this opens up the
format control. Now I can click into link cell and let's link that to C3 and then I'll click on okay.
Now here, if we test it out, we'll now see that this cell is now linked to this check box,
but an even quicker way, let's press control and then select this check box and up on top in the
formula bar, let's enter an equal sign. That lets Excel know that we're about to enter in a formula.
And I want to link it to cell C4. I'll click over here and hit enter. And these two are now
linked. That's an even quicker way of linking them. And down below, I'll click on control.
Insert equals up here and then select cell C5. Now, all of my check boxes are linked to one
of these cells. Now that we've linked all of these cells to these check boxes, we can now use it with
conditional formatting and also functions. Down at the very bottom of this list, let's type in
items completed, and we're going to use a function to count how many of these items are checked off.
Right up on top, let's click on this icon and this will open up the insert function dialogue. And
there's a function called count if that'll allow us to count the number of trues in this column.
Let's click on go and here's count if. I'll select that and then click on okay. Here, it first wants
us to enter in a range. This is our range, all of these values here. And for the criteria,
I want a count of how many are currently set to true. And then I'll click on okay. Right now,
we see that all of the check boxes are unchecked. So, none of them are complete yet, but here, if I
check one of them, look at that. It says true. And now we get a count of one. If I go through,
we can count every single item. Here we have four items completed. We can also figure this out as
a percent. Up here in the formula bar, let's divide this by the total number of items. I'll
use another function called count a. This will count the number of cells in a range that are not
empty. I'll open up the parenthesis and here I'll simply highlight all the different cookies that
we have. So, we have four different items in the list and then I'll close it and hit enter. So here
we see a one, which means that we have a hundred percent done. And if I check this, there's 0.75.
Now right up on top, let's click on home and right in the center in the formatting controls, we can
change this to a percent style. And now I see that 25% of this list is done. I'll check the first
box here. We see 25, 50, 75, all the way up to a hundred percent. Let's now add some conditional
formatting with this cell selected. Let's go to the home tab and in the center, in the styles
category, let's click on conditional formatting. And right in the center, there's an option for
data bars. Let's hover over that, and then let's go with solid fill. I’ll select this option.
Let's go right back up to conditional formatting and then select manage rules. And here let's click
on edit rule. Right over here, we can now define what this bar should look like. I'll
select a minimum. Let's set that to a number. And for the minimum, let's set that to zero.
For maximum, let's also set that to a number. And here we can set that to a hundred percent, or you
can enter one and then click on okay. And let's click on okay again. Now, when I go through and
check the boxes, look at that. We're now using conditional formatting to show the percent of
items that I've completed. That's pretty cool. Hopefully now you're starting to see the power
of checkboxes and using the state of whether it's checked or not in various formulas and functions.
I want to show one more example of how you can use this. Let's say I want to generate a list of all
the items that are still remaining. Let's go over to column E and here I'll type in items remaining.
And to get a list of all of the items remaining, we can use the filter function. Up in the formula
bar, let's click on the insert function icon. This opens up the insert function dialogue and right
in here, let's search for the filter function, then select the function and then click on okay.
This opens up all the different function arguments. And first off it asks for the
array. Down here, we can see that's the range or the array to filter. Now I want a list back of
all the items that are still remaining. So, this is my array. These are all of the different items
that I want to filter. Next it asks me what items I want to include. Well, this is a list of all of
the items remaining, so any item that is set to false or any box that's unchecked should be
included. I’ll select this list of items and if it's set to false, I want to include it.
These are the two required arguments. Down below, I also have an optional argument that says if
empty. So basically, if everything is complete, what should we show? Well, I just want to show an
empty cell, so I'll enter in quotes and then close quotes, so it'll just provide an empty list back.
I've now filled in all of the function arguments. Down below, let's click on okay. And look at this.
I now have a list of all of the items remaining. So here, as I check the different boxes,
now I just have two remaining. Now I just have one remaining and now I'm all done. So just a
few different ways that you can use a checkbox and the state of that checkbox in various functions
and formulas. Now, looking over here, you see that we say false and you probably don't want it
to say true or false on your sheet. We can format these cells so you no longer see them. With all of
these cells selected, up above, I could change the font color to white or an even neater way to hide
these, you can press the control one key with all of these cells selected, and that opens up
format cells. You also could right click and here too, you could go down to format cells. So, a few
different ways of getting there. Let's go down to custom and here in the type, simply enter a
semi-colon three times. That'll hide the value in the cell. Now we'll click on okay. So that hides
all of them. But if we look up at the formula bar up above, we'll see that it still has a state up
here. It's just not visible. And just like that, we now have a pretty neat sheet using checkboxes
and we can use the state of that checkbox in various functions and formulas. All right. You now
know all about checkboxes in Excel. You can check that off your list. Sorry, that was really bad. To
watch more videos like this one, please consider subscribing and I'll see you in the next video.
0 Comments