Hi everyone, Kevin here. Today, we’re going to look at how you can use check boxes in Excel. 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.