How to Insert Checkbox in Excel

 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.  

Insert a checkbox

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.  

Insert multiple checkboxes

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,  

Link checkbox to cell

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  

Use checkbox with countif function

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  

Use checkbox with conditional formatting

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  

Use checkbox with filter function

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  

Wrap up

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.

English