Today, I'm going to show you how you can use checkboxes in Excel to create checklists that
look like this one. Adding a checkbox is very easy; by using the outcome of the checkbox, so whether
the box is checked or not. It's also really easy once you understand how to use this one setting that
I'm going to show you in a bit. In case you'd like to improve your Office skills, make sure
you're subscribed. Now let's get to it. I want to add checkboxes right here, so as I'm going through
my learning list and I finish something, I want to place a check mark in the check box and I want to
cross this off my list. First off, let's add a check box. To do that, you need to go to the Developer tab.
Now, if you don't see the Developer tab, because it's not there by default, you need to right-mouse-click
on your Ribbon, go to Customize the Ribbon, under Main Tabs here, you're going to see Developer.
Yours is not going to have a check mark beside it, so you need to place a check mark and then click
on OK, and then you're going to see Developer right here. Then, go to Insert and from Form Controls, see
the check box right here, that's what we need. Click on it and then, draw it out where you want to have
it. I want to have multiple check boxes in each cell, I'm going to put mine right here, then you can
click inside it or you can also right-mouse-click on the check box and edit the text. Then, you can
type in what you want, so I'm just going to remove this and put "Done." You can also remove the text
completely, so you don't have any text in there. Now, I'm able to place a check mark in here or uncheck
this. If I want to copy this down and apply it to these cells as well, I can click on the cell itself,
okay, so make sure it's a cell selected, and then just drag this down until where you want to have
it. And now, we have our multiple checkboxes. If you just need to add simple checkboxes and you
don't need to do anything with the result of the checkbox, aside from seeing it visually, then you're
done. But if you want something to happen when you place a check mark in the check box, then you need
this one setting. That one setting is this. So, you need to right-mouse-click on the check box and
go to Format Control. There's also a shortcut key you can use, it's Ctrl + 1, that's going to bring
you to this view. Under Control, we have this value part here, but you can ignore that because that's
just the current status of the check box. Mine is checked, so it's showing me that it's checked.
You don't really need to touch this part. What you need is actually this the cell link. This is
where you're going to put the result of the check box. How does that look? Well, we can put the result
right here. I personally like to put it on the same cell as my checkbox because this way, I don't have
to look for it. But just so that we can see it better, I'm going to put it on D2, and then click
on OK. Take a look at this. When it's checked, we have TRUE. When it's unchecked, we have FALSE. Even
if I go and delete this, if I place the check mark here, it comes back. It tells me it's TRUE; if it's
unchecked, it's FALSE. Now, this is information I can use to control anything else. I can use this in
formulas, I can use this in conditional formatting. But here's the problem though, if I right-mouse-click
and go back to Format Control, and let's actually change this to "C", so that it's placed
under the checkbox. If I do this, and even if I take away these dollar signs here, both of them or
just the one for the 2, even if I do this and click on OK, and now let's take a look. So, if I
click away and click on this, I get the TRUE/FALSE here. This is not controlled anymore by the cell, so
we can see TRUE/FALSE. It doesn't look so nice, but don't worry, we are going to make the font white. We
can make it go away. The problem though is that if I copy this checkbox and I put it somewhere else,
then when I right-mouse-click and go to Format Control, it's still pointing to C2. So, these are
not dynamic, even though they look like dynamic references. That, unfortunately, doesn't work. So
this means that we need to go through each checkbox here, and add its own cell link. So
this one would be C3, and then let's actually use the shortcut key, Ctrl + 1. This would be C4,
C5, and finally, C6. Okay, so I put all of these right below each checkbox, and you can see that
they say TRUE. Let's change the color, so that they're not visible anymore. Okay, so
now is the time to use that result to adjust the formatting, so that every time I have a
checkbox here, and this underlying cell is saying TRUE, I want to have a strikethrough
type of formatting. That's conditional formatting, I'm going to highlight this,
go to Home, Conditional Formatting, and add a new rule. That rule is going to use a formula. The
formula is actually very simple, it's just going to look at this cell that has TRUE/FALSE, that was C2,
right? So, let's see if I can actually click on it. Just carefully click to the side, it picks it up.
Now here, I have to be careful because I don't want the formatting of the entire range to always
just look at C2. I want it to be dynamic; it should look at each individual row. So, I need to take away
the dollar sign from the 2, but I can leave it on the column. Now, let's go to Format, under Font, for
color, we can adjust this if we want as well. We could make it a lighter gray, and let's also have
that strikethrough effect. So then, let's go with OK, and OK. Now, we can see this in action. All
tasks are done. Now, in addition to this, you can also use this result in formulas. So, let's say
I want to have a list of outstanding tasks. I can use the FILTER function to get that list. My array
are my tasks here. For the "include" argument, I want to include anything that's here, that's
FALSE, right? So, I'm going to select this range and take a look at whether it is FALSE. That's it! Close
bracket, press Enter, and I get everything that's unchecked. This one joins the list, it's there,
and everything works. Why is this working? Because it's looking at the underlying cell
here, and remember, I changed the color to white, so we can't see it. If I turn it back, we can see that
these are TRUE, this one is FALSE. Now you can, of course, put these on other cells, right? You
can put it on this cell here, but I just prefer to have it on the cell itself, because otherwise,
I don't have to go and find it. I know where it is. That's also the default behavior in Google Sheets.
Okay, so let's turn these back to white. As you can see, adding checkboxes is really easy.
Using the outcome of the checkbox to control the rest of your report is also very simple once you
learn how to use the linked cell in your formulas. I hope you found this tutorial useful. Do give it a
thumbs up and subscribe if you aren't subscribed already. I'm going to see you in the next video.