Join Dennis Taylor for an in-depth discussion in this video Applying conditional formatting across rows of data, part of Excel Tips Weekly.
- 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.
Author
Updated
1/12/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 14m 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: Applying conditional formatting across rows of data