So Excel's finally got new checkboxes; these are
so much better and easier to use than the other workarounds we had to do before. There are so
many different ways, aside from just tracking your to-dos, that you can use these checkboxes.
I'm going to give you some ideas, but first, let me quickly show you how you can use them. This
is what we're going to be creating from scratch; you are going to learn how to use checkboxes, and
as you learn this, you're also going to learn some cool techniques that you can use together with
them. So in this example, we have created a list, this is our learning list. We have a checkbox in
front of it, and every time we tick off something, so let's say we've learned about the FILTER
function, we get a timestamp of when this task was done. This is using the NOW function,
but notice the timestamp stays as is. So let's say we finished learning about the checkboxes,
I place a check mark here, and now we get the new time added here at the same time. See
what's happening up here under "completed tasks"? We have now four out of nine tasks done.
Once we learn how to do conditional formatting, we have five out of nine done. Also, keep your
eye on this; this is the list of not completed, i.e., our "look forward to" bucket, and once we
place a check mark here, that task is removed from this bucket. Okay, so that's what we're going to
learn how to do. Let's set this up from scratch. I have my learning list here and added this little
cute icon in front of my learning list. I want to add checkboxes. In Office 365, you are going
to find them under the Insert tab, all the way here. There's the checkbox. Just select it, and it
inserts one inside the cell. When you hover over it, you're going to see the hand icon, and when
you click on it, you are going to get the tick mark in there. Notice what happens in our formula
bar. When we have a check mark, we get a TRUE; when it's unchecked, we get FALSE. Now, this is
great because it means that we can write formulas that behave differently depending on whether our
checkbox has a tick mark in it or not. If you want to change the color of your checkbox, you just go
and change the font color. So if I change this to ORANGE, I get an orange checkbox now. Instead
of clicking on it, you can also use the space bar to toggle it on or off. If you want to remove
a checkbox completely, you can try to delete it, right? So if I just press the back arrow
or the delete button and then press Enter, the checkbox is gone. If I go to the cell, I
can see I don't have any value in the cell, but I get this light-colored checkbox there. So
it's still there because checkboxes are a mixture of formatting and cell values. So the formatting
of the checkbox is there, and this can be a good thing in case someone unintentionally removes
your checkboxes. You can just go back, click here, and the checkbox is back again. And if you really
want to remove it, you can either delete the cell, or you can just go and clear all, and that clears
the value and that checkbox formatting as well. You can, of course, add multiple checkboxes all at
once. If you select a range that you want them in, then go and just select checkbox. Okay, so now
you're going to learn how to set up this bit where we're going to count the number of checked
checkboxes that we have. Okay, so first off, we know that whenever a checkbox has a check mark
in it, we get a value of TRUE in the cell. So we are going to be using that in our formula. So
first thing I'm going to do is write the formula on this side, and then later I'm going to show
you how you can bring it inside a shape. So there are different ways you can set this up. I'm just
going to use the COUNTIF function. So we COUNTIF, we need a range. That's my range. If you're using
table references, you just have to reference the table column. The criteria is simple;
it's TRUE. Close the bracket, press Enter, and I get two because I have two TRUE values here,
two checkboxes are marked. If this one is marked as well, we get a three. So that's the number
of completed tasks. Now on this side, I'm also going to get the total number of checkboxes that
I have. Again, different ways of doing this. I'm just going to use the COUNTA function. So COUNTA
counts everything. It counts numbers, and it also counts text. It counts Boolean values as well. So
when I close the bracket and I press Enter, I get nine. So far so good. Now I want to combine all
of these together. I'm just going to combine them in a single cell. So we're just going to reference
this one. We're going to use the Ampersand because we're combining values from different cells. Then
I want to combine it with this one, Ampersand. I want to get the division operator; let's put that
inside quotation marks, then Ampersand. And we're going to select this cell here. When I press
Enter, I get the whole thing stuck together. I could add a space between the two, but what
I'd optimally want to do is to put the task, the three out of nine, below "completed tasks."
So I want to add a line break there. I can do that using the CHAR or C function. So I'm going to use
the ENT again, put in CHAR open bracket 10. That's the one that you need. Then when you press Enter,
you are going to get it on the next line. You just can't see it yet because you have to activate
the WP text if you are looking at that value in the cell, right? So now we can see it nicely
separated. This doesn't really matter if you are bringing this value inside a shape. So let me just
show you to bring it inside this little box that we had here. First thing we need to do is insert
this shape. So I'm going to go to Illustrations, Shapes, and I'm just going to go with this one.
Let's draw it out quickly, adjust the shape fill and the shape outline. Just want to get a thin
weight there. So now with our shape selected, we're going to go directly to the formula bar,
type in equals, and select this cell. Now when I press Enter, I get my completed tasks shown
inside the box, and it's all dynamic, right? If I complete pivot tables, I get four out of nine,
five out of nine, and so on. Now let's just make it bold as well so everything stands out nicely.
Now all we need to do is to hide this bit from view. There are different ways you can do this,
right? You can just put it somewhere really far on your worksheet, or you can group the columns
together, or you can just hide these columns. Now let me show you how you can combine the value of
a checkbox with an Excel function. So for example, I want to create my not completed list, my "look
forward to" bucket list here. Basically, I want to include any items that don't have a check mark. I
am going to use the FILTER function. First thing I need is the array; this is what I want returned.
I'm going to select my learning list items. Then what I want included is anything from this column
that equals FALSE. If empty, so if there are no matches, I want nothing returned. I'm just going
to put quotation, quotation. Close the bracket, press Enter, and that's my list of outstanding
items. Now let's say I finished learning Power Query. I'm going to place a check mark here, and
Power Query disappears from my list. If I have a check mark everywhere, I have nothing to look
forward to. If you want to add a box around this, all you have to do is go and insert a shape.
So for example, let's go with this one. I'm just going to draw it out here. Let's quickly
adjust the shape color. Now, obviously, this is covering our value in the cells, so I'm going
to go back to shape fill under more fill colors. We're going to adjust the transparency to be a lot
higher, so I'm just going to go with this number, click on OK, and now I can see the values through.
The good thing is that when you click on this, it selects the shape, so it's kind of a way of
making sure no one messes with your formulas too. Now we're going to take a look at how we can
combine checkboxes with conditional formatting. So let's say every time I have a check mark here,
I want to get my item to have a strike through, right? I want it to be crossed off. What I'm
going to do first is select the range that I want to conditional format, then
go to Home, Conditional Formatting, and add a new rule. I'm going to use a formula
for this, and for the formula, notice it says "format values where this formula is true." The
formula is going to say equals this cell. All of these cells are looking at C5 because notice I
have a dollar sign for the column for C and also for the row. I don't want them to all look at
cell C5; I want them to all look at column C, but the row should be different, right? It
should be relevant to where that cell is, so I have to remove that dollar sign here. Now you
can say equals TRUE, but you don't really need to do that because the default check is whether it's
true or not. So basically, that's all you need to do here. Then you go to Format and you decide
on the format that you want. So I want them to have a strikethrough, right? I want it to look
like this. Click on OK, OK, and that's that. The moment I have a check mark anywhere here, I get
my strikethrough formatting to show up. Now the next thing I want to do is to change the color of
the entire row to be green as well. So I want the font color to be green again. You have to select
the area that you want to conditionally format, which in this case is this area. We're going to
go ahead and add another rule, so let's go back to Conditional Formatting, New Rule. Again, I'm
going to use a formula. The formula is going to be the same; we're going to have C5 here, and
again, remove the dollar sign for the row. Now the formatting that we want to apply here is for
the font, but I want the color to be different, so I want it to be a green. Let's just go with
this one. Click on OK and OK, and now we have two conditional formatting rules in place,
right? So every time we place a check mark, we get the strikethrough effect here, and we
also get the entire row in this green font. Okay, I've saved the best for last. Let's see
how we can add a timestamp every time we check off an item. So the way we're going to do this
is to use the IFs function. This one is going to check whether one or more conditions are true.
Our first logical test is to check whether the value of this box equals FALSE. Basically, if
it's not checked, what do we want to return? We want to return nothing, right? We don't want a
timestamp if there is no check mark. Now, here's where things are going to get weird, but stay with
me. So our next check is to check whether the cell that we're writing the formula in, if this cell
equals nothing, what we want to return is the NOW function. So NOW is going to return the current
date and time. Now, I know this is strange because we're referencing the cell that we're writing it
in, and this is not correct, but stay with me, and I will explain. Our last logical test is going
to be the catchall. So if none of these conditions is met and we get to the end, we are going to
put a TRUE. This is acting as our catchall, and what we want to happen is to return the value
that we have in the current cell. Now when I close the bracket and I press Enter, I get a value. I'm
supposed to not get a value because I don't have a check mark here. Even when I press a check mark,
I still get the same value, right? So this is not correct, and yes, it is not correct in this
way because a cell in Excel by default can't be an input and an output at the same time. Excel is
going to go in a loop, and I'm just going to get a circular reference. Here's the thing that you need
to tweak: you have to go to File, Options, under Formulas, for the calculation options for this
workbook, you need to turn on "Enable iterative calculation" and then click on OK, and everything
will work like magic. Now why is this so? Well, iterative calculations allow Excel to handle such
situations by repeatedly calculating the value of the cell until it reaches a stable answer or
until it reaches the maximum number of iterations that are allowed. So in my case, default was
100. This can have unintended consequences, so whenever you're using it, you have to use it
with care. Okay, so having said that, we have completed our tutorial on how to use checkboxes.
You've learned how you can add a total count of your completed tasks, how you can use conditional
formatting together with the checkboxes, and how you can use the value of the checkbox in
your Excel formulas. Okay, I'm super excited about this development. Let me know how you're planning
to use checkboxes in your reports. Thank you for watching; do subscribe if you aren't subscribed
yet, and I'm going to catch you in the next video.
