Applying conditional formatting across rows of data

- Excel's Conditional Formatting feature, found on the Home tab of the ribbon, is a great tool for highlighting data, making data stand out based on its value. In this worksheet of some 900 rows of transaction data, we'd like, for example, column F data when it exceeds 12 to stand out a little bit more prominently. We might also consider what's in column G as well. Let's first focus on the idea that we want to make a change in column F so that any number that's 12 or more is going to stand out with a color background or possibly some shading, other kinds of formatting features we could choose as well.

I'm going to select column F, and then from the Home tab choose Conditional Formatting. This is a rich feature which gives us lots of capabilities and lots of rules, and we can also set up new rules as well. I'm going to start with the simple idea that we simply want to highlight those cells that are Greater Than, and this could also be Greater Than or Equal To. From this dialog box that pops up we have some choices here. Greater Than, I'll go with that. If it's greater than 12, fine. This number, of course, isn't always what you want it to be, and you can certainly change that.

In the background, we're seeing already what's happening with this particular kind of formatting. If we want to keep that, fine. If not, we could try some other options here, or go to Custom Format and provide different kinds of either Fill, Border, Font, or Number formatting to make these numbers stand out. I'm going to go with a green background. If I don't like the greens here, I'll go to More Colors, and we've got lots of choices out here. How about a bright green? Click OK and OK and OK again, finally, and we see what's happening.

By the way, when you select an entire column, as I did here, the advantage is that as the list grows or shrinks, we don't have to redefine it. Slight disadvantage is that we've got formatting in cell F1. For the moment, I'll leave it there, but I will show you how to remove that. So the feature is dynamic. We can see what's happening here. If this 9 was a mistake and should have been a 13, I'll make it be 13, it will turn green, or if I press control z to undo, of course it will go back. Now, what I would really like to be able to do, though, is highlight the entire row, and try as you might, you can go back into Conditional Formatting, and you won't find any option for selecting the entire row.

Let's remove the Conditional Formatting and then take the steps necessary to highlight the entire row. In other words, in our list here we want this entire row to be green, if this entry here is over 12. So, we can click anywhere within our worksheet, go back to Conditional Formatting to remove Conditional Formatting, because we have options, and the official wording is, Clear the Formatting Rules, either from selected cells, had we highlighted them, or clear rules from the entire sheet.

That's what we're about to do. So, if we do want to highlight an entire row, if an entry in column F is greater than 12, let's select the data from columns A through G. Now possibly we could be highlighting all the data, but I want to leave the door open for the idea that this list might grow, and we don't want to have to constantly redefine our Conditional Formatting Rules if the list grows. So I'm, in effect, selecting all of columns A through G, all the way down to the bottom of the worksheet, which is over a million rows deep.

Now we go to Conditional Formatting, but instead of going into Highlighting the Cell Rules, as we did earlier, I'm going to go down to New Rule. We're going to set up a New Rule, and the rule type that we're going to select is Use a formula to determine which cells to format. Now, when I highlighted these columns here, from A through G, A1 becomes the active cell. When we write a formula, we keep that in mind. Here's what I will type as the formula, = F1. And you might be wondering why F1.

This is going to be always in column F. We have to make it an absolute address. If you're familiar with that concept, you know we can put dollar signs in front of F and 1 or simply in front of one or the other. If I press the Function key, F4, it'll put a dollar sign in various locations. I want it in front of column F. Now what this means is, think of any single cell in the worksheet at any given time, you want to be checking to see what's happening in column F. So it might seem a little strange to be putting in $F1.

but think of it as a floating reference. $F1 greater than 12, and by inference, that means any cell, before it's actually finished with this concept here of formatting, is going to be checking into column F to see if, in that particular row, if that value is over 12. Let's go to Format over here, and I'll again go to More Colors and use that bright green. Click OK and OK, and there's the rule. Now, I know that's rather small for some of you, depending upon your monitors.

I'm going to highlight this, press control C, and display it larger on the screen in a moment. So we've got our formatting rule in place, our Conditional Formatting Rule that says, for every single cell, go look in column F to see if that entry is greater than 12, and if it is, give it this format with the bright green background. Let's click OK, and there we see it. The formula, by the way, and you don't normally put it out here on the worksheet, but I will, is this. This formula, think of it as the rule, is the rule for this worksheet right now.

Now, we don't want the top row here to have that look, so I'll go back to Conditional Formatting and clear the rules from the selected cells. So, doing all this is not difficult, I think you can see that, but it's not obvious at all from Conditional Formatting Rules how to set this up, and we see what's happening here, and it truly is dynamic, and based on our previous example, I'll do this again, I'll change this 9 to a 14, and as I press enter, watch the entire row become green, from columns A through G, that is.

There it is, it happens that way. Or later maybe it should have been a 10. I'll change it back to 10. That, of course, is not greater than 12. Same thing will happen that way too. So, although relatively easy to set up, it's somewhat hidden in the scheme of things. This is the formula that we used. Remember, we selected all the columns here, and as this list grows and shrinks, we don't have to rewrite our Conditional Formatting. So, applying Conditional Formatting across a list, as we've seen here, straightforward, not exactly obvious, but certainly of value.

Many, many times I get this question when I'm doing live teaching. How do you highlight the entire row through the list? This is how we do it.