Enhance your Excel skillset by mastering advanced conditional formatting with formulas, enabling dynamic cell formatting based on external data points. Learn practical techniques that highlight important business metrics clearly and efficiently.
Key Insights
- Apply advanced conditional formatting by using custom Excel formulas to format cells based on values from another location, such as making check numbers green if column K equals "Y".
- Utilize mixed reference formulas to dynamically format entire rows, such as highlighting in red the full rows for salespeople whose checks haven't cleared (column K equals "N").
- Implement conditional formatting for specific business scenarios, including clearly identifying salespeople with sales greater than 50,000 or those with sales below 20,000, improving data visibility and decision-making efficiency.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Advanced Conditional Formatting. Advanced Conditional Formatting is basically conditional formatting with formulas. You can format cells based on values in another location.
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 a 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 Formula to Determine Which Cells to Format.
Now, if you don't see these options on the Mac, you'll have to click the drop-down and choose Classic. Then you will see options that allow you to choose Use Formula to Determine Which Cells to Format. Okay, 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 in double quotes 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. 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 whose 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 are not clear. Go back to Conditional Formatting, New Rule.
Again, use 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, 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.
21, which is the row number, needs to be flexible because we have multiple rows of datas we're examining, and it's okay 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 I'll 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'll automatically update in the sheet.
So you only have to write the formula once, apply the conditional formatting using that formula, and it'll automatically update. The exercise in the class relates to salespeople. I like exercise one 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 formula. We're focusing on the sales column. And 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 this cell. I can actually just click on the first value and look for values that are greater than 50,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 it 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 50000. 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. One more exercise, highlight the entire row of any salesperson whose sales are less than 20,000. So using the same strategy, I'm going to select the entire row of any salesperson.
I'm going to go over to the Home tab, choose Conditional Formatting, New Rule, Use Formula, go into the 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 correspond to Column C, but when I move over 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 E is locked, and I'm looking for values that are less than 20000.
For Format, 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 are 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 20000, press ENTER, and there we go.
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.