Hi, this is Wayne again with a topic “Using Checkboxes in Excel – Part 1”.
In this tutorial, I’m gon na show you one way to add checkboxes into your Excel spreadsheets and we’ll look at how that can be useful to you. In a previous video, I showed you how to use a form control here on the Developer tab in the controls group. You can insert form controls and I showed you an example of one of those. There was a lot of interest in that video, and so I promised to make another form control video, and here it is so you can see in this spreadsheet. I have an Employee List with some basic information about the employees in a hypothetical company and I would like to create a couple of columns with checkboxes. So, for example, have they updated their contact information for the year 2020 and have they completed the driver safety training and there really are two or three different ways to add check boxes and to think about them. I’D like to show you the easiest example first, so here in cell e2, I would like to create a check box now. The absolute first step that you need to do is to make sure that you have the Developer tab, it’s not there by default. So for many of you, you won’t see that the way you get the Developer tab there’s a couple of ways you could click on here, but probably the easiest way is to right-click on the ribbon. So I’ll, just right-click here customize the ribbon. It brings up some options that I can use to change the way the ribbon looks and what options appear there and I’m just gon na go here to the right side, where it says main tabs and you’ll notice that I have developer checked. If you don’t, you need to check that if you want to add checkboxes like I’m about to show so I’ll click. Ok, I have the Developer tab, so I would like to add a checkbox right here.
All I need to do is make sure I’m on the Developer. Tab in the controls group – and I click on insert here – are all the form controls now. There’S two sets of form controls, there’s ActiveX controls.
Those are a little bit harder but more powerful, but in this case I don’t need anything that powerful I’ll just go with regular form controls. These are easier and they’ll do the job in this case. So here it is check box, form, control, I’ll, just click, and then I need to click on the screen where I want that checkbox to appear so I’ll click right there. Now it didn’t quite go in where I want it to be, and if I click away you can see that and if I click back on it look what happened. It just checked the box.
Well, I want to move it up a little bit and line it up correctly with Geena pullin. So the way to do this, you can’t left-click and drag it. You have to right-click and drag so not your typical process for moving something, but just because of the nature of form controls you do have to use right. Click drag in order to move these form controls in many cases.
So I’ll click move here and that’s a lot better. Now, you’ll notice, the check box comes in with some text. Sometimes you want that text. Sometimes you don’t in this case. I do want to use the text, but I want to change what the text says. So I’m gon na right click on it click Edit text and I get a cursor now that I can use to erase.
So I just tapped the right arrow key and I’m gon na use the Delete key on the keyboard to delete. What’S there and I’ll just type in updated, so if this checkbox is checked, I know that this person has updated their contact information recently I’ll uncheck, that for now so now I need to do all of that all over again right for marcelina Restrepo for Regina Loftus And for all of these other wonderful people, well, the good news is that you can click on in this case e to and you can use the autofill handle this little square in the lower right corner of the cell. That’S selected, you can click and drag and pull that down and look what it does it just copies the contents of that cell all the way down, and in this case the cell had in it a form control. So that’s a big relief that you can just click and drag to add these check boxes all the way down the spreadsheet. So that really is pretty easy, pretty doable right to add simple check boxes.
Well, I want to show you a little bit harder example, but I think it’ll be rewarding for you to watch this. It is possible to use these check boxes for more than just a visual q that something has been completed or that some characteristic can be applied to this person or thing, so I’m gon na click here on f2, in this case again on the Developer tab in The controls group – I will click, insert checkbox and do the same steps right click and drag to put it in the proper place. In this case, I’m gon na right click on it and just delete the text just so that you can see that you do have both options.
So the checkbox now just simply means that, yes, they did complete the driver safety training, but next I’m gon na right. Click on the checkbox and I’m gon na go down to format control, and this brings up some options for that checkbox. What’S the default value of that checkbox, unchecked or checked or mixed mixed is basically grayed out, I’m gon na go with unchecked, that’s the default. Next. There’S an option for a cell link, so I can link this checkbox to a cell and the results of this checkbox can go to that other cell. So I’m gon na click here on this arrow, it’s a button, so I click there and now it hides the rest of the options. And now I can just click on the cell that I want to be linked to this checkbox. So I’m gon na click here on g2 and then I can go back and click that arrow again, that’s great. If I want to, I can add some 3d shading to the checkbox. Let’S see what that looks like so now I click.
Ok – and you can see my checkbox now – does have some 3d shading, that’s kind of cool. Now, look what happens when I check the box, because I linked cell g2 to the check box. It’S telling me whether it’s true or not, that the box is checked, and it’s also saying it’s true – that they have had the driver safety training right, that’s all linked up, so this is great. I like this a lot so now, let’s try the same thing that we did before.
Let’S try the autofill handle to copy this down the page. Now it’s a little tricky here. Sometimes, if you click, you might be clicking on the check box.
So just be careful, you might need to right-click in order to select the cell, but just get that same autofill handle you can copy it down. Now, let’s see if it works notice, when I check one checkbox, they all get checked and they’re all working off of G and so that’s a problem. I need to right-click on my second checkbox and go to form control and I need to update this to be g3 and then I’ll go back, click, OK and then I’ll click away. Let’S try it now! Ok! So now it’s working independently from the other ones, and so this is one of the downsides to the way the check box form control works in Excel. I am gon na have to update each of these now learning the pattern I can just go in and type.
I can just type 4 for g4 and it works so I’ll be honest that this is a little bit of a hassle, especially if you have a giant spreadsheet. That’S why I think in this case the example of a list of employees for a small business or a midsize business. I think that’s a good example of when I would do this more difficult, more complicated kind of check box, because this way it’s not an outrageous amount of work. If I have a spreadsheet with a thousand records, I don’t think this would be a wise way to create check boxes and there are other ways that you can get to something similar to this. In my opinion, these are the best methods for adding check boxes, but this is the downside.
What I just showed that you have to update the cell reference if you want to use the data the way I’m about to show so give me a minute to fix the rest of these form, control cell links and then I’ll resume the video okay. So I’ve finished updating these, and now you can see that I can very easily mark whether or not each employee has completed their driver safety training. Now the nice thing about having this report false or true, is that then you could set up a formula basically an if statement.
You could click here, so this cell is equal to. If and then you could basically say if this cell says true, then do something you could also use conditional formatting as well. So, for example, I could have the employees who have not completed the driver safety training highlight in red, so there’s a visual cue saying that they need to get this completed and then, of course, if I wanted to, I could even hide this column just by right-clicking On the column letter I could go down to hide and that true-false true-false, it still exists, is still in the spreadsheet, but it’s hidden from the view of the people that look at the spreadsheet. So now it’s just about the text boxes at least visually.
If you’re interested in seeing how I could make the records light up in red or green or any other color by using the conditional, formatting and also the ways I could use if functions, to do some things with this spreadsheet leave me a message in the comments Below and I’ll consider making a follow-up video thanks for watching. I hope you found this tutorial to be helpful. If you did, please click the like button below and consider connecting with me on my social media accounts like Facebook, Pinterest and Twitter, and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and when you do subscribe, click the bell. Next to the subscribe button, if you do you’ll be notified whenever I post another video and watch for a new video from me at least every Monday, if you’d like to support my channel, become a supporter of mine through my patreon account and you’ll, see a link To that, in the description below .