Using check boxes and Conditional Formatting together for dynamic displays

- If you're seeing this worksheet for the first time your eye is probably drawn over to column M where we see what are called check boxes, as well as some colored panels. I'm going to check the box for Contract. And you can see what's happening in the data over in columns A through J, every time we see the word Contract in column D, all that row is highlighted. If I do the same for Hourly, the Hourly box, we see those highlighted. This is similar to a filter, except it's not hiding data, it's simply highlighting the data.

So these check boxes are associated with Conditional Formatting. As I uncheck the box for Contract, we can see what's happening. So it's quickly obvious what is being done with these boxes and how they're being set up. But let's explore a little bit, I'll scroll to the right, and uncheck a couple of the boxes. I'm gonna make column M wider. And we see these words, True, False, False. Look what happens when I check the box for Contract.

Second of those entries became True. I'll uncheck the box for Full TIme and Half-Time, and the top entry here is False. These boxes are associated with these three cells, respectively. And over to the left we can click within the data. If we go to the Home tab, and take a look at Conditional Formatting, we can observe the existing rules by managing the rules. This does not necessarily mean we're about to change them. Manage Rules, we've got three rules set up, as you might imagine.

And I'm going to click the blue one here and Edit. Now again, I will not be changing it but we'll simply look at this. So what we're doing, in fact, is showing what's being set up and then we'll backtrack and show you how to do this. There's a formula here that says, in effect, if the column D entry, for a moment we're not seeing that in the background, the column D entry there is hourly and if cell N3 happens to be true, then we'll make those cells be blue. Now at the moment it's not, so that's why we're not seeing any blue off to the left.

So, it's kind of hard sometimes to put the pieces together if we see existing rules, but let's, in effect, start over here. What I'm going to do is click within the data here and simply remove all Conditional Formatting. In fact, I didn't even have to click there, necessarily, because when you go to Conditional Formatting you can clear the rules from the entire sheet. So no Conditional Formatting in effect right now. Clicking these check boxes does nothing. So how do we get to these check boxes? Where do they come from? It's what we call a Form Control.

And what you'll need to use is the Developer tab in the ribbon. And if it's not visible, as is the case right now on my screen, you'll need to make the Developer tab present. Simply right click any existing tab, customize the ribbon. And along the right side you'll see a number of choices, including Developer. Now you might see more choices, additional choices, then you're seeing right now on my screen. But check the box for Developer, click Okay. And now on the Developer tab, they have a section called Controls, and a button for Insert, and there's a button there for Check Box.

It also has something called a Combo Box, there's also over here we've got some other kinds of buttons off to the left here. When you slide over these a pop up tells you what it is and some others we're gonna be using Check Box. So when I click Check Box, and move the mouse around, it's essentially inviting me to put this somewhere so I'll just put it right here. Now I'm going to need three of these so as I drag one of these I'll hold down on the control key, drag it down to here, and then do that again to set up three of these.

Obviously the wording's going to be changed, and we want to add color. What we do with these check boxes is associate them with a cell. And with a check box, if you right click it you should get some choices here, including Format Control. So, for the Format Control here, I want to link this to a cell. It can be any cell I want. I'm gonna link it off to the right to this cell right here, that's N9. 3D shading helps a little bit for the visual on the box, click Okay.

And you see that if I click outside of here, and then check the box, look what's happening off to the right, as I uncheck it. So it's true or false, when it's checked it's True, when it's unchecked it's False. You don't see that until you click outside of the box here to make sure it's not selected. Now I want to do the same thing here, so I'll right click in the next box, and Format Control, 3D shading, Cell link, this time I'll put it in cell, N11, and click Okay.

And then in the lower box, right click Format Control, 3D shading, Cell link, I'll put this one right here, in cell N13. If we want to add color, this one is currently selected. Notice that there's a Format tab, a Contextual tab available on the ribbon when a box is selected. Here's a Shape Fill, and I'll use a blue here, maybe a darker blue for the example I'm setting up for Hourly. The box above it, right click that, actually just click it, and there too, from the Format tab up in the ribbon Shape Fill, and this time I'll use a green, a slightly different green than the other one.

And then the third box, the one on top right here, hold down control and click this, and on the Format tab, Shape Fill, a yellow, slightly different kind of yellow. The wording, of course, is off so I'll control click top box, take out the wording that says Check Box 12, and put in Full Time and Half-Time and click outside the box. Control click the next box, and again, get rid of this and put in Contract, in this case.

Click outside the box, control click the last box and we'll get rid of that text there, then I'll type in Hourly. Now, in interest of time, I'll only set up two of these, but just so you get the idea. Keep in mind, even now, even though we're not ready to, and even now, although we don't have Conditional Formatting set up, you do see what's happening here as we check and uncheck the boxes. And any time we need to change the format of the box, the color or the sizing, be sure to hold down the control key as you click it. This box, of course, needs to be made wider if we want that text to show, as we would.

Something like that. Okay, now, the idea is we want to use Conditional Formatting, and we want, for example, cells to be highlighted in green whenever we see the word Contract in column D, and when this entry over here in N11 is true. So again, for the moment, it's not. So when those two conditions are true, when the word Contract appears in column D, and the word True appears in N11, then we want those cells to be green.

So in setting this up, be sure to select all of the data. Now if you highlight the columns themselves, you also have the advantage of allowing for future entries in the list, in other words, if the list grows, or shrinks, we will not have to redefine Conditional Formatting. So by selecting columns A through J, in effect, we're saying this list could grow to be over a million rows. The Conditional Formatting will be in effect in all cases here. So as we go to Conditional Formatting, remember, this is on the Home tab, we jump immediately to a New Rule.

And that rule will be set up by way of formula. You use a formula to determine which cells to format. Now at the moment, all the cells within the data we're concerned about are selected. So what we want to be true, first of all, is that the column D entry. So I'm typing equal D one equals double quote Contract Now within double quotes. At all times, within our Conditonal Formatting cells here, we're always looking into column D so we want that to be an absolute reference.

So instead of a D one, we want a dollar sign in front of this. You could be pressing the F four key to apply this, as well. Now we want that to be true, but we also want something else to be true. We want to make sure that the cell that's associated with the Contract check box is true. So scrolling off to the right a little bit, that's going to be cell N11. Now we need two things to be true, so following our equal sign we want the word and. It doesn't have to be capitalized, but I'm doing this here. I will display this larger in a moment on the screen so you can see it even better.

So we need two things to be true for these cells to be green. We want to make sure that the entry in column D is always Contract comma, and the explicit entry in cell N11 is always true. Now simply by indicating it here, by its very presence, that means it's true. If it were false, this would cause our test here to fail. That might seem a little strange if you've never used this construction before. When both of these are true, we want a particular format, and I'm simply gonna use a Fill of that green in the background.

I'm going to highlight this formula and then paste it on the worksheet so we can see it more clearly. It's not hurtful, but you don't need to do that. So I'm pressing control C as this is highlighted. So I'll click Okay, click outside the data, then click the box for Contract. Then we see what's happened. Now the formula that's in effect, and this is what I said, suggested earlier, is not typically displayed here, but I will display it right here. That's the rule in place, and it applies for all the data in columns A through J.

And again, in English it's saying anytime a cell is equal to the word Contract, anytime a cell in column D is equal to the word Contract, and N11 is true, then apply the green format. So if I uncheck the box out here for Contract, the word True will become False, and this test it would fail. Now in setting up the other rules, we don't have to go through all that retyping, we'll simply copy one of these. I'll only do the one for Hourly simply by dragging across here and going to Conditional Formatting and managing the rules.

And here's the one rule we have in place, I'm gonna edit this, simply to copy it. So I'll highlight this, press control C, Okay, and create a new rule. Use a formula, I'll paste it in here, control V. And this time I'll make sure that the column D entry, instead of being Contract, is going to be Hourly. And instead of N11 being true, in this case it will be N13.

So when N13 is True, and that's an absolute address, and when the entry in column D is Hourly, we want to apply the format of blue as a fill color. Now remember, you could use other kinds of formatting, border features, fonts, number formats. In this case it wouldn't apply, certainly some of the others, as well. Click Okay, and Okay, and Okay. Click the box for Hourly, and the Hourly cells are blue. Green, Contract. You see what's happening.

If I didn't paste the formula this time but it looks almost like this except we're seeing the word Hourly instead of Contract, and instead of N11 we're seeing N13. Now eventually you might want to move these around a little bit. Remember, if you want to move these and resize these, you hold down control, click this, move it around, possibly making it larger, that sort of thing, you could do that, as you prefer. Maybe make them that big as we saw earlier. So lots of different other techniques you might want to employ here to beef up the visuals, but the main idea here is we're using a check box.

Remember, that's available off of the Developer tab, which you might need to make available, Insert, there's the Check Box, one of our Form Controls. And we combine that with the settings of Conditional Formatting to make this worksheet a lot more visual. And as we are making presentations, for example, the data will jump out at us as we're making these choices.