Hi, this is Wayne again with a topic “Using Checkboxes in Excel – Part 2”.
This tutorial is a follow-up to a previous tutorial called using checkboxes in Excel. So in the previous video, which I hope, you’ll watch, if you haven’t already done so, I created two kinds of checkboxes. This is a simple checkbox that you can just click to mark something as being done or not done. In this case, it just means that the contact information has been updated and then I showed how to set up a checkbox a little bit differently here in the driver, safety training area. I’Ve set it up so that when you click the checkbox, yes, a check appears in the box, but also this cell is linked to the check box, and so, if it’s checked it says true.
If it’s not checked it says false. So in this follow-up video, I want to do a couple of interesting things with this true/false. The first thing I’d like to do, instead of it saying true, I would like a more customized message to appear, instead of just true or false, to do this, I’m going to use the if function, so I’m gon na click here on h2 and I’ll just type Equals so this cell equals, and then you put in the if function, you follow that up with a left, parenthesis and Excel gives me a little helper message that appears here if logical test value of true value with false, so that should help me. It’S like a cheat sheet so equals if left parenthesis.
Now I’m gon na click on the cell that I want to test. So I want to test to see if G 2 is equal to false, so I’ll click on G 2 is equal to false. Now sometimes I’m tempted to put that word faults in quotation marks, but you don’t need to do it in this case. So don’t now I’ll put in a comma and the comma basically represents then so if G 2 is equal to false, then do something now, if you want to learn more about the if-then statements in Excel in more detail, watch my video tutorial that shows that in-depth, But for now I’m just going to put in the comma for them and I’ll put in a space and then quotation marks and then here I’m gon na type, the word delinquent and follow that up a close quote. So, let’s review what this says: if G 2 is equal to false, then the quotation marks means print so then print the word delinquent in H, 2. Now, how do you say if not notice, that there’s a comma here in the helper text that I’m given so I’ll put another comma? So if not and I’ll put a space, but I don’t think you have to then print done and then close quote now. I suppose I could have done this in the opposite order.
I could have said if it is equal to true, then print done. If not then print delinquent, it doesn’t really matter one way or the other. Now. At this point, I should put in the close parentheses and then tap enter on the keyboard, but you don’t really have to so I’ll just leave it blank and tap enter on the keyboard and notice that now it’s generated. The word done to.
Let me know that employee number one Gina pullin her driver safety training is completed now. At this point, I hope I don’t have to do that same formula again for Row 3. Fortunately, I don’t have to all I have to do. Is click here, undone and use the autofill handle here at the lower right click and drag down, and it will copy the formula that I’ve set up. Here’S the formula and it copies it all the way down, but changes G to 2 G, 3 to G 4, etc.
So now I’ve got a nice, understandable text here. That tells me whether they’re done or not done and notice. When you check a box, it changes both column, G and column.
H now so that’s kind of nice. Now there’s a couple of things I could do to make this look a little better. One would be. I could click and drag on f1 through h1 and then I could go here to merge and center.
This is the merge and center button. I click on it and now it’s centered over all three columns. I think that just makes it look a little better. It makes a little bit more sense.
The other thing I could do is hide column G. Just by right-clicking on G I can go down and choose hide, and even though it’s hidden, the formula still works. If I really wanted to, I could make column F, more narrow and again we’re just making things look a little nicer. I think that looks great, actually, okay, the next technique.
I want to show you how to change the look and feel of your data and of your spreadsheet based on checkboxes is I want to show you how to use conditional formatting so here on the Home tab home ribbon? You should see in the Styles group conditional formatting, and this is so useful if you haven’t watched my tutorial on conditional formatting you’ve got to do that. I actually have a couple of different tutorials on it, but I’m just gon na click here on column, H and I’ll. Go to conditional formatting highlight cells, rules and I’ll go down to text that contains. So this is going to test to see if any of the cells in column H have text that contains the word delinquent. Now, if I hadn’t hidden column G, I could have done this with column G and I could have said if a cell contains the word faults.
Then format it, but that’s okay, I’ve hidden it so I’ll. Just do the same thing with done and delinquent. So I’ll.
Just say, format, cells that contain the text delinquent with light red fill with dark red text. I think that makes total sense. These are people that need to get in and do their driver safety training. If you want, though you can change it and have red border or you could have green fill green text, you can also do some custom formats where you set it yourself, the colors, the fonts all of that kind of stuff, but I’m gon na stick with light Red fill with dark red text, click. Ok! Now I could click the same column again and do another highlight cells, rule text that contains – and this time the word done. If it contains the word done, I’ll highlight it in green click.
Ok and there we go so now. I have a very visual cue telling me whether they’ve done the driver, safety training or not. Now it is possible if you want, for the entire row to be highlighted based on whether their safety training is done or not done. In this case, I don’t think it makes sense, but if you want to learn how to do that, you should watch my conditional formatting advanced technique, tutorial. That shows you how to do it. In the meantime, though, 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. Please click the bell next to the subscribe button. If you do you’ll be notified whenever I post another video and watch for another video from me at least every Monday, if you’d like to support my channel, consider becoming a supporter of mine through my patreon account and you’ll see a link to that in the description Below .