Excel Advanced Conditional Formatting

Play video

Advanced Conditional Formatting

Advanced conditional formatting is basically conditional formatting with formulas. You can format cells based on values in another location.

Conditional formatting is covered extensively in our NYC Excel classes. For those outside New York, find and compare the Excel classes near you or the best online Excel courses.

Step 1

For this first example, I'm going to pretend that I'm helping a business owner figure out which checks have cleared.

Now, when they're doing this themselves, they are stuck because they know that they can select some cells, and go to the home tab and simply choose Conditional Formatting, and format cells based on certain criteria using the values that are in that column.

But their problem is they don't know how to have the criteria be somewhere else other than the column they selected, And that's what we're going to take a look at here.

So the goal is to make the check numbers green if the value in Column K is equal to Y. So I'm going to go to Conditional Formatting, and I'm going to bypass all the presets, and go over to New Rule. When I go to New Rule, I'm going to choose the option, "Use a formula to determine which cells to format."

Now, if you don't see these options on the Mac, you'll have to click the dropdown and choose Classic. Then you will see options that allow you to choose "use a formula to determine which cells to format."

OK, so now, when should these check numbers be colored green? Well, starting with cell K8. So the formula is going to be equal, K8, equal, "Y." That is a true-false statement. If that statement returns true, then the format will be applied to the cells that are currently selected.

So I'll go and click Format. The font color I want is green, and I'll make the text bold, make it really stand out, and then I'll click OK. Now, if I did this correctly, as soon as I click OK, any cells that have a matching value in Column K that is equal to Y will be colored green. I'll click, OK. And as I take a look, I see that that is the case.

Now, I'll display the formula here so you can actually see it in the dialog box. It's pretty small. When I teach this in the classroom, people usually request for me to copy and paste the formula or the function in a larger font that they can see.

Step 2

That's step one. Let's say our business owner wants us to also focus on people who didn't pay, but they want the formatting to occur differently. They would like me to format the entire row for any salesperson who's check didn't clear.

So not only am I going to select one row, I'm going to select all the rows, because they're all potential candidates for being checks that have not cleared. Go back to Conditional Formatting, New Rule, again, "use a formula to determine which cells to format." I'll type in equal, the cell I'm going to start with is cell K21 this time. I can click on it, but I don't want dollar signs all around the cell reference. So I'll press F4, and I can keep pressing F4 until they all go away.

But for this particular formula, I need to use a mixed reference formula and lock Column K. Why? Because I am selecting multiple columns, and I want the criteria to only focus on Column K, so I'm locking that. Twenty-one, which is the row number, needs to be flexible because we have multiple rows of data we're examining, and it's OK for me to move to 22,23,24 and so on. That's actually appropriate.

Now instead of equal to Y in double-quotes, I'm going to put in the letter N. Now apply that format. That's a little more drastic. I'll go over to the fill and choose a red fill. I'll go back to font and make the font white, make the text bold as well. When I click OK, I get a preview of what the entire row is going to look like. So the entire row should have this format applied if the value in Column K is equal to N. I'll click OK.

Let's see how we did. There we go. And as I look at all the values everywhere, I see the letter N. I see the format has been applied. I'll go here and change the font color in this cell and reveal the formula that we used to get that result.

The nice thing about this is if you make any adjustments to the cleared status, it will automatically update in the sheet, so you only have to write the formula once, apply the conditional formatting. Using that formula, it'll automatically update.

Exercise 1

The exercise in the class relates to salespeople. I like Exercise 1 because the first six words tell you exactly what you need to do, which is highlight the name of any salesperson. And then I'm going to go over to Conditional Formatting, New Rule, "use a formula." We're focusing on the sales column. What are we looking for in the sales column? We're looking for values that are greater than 50,000?

So I'm going to go into the cell. I can actually just click on the first value. I look for values that are greater than, and then 5,000. Now, these don't need to be locked and they shouldn't be, so I'll just press F4 a couple of times until they're all gone.

Now, I'll go and apply the format. The format I like for this is a navy blue background, and I'll make the font white and bold. I'll click, OK, and then I'll click OK. Now I may not be too clear if this is actually selecting the right salespeople. So what I can do is I can go to the data tab and apply a filter.

When I apply that filter, I'm going to look for numbers that are greater than, and then I'll type in 5,000, and when I click OK, I'm filtering for all the values that are greater than 50,000, and I can see they're all formatted with that blue background and white text. I'm going to clear the filter.

Exercise 2

One more exercise. Highlight the entire role of any salesperson whose sales are less than 2,000.

So using the same strategy, I'm going to select the entire row, any salesperson. I'm going to go over to the home tab, choose Conditional Formatting, New Rule, "use a formula, " go into this cell. I'm going to start with this first value. And this is where we have to use a mixed reference. We're selecting multiple columns, and I only want to focus on the first column. I don't want this to then look at the columns to the right of the column I'm in, because my starting point is Column C.

So it might start looking at the criteria in Column E that corresponds with Column C, but when I move forward to D, it'll move over to F, and there's nothing in F. So I need to lock Column E. I'll press F4. There it is. Only Column B is locked, and I'm looking for values that are less than 2,000. Reformat. I'll use the same format we used last time, red fill. Go to Font, make it bold and use white text. I'll click OK.

That's how the entire row is going to look if any of these values is less than 20,000. I'll click OK, and there are the values highlighted that are less than 20,000. I want to double-check Number Filter. Look for values that are less than 2,000. Press ENTER, and there we go.

Recap

So in this section, we looked at how you can apply advanced conditional formatting, and that's formatting based on a formula that may reference other ranges in a table.