Using check boxes and conditional formatting together for dynamic displays

- [Instructor] On this worksheet is an employee list, hundreds of rows, but off to the right, there's something colorful in column M. If I click on the box here that says Full Time, look what happens. This is called a check box. I'll check the box for Contract and for Hourly. And so, at different times, rather than filtering the data, which hides the data we don't want to see, we can still see the data, but on the other hand, we're giving special emphasis to one or two or all three of these in different ways by using different colors. and the feature called Check Box, we don't see initially in the Ribbon menu system. We're also combining this with a better known feature called Conditional Formatting. It's available on the Formulas tab. Now, something else is happening too, as I click these check boxes. It's off to the right. Ultimately, this doesn't need to be visible, but I'm going to check the box for Full Time. Watch cell N1 becomes true, in addition to the highlighting that we see off to the left, same thing with Contract and Hourly. When you set up check boxes, you link them to certain cells, and then you can use, for example, Conditional Formatting to take advantage of the content here, and then apply formatting as you need it. So, how do we get to these check boxes? Where do they come from? They come from a tab in the menu system, called Developer. Unless you're familiar with it or have needed to use it, you won't see it here, unless you right-click one of the existing tabs, and choose Customize the Ribbon. Off to the right you'll see Developer. If it's not checked, of course it's not there. Check the box. Click OK. And on this Developer tab, under Insert you see Form Controls, one of which is a check box, so we're going to be using that. We can leave this existing set of check boxes here. If we simply click on these with a control key each time, let's press delete. They're gone, and we also want to turn off the existing Conditional Formatting, so back to the Home tab here. We'll go to Conditional Formatting, and simply clear the rules from the entire sheet. So, let's set this up. We need to put in a check box, and actually we need to put in three, Developer, Insert, Check Box, and just click right here. We will be copying this too, but let's first change its name. I'm going to click just to the left of the text check box, and type in Full Time. Delete the other text off to the right. Now, I want this box to be associated with cell N1, so simply right-clicking on it gives us a menu, Format Control, Cell Link, N1; give it some shading if we wish. Now, we essentially need to do this two more times, but we can copy this. Hold down the control key, drag this down here. Hold down control; do this again. Now, we need to change the linkage in each case. I'll right-click the second one there and Format Control, and make sure that that's linked to cell N2, and click in there. Change that text, in this case, to be Contract, and delete the other text. And then third time around here, control-click here, and make sure that that text will click in it, Hourly, and we need to change the linkage here too. When you're making changes here, sometimes you have to click outside of it, then come back and control-click, and right-click, and go to Format Control. We want this to be associated with cell N3. And a quick test of all these, going back to the first one, we click Full Time. We see true or false as its on off, same thing with Contract, and clicking outside of the other box momentarily, coming back. Now, when I did the typing there, I made a mistake on its location, so I could just move this up here, and get rid of that latter one. In fact, I can move it all the way up to here. There we go, so that's in sync too. Now, we need to then apply Conditional Formatting to all the cells, from column A over into column J. And we need formulas to say, in effect, "Only make changes in a row, "when the column D entry matches up "with one of these entries that we see here." And the formulas look something like this. I'll simply copy this. Highlight this; press control-C. We're about to use this as a Conditional Formatting rule, so highlight it, press control-C, escape. We'll come back here, and now use Conditional Formatting on these cells, Home tab, Conditional Formatting. We create what are called New Rules. Use the form then to determine which cells to format. We'll paste that rule in right here. And what are we saying in English? "Every time the entry in column D "happens to be Full Time, "and the entry over in cell N1," that's the one off to the right here. When both of those are true, that means we want to apply the format, and I'll just make it a bright yellow here. Click OK. Click OK. Let's check that box, and see what's happening. Yes, it's working, and we'll do the same kind of thing with Contract and Hourly. It eventually will probably make this a little neater, a little tidier. We don't really need to see these. We could either hide the column or move 'em off to the right. It certainly helps to have them present when you're checking this out. So I think you can begin to see, and we can contrast this with what we saw. I made a copy of this. It's on the other worksheet. You can experiment with that a little bit, and come back and do some of the things that you saw me do here, if you're interested in doing this. I think it's a good combination of different techniques in Excel, different capabilities, Check Box capability, and Conditional Formatting to provide a display here that's really handy, and it's a good alternative to using filtering.