Creating Variable Conditional Formatting Rules

- In this worksheet, we're seeing rows highlighted in yellow, because they happen to be in the logistics department, and, notice this cell K2 has the word logistics. Below this is a description of what is actually occurring right now. We've got a format here, a conditional format, set up up in this worksheet so that when K2 has an entry in it, we look at the entire list here, and whenever we see that entry in column C, we make the entire row yellow. If K2 is blank, and I'll make it blank right now, simply by deleting the content of K2, no format at all is present.

Conditional formatting is a powerful tool. If you simply click this button, you'll see lots of built in choices here. Top/Bottom rules, highlight cell rules, based on content, also see a variety of color bars, color scales, icon sets. It's a great tool for applying visual features. You also have the capability of setting up a new rule, and then possibly later managing, or editing the rule, or getting rid of the rules. Let's imagine in this worksheet, we want to see how this rule that we saw earlier was setup.

Even now, if I click on cell K2, I can type in operations, for example. That's one of our departments, and we'll see what happens. Again. So, let's show how we can set up conditional formatting to work on the basis of what's happening in cell K2. I'm going to remove conditinoal formatting. You can remove conditional formatting from a worksheet by simply clicking on any cell and then going to conditinal formatting and clear rules from the entire sheet.

Now, let's imagine we do want to highlight the entire row here, based on what's in cell K2. There's no way to do this by way of conditional formatting, except by way of a formula. Now, we want the highlighting to take place across columns A through I. Let's begin by selecting the entire columns. Also, by doing this we are setting this up so that the list that we have below, which now is about 400 rows, could extend farther downward. Maybe 500 rows, maybe eventually 1,000.

We want this to be in effect, no matter how many rows we have here. So, by selecting the colunm letters, we're doing this for the entire columns, all the way down the entire worksheet. Let's go to conditional formatting. And, rather than using some of the standard features, that allows us to us data bars, or colors, or icon sets, let's go right to 'new rule.' We're going to set up a new rule. One of the capabilities of a new formatting rule is 'use a formula to determine which cells to format,' and in effect, we're setting this up to be a variable.

We're saying we will change what's going to be highlighted based on what's happening in cell K2, and that will vary. So the formula we use here at first, is not exactly obvious. I'm going to click cell C1. Now, C1 itself doesn't contain one of the department names, it contains the word department. But, beacsue we've selected all the columns across, C1 is what we refer to right now. That's not exactly obvious. Now, as we focus on any individual cell here think of it as looking into column C for what it needs to do.

The reference here, that's an absolute reference, is only relevant for column C. So, we want the reference here to be $C1. In other words, take out the second $ there. For every single cell that's going to be used for conditional formatting here, we want it to look into column C, to see if that's equal to whatever is in K2. and that is a full absolute address. Once again, rephrasing how this is going to be interpreted, we're saying for every cell that's highlighted here, let's go look in column C for that particular cell, and see if it's equal to the content of cell K2, and if it is, let's apply a format.

Acron-color is probably the most obvious, so let's use that, I used yellow, let's use that again. You can see through it; dark colors don't work so well. Click OK, and then click OK, and it appears to be working. Instead of operations, let's type in logistics. Now, this is similar to what we could be doing with the filter, but we're not hiding the other data. We're simply highlighting the data that we do want to see. To make this a little bit easier, because what if we start typing in some of these longer names, we could obviously make typos, and typing does take up some time, so, let's accompany this with a pick list.

Now, as we move to the right, here's our master list of all departments right over here. Let's set up this cell K2 here, so that it provides an arrow, and we're going to be able to pick from the list without typing. If we know that many many times we're going to be using manufacturing, it's going to be our biggest entry, it is already, we could put this at the top of the list, but otherwise let's say our list is alphabetized. You might want to make an exception to it, so, if we want manufacturing to be there quickly and easily, so we can pick it off the list from the top, we can move this upward by dragging it's top or bottom edge upward with the shift key.

We'll put this on top, right there, click it with the mouse first. Let's set up the pick list here by way of what's called Data Validation. It's on the 'data' tab on the ribbon. Data Validation. On the settings tab, under 'allow,' let's choose 'list'. We're going to be referring to a list. The source of the list is these cells in column M. M1 down to M23.

Click OK. We see the drop arrow. We make our choice. Is it manufacturing admin? We don't see any of those over there, maybe their farther down the list. There we are, we've got a few down there. Not so many in the top part of the list. But the point now is, and we don't need to see that list all the time, eventually we might want to hide it or move it off to the side, if we're using manufacturing by itself, often, let's click it there, and we don't see that in our list early on either, but farther on we do. So we don't always see it on the screen immediately unless we do some scrolling.

Earlier we saw 'logistics' and 'operations,' of course those are still going to work in the same way that we saw them earlier, but the pick list just makes it simpler. We're combining the capabilities of pick list along with a conditinoal formatting formula. When we set this up, we highlighted these cells, so in effect, we also have something going on up here, but this entry here is never logistics, so we don't see any color changes. In a certain sense, we don't need conditional formatting here, it didn't hurt.

We can remove it, not really required here, but we could go to the home tab, and simply, for these cells only, conditional formatting clear the rules from the selected cells. Now, in some cases, that is critical, depending upon how you're using it. But we see the example here, being used 'conditional formatting' along with a pick list, this list by the way doesn't have to stay here, it wouldn't hurt. We could hide the column, move it way off to the side, or possibly put it on another worksheet, but it does facilitate the creation of the pick list here.

Recognize 2 here, if this is blank, we see what's happening, no format whatsoever. So, using a combination of techniques, we're using conditional formatting based on a variable, and also using a pick list that allows us to quickly come up with some entries. Compliance is a small group, we don't see any entries there for awhile, but it's down there as well too. So, it works with all of our entries here, and it's a great tool for getting things done quickly. Remember, like a filter, it's highlighting the data, or showing us the data, we want to see, but unlike a filter, it's not hiding the other data.

So, a great combination of tools here to highlight the data we need to see.