Join Dennis Taylor for an in-depth discussion in this video Creating variable conditional formatting rules, part of Excel Tips Weekly.
- 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.
Author
Updated
2/23/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 24m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Creating variable conditional formatting rules