Hi everyone, Kevin here. Today, we’re going to learn how to insert checkboxes into Microsoft Excel. This is brand new functionality. Then we’ll look at some of the more advanced ways that you can use them. For example, you can sum them up or even make an interactive chart. Let’s check this out. Here I am in Microsoft Excel. And if you would like to follow along, feel free to click on the workbook down below in the description of this video. To insert a checkbox, simply select the cell where you would like the checkbox to appear. Up on the top tabs, click on the insert tab and all the way over on the right-hand side, there is now a new option for a checkbox. Simply click on that. And just like that, you’ll now see a checkbox on your sheet. To check the checkbox, simply click on it. And to uncheck it, you could simply click on it again. Now as a quick shortcut, you can also press the space bar key on your keyboard and that will also check the checkbox or here I can press it again and that will uncheck it. Now, one thing to notice, when I check it, let’s look up on top at the formula bar. And when it’s checked, it shows the value of true. And here, if I uncheck it, here it shows false. To insert more checkboxes, simply click into another cell and up on the insert ribbon, again, let’s click on checkbox and there I now have another checkbox. You can also highlight multiple cells and then here I’ll click on checkbox and there I now have multiple checkboxes. You can also copy a checkbox, press control C, you can highlight multiple cells and then press control V and that’ll now paste multiple checkboxes. You can also click into a cell with a checkbox and in the bottom right-hand corner, you’ll see this square icon. You can click on that and you can fill it all the way down. You can also click into a cell with a checkbox, highlight multiple cells and you can press control D and that’ll also fill it all the way down. And look at that, I now have a whole bunch of checkboxes on my worksheet. Now that we’ve added all these different checkboxes, you might be wondering, well, how do you remove checkboxes or how do you delete checkboxes? Now you would think you could just highlight some cells and press the delete key and all those checkboxes appear to have disappeared. However, when I hover over any of these cells, you’ll see that the functionality is still very much there and you may have a need for that but if you just want to entirely remove a checkbox, here I’ll highlight all of my checkboxes in this table. Then up on top, let’s click on the home tab and right over on the right-hand side, we have a category for editing. I’ll click on this icon right here and I have the option to clear all. When I click on that, that removes the checkboxes entirely and also when I hover over, that functionality is no longer there. I don’t know why I deleted all of those checkboxes because I actually have work I need to get done. I need to fill out this attendance sheet for the Kevin Cookie Company. So, let’s re-add those checkboxes. Here, I’ll simply highlight all of these cells. Let’s go up to insert, click on checkbox and here I have all of my checkboxes. With checkboxes, you can also modify the appearance. I’ll click into this cell right here then click over to the home tab. Right over here, you can adjust the size, so I can make a checkbox bigger, here I can make it smaller. You can also adjust the color. So here I could choose whatever color I want for this checkbox. You can also adjust the alignment. So, lots of different options to get the checkbox to look exactly how you want it to look. Of course, I don’t want one checkbox that just stands out. I’ll click into this cell, let’s click on the format painter and click on this cell and this restores the look of that checkbox. Now that we know the basics of working with checkboxes in Excel, let’s look at some of the interesting ways that you can use them. Back in Excel, I have an attendance sheet for the cookie factory at the Kevin Cookie Company. Here we have all of our employees and a checkbox indicating whether or not they attended that day of work. Now I want a count of how many days during the week each employee was there. Now I could go through and I could count the number of checkboxes and you might be thinking, well, this is a cookie factory, that’s probably how they work, but no, we are more efficient than that. I think I could use a function to help me. Now, if I click into this checkbox that has a check in it, you’ll notice up above it says true. And here, if I click in this one that’s unchecked, it says false. We should be able to use a function to count up the number of trues or the number of falses. So, with this cell selected under total days, let’s go up to the function helper, this FX. Let’s click on that. And this opens up the insert function dialogue. And we’re going to use a function called count if. If you don’t see it in this list, you can also search for it. I’ll click on count if and then click on okay. This opens up the function arguments. And right down here, we can read what this function does. It counts the number of cells within a range that meet the given condition. Now, the condition is that the cell is true. Here, I’ll move up the function arguments so we can see all the data down below. For the range, I’ll highlight all the days of the week. So, there we see the range entered in right here. Next, we need to enter in the criteria. Now remember, the cell either says true or false. I want to know how many days they attended, so the criteria is that the cell has the value of true. And down below, we can see that for Chip Baker, he was there five days during the week. And here, if I look across all five check boxes are checked, I’ll click on okay. And there we can see that he was there for a total of five days. Now, one of the neat things is I can click on this fill icon in the bottom right-hand corner, and that’ll copy that formula all the way down. And here, I can see that Ginger Snap. She skipped Friday. She was only there four days during the week. Here, I can see that Mac Aroon, he was only there for three days. So, this is a really neat way that you could very quickly sum up how many check boxes are checked or are unchecked. Word on the street is that a number of our employees tend to skip work on Fridays. So, I could very quickly see who skipped work on Friday. Now, I could try to look through this list. And here, I see that Ginger skipped on Friday. And I could try to go through the entire list and find out who wasn’t there. Or we could also use something called a filter. I’ll click on this header right up above. Up on top, let’s click on the data tab. And right over here, we could turn on a filter. Here, we see this dropdown icon next to every day of the week. Now, click on this. And here, I can now filter based on true or false using the value of the checkbox. And I want to see who was not there on Friday. So, I’ll select false. That means unchecked. Then let’s click on okay. And here, I see that both Ginger Snap and Mac Aroon decided not to come in on Friday. I don’t have to let their manager know. (chuckles) I’m not going to do that. I’m not a snitch. Let’s turn off this filtering. Along with filtering data in place, you can also use a function to filter the data. Let’s scroll down just a little bit, and here’s a question, who skipped work on Friday? Now, we know from earlier that both Ginger Snap and also Mac Aroon skipped work. So, let’s see if we could use a function to tell us this. I’ll click into this cell. Then up above, let’s click on the function helper. And right up here, let’s search for the filter function. I’ll click on go and there’s the function, and then let’s click on okay. This opens up all of the different function arguments, and it wants a few different arguments. First off, it wants the array or basically the value that it gives back. Now I want to see a list of all the employees who didn’t show up. So, for the array, I’ll enter in the employee name. I’ll select all of these names over here. Next, it asks me which names I should include. And basically, I just want to see all the names that are unchecked or all of the names who did not attend on Friday. I’ll select all these different values down here. And again, I just want to see the ones that are unchecked. And remember, when it’s unchecked, it’s set to false. So, I’ll say equals false. Down below, I can also indicate what should happen if it’s empty. So, if all of them attended work on Friday, what should I show? Well, I just want it to be blank, so I’ll insert a quote and then another quote. So that way it’ll just show nothing. Down below, I can see the results of this function. I can see that both Ginger Snap and Mac Aroon did not attend on Friday. And that’s what I see down below when I look at these check boxes. Here, I’ll click on okay. Down at the bottom, I can now see the results of this function. And the neat thing is it’s completely dynamic. So, let’s say that Oreo also decided to skip work on Friday. I’ll uncheck this box and his name is now added to this list. But again, I don’t want anyone to get in trouble. So here, let’s check this box. Let’s check this one and check this one, and if we look down below, it’s now empty and blank, and you’ll see that there are no names in this filter function. That’s perfect. Next, let’s take a look at how you can use check boxes to make a dynamic chart. Down below, let’s click into the charts worksheet. On this worksheet, I have cookie sales data by year. To insert a column chart, I’ll click anywhere in this data and I can press Alt F1 on my keyboard, and here I have a column chart. Now, ideally, I would like to be able to toggle on or off a checkbox to see a specific year. For now, let’s delete this chart. I’ll select it and then press the delete key. And unlike with check boxes, when I press delete, it actually deletes it. To make this dynamic, let’s select all this data here and press Ctrl C to copy it. Down below, let’s press Ctrl V to paste it. So right now, I have the identical data down below. I’ll highlight all of the data and then press the delete key and that also removes it. Up above, let’s select these three cells, then go up to insert and over here, let’s insert some check boxes. Now we have three check boxes. Let’s now go down to cell B11. I’ll click into here and let’s click on the function helper. And for this one, we’re going to use a function called if S. Then let’s click on go. Here, I’ll select this function and we can now enter in different arguments. Now I want to show all of the data if this check box is checked and if it’s not checked, I don’t want to show any of this data. That way we’ll make the chart dynamic. So right up here, it asks for the logical test. I’ll click into the cell and here I’ll say if equals true. So, if it’s checked, then I want to show all of this data down below. I’ll highlight all of this data here. Then let’s click on okay. Down below, I see that it says NA and that’s because it’s currently unchecked. However, if I check this box, here we see all the numbers populate in this table. Here, I can uncheck it. Now I could take this formula or this function and I can copy it or fill it all the way across. Now, as I check the check box, you’ll see the data appear or you’ll see it disappear. Now down below, let’s use that shortcut key again. I’ll press Alt F1 and that inserts the chart. Now you can see that currently there’s no data in here because all of these check boxes are unchecked. But here I could check this one and now we see 2025. Here we see 2026 and now we see 2027. So, this is just a quick example of how you could use check boxes to make your charts dynamic. All right, you can now check that off your list. If you enjoyed this video, please consider checking that subscribe button and check in on the next video. (laughs) Cut, cut. (upbeat music)