Transcripts
1. Introduction to Formula-Based Formatting : everyone welcome to Section eight formula based formatting the sections a little bit different because rather than covering a new category of formulas and learning new functions and syntax, we're actually gonna be using the formulas we've already learned in a new context in this case to create custom formula based formatting rules. So we're gonna start with just a quick section on creating and editing your formatting rules. Then we're gonna go into a series of demos. Demo One is about highlighting every other row using the mod function. The second demo is gonna cover formatting cells based on the value of another cell, which is very useful one. The third demo is going to be about formatting rose using statistical functions. And last but not least, I'll show you how to format rose using text functions on. And these are just some examples. The beauty of this is that just like there's an infinite number of ways to use the formulas we've learned, we can also apply those formulas to an infinite number of custom formatting options. So really cool creative things that you can do here last but not least as always, to files to download, got our excel for analysts formula formatting. PDF file. Go ahead and print that out, and then the Section eight formula formatting Excel file. So again, this is going to be a really hands on section. Ah, lot of working with Excel. So go ahead and open that up, Get yourself familiar and let's go.
2. Creating, Editing & Managing Formula-Based Rules: so I want to take a minute to talk about conditional formatting and excel. It's one of my favorite things. I love conditional formatting because once you learn it, it's pretty easy to do, and it absolutely blows people away. I swear. You just slap a color scale on a column and people will think you're an absolute rock star . So many of you are probably familiar with the stock conditional formatting rules. So highlighting rules your data bars, color scales, icon sets that's so great, so well and good. But let's leave that for the Children. Let's talk about how to go rogue and actually create our own conditional formats. And let's do it using formula based rules. This is kind of one of those next level Excel tricks that really opens the door to do some pretty incredible things. So first off, just want to cover creating and editing those formula based rules basically in the home tab . If you click the conditional formatting drop down, you'll see this menu here on the left. Um, the 1st 5 options here are those kind of basic stock conditional formatting rules that we're gonna leave for another time. These three options down here. This is this is our sandbox. This is where we're gonna play. So you could just create new rule here, and this little pop up box will show up. And what we're interested in is this last option Here, under the select a rule type window, use a formula to determine which sells to format. So once we click that this window appears here, and this is where we write our formula, so it's a little bit trickier because we don't get the little helper box to guide us through. Um, but at this point, I mean, we're in section eight. You guys should be experts. By now, we're gonna have to rely on our intellect and our memory and our pure instinct. So this is gonna be fun. So this is where you write your formula, which is basically saying, if this formula is true, then I want to form at the cells. As such, you click the format button, choose your options, you fill your border, your font, all that good stuff and then press. OK, so here we have showcased just one example of a formula based formatting rule. In this example, we're formatting the cells in columns be through H with a green Phil and a bold font, but only when the state name is equal to the value and sell. See two, which is this drop down? Sell up here? Eso. One thing to note is that when you're working with formula based formatting rules, the reference types become incredibly important. So understanding when to use fixed versus one to use relative references is critical. So I'm gonna spend a lot of time talking about that. So let's hop over to excel and actually see what this looks like. I've opened up the Section eight formula formatting Excel File. Go ahead and do the same thing. Let's take a look at what we're working with two tabs here. The first should look familiar. This is our price checker example that we built out during the look up reference section, particularly when recovering the index and match functions. Second tab is called State Data. This is just some raw state and year level data showing total population and student population. So these are the two tabs that we're gonna be using for the demos in the upcoming lectures . So if I want to create a new formula based formatting rule. First thing I want to do is select the cells where I want the rule to be applied. In this case, I'll just select seat 3 37 I took the home tab. Conditional formatting dropped down new rule. Use a formula to determine which sells to format. And then here's where I'm gonna type my formula. And then once that's written, I'm gonna click format. And here's where I'm going to select my actual format to apply when my formulas true. So cancel out of that and then once I have rules written just going to go into conditional formatting again going to manage rules. And then I can either look at rules for the current selection, which right now is sell age 10. Or I can say, show me all the rules in this current worksheet. Right now, there are no rules applied, but as we start writing them, you'll see this list start to populate, and this is where you can edit your rules. You connected the formulas within your rules. You can edit the formatting settings, or you can delete your rules out together. Um, so there you go. Let's get ready for demo number one
3. Highlighting Every Other Row Using the MOD Function: All right, let's go ahead and dive into our first demo. So go ahead and open up the Section eight Excel file. We're gonna start on state Data tab on. Our challenge is going to be to create a formula based formatting rule that highlights every other row in this data right here. I'm so I may have lied in the last lecture when I said that you've learned all the formulas you need to use for this. We're gonna learn one more, and it's called mod M o D. And all that mod does is returns the remainder when you divide one number by another. So, for example, if a type equals mod, and then you'll see that this is pretty simple formula only two components, the number and your divisor. So if I put a four and a to close it out, I'll get a zero because four divides evenly into two. If I change that 4 to 5 now, I get a remainder of one because any odd number divided by two will return a remainder off one. So that's the mod function. And that's gonna be a critical piece to creating this formula based formatting rule. So first step is to create the data array that I want. This rule to be applied to in this case would select a to control shift arrow down two d 52 . So I've got my whole array selected now in the hometown. Gonna hit the conditional formatting drop down, create a new rule, use a formula to determine which sells to format. So every formula based formatting rule starts with the equal sign. I was going to start with my mod function and you can see how excels not really helping me along here, kind of on my own. So make sure you know exactly how your formula syntax looks before you jump into this step . So first piece of the mod is my first number, and that number is just going to be the row function with an open and close parenthesis. And that's basically gonna go down and say for each sequential row, let's see what the road number is, and the second number is gonna be too. So basically what I'm doing is simple division problem where I'm dividing the current road number row by row, divided by two. And the last piece of this function is gonna be equals one. So again, dividing the current row number by two and checking when the remainder is one. In other words, this is going to impact on Lee Odd rows. And when that's the case, I'm gonna choose the formatting that I want to apply. So press format. In this case, all I really want is a light green Phil. So I could also Are you going to border fund this case? Just a green Phil will do just fine. So as soon as I press OK, go back here a press, OK, one more time. And there you go. The rule has been applied, and as you can see, it's applying the custom formatting teach odd row because when each odd number is divided by two, it returns a one. So that seems to working properly and every wanted to edit this rule. Let's go back in the conditional formatting manage rules. There's my rule. Click at it and now if I change this to zero, I should see the same formatting applied to the even rows because in those cases the mod function will return zero because there's no remainder, so press okay again and I'll just hit this apply button here. And there you go. You see the formatting shift up toe, only the even rows. Um, last minute, at least if I want to delete this, you could press, delete and apply. And now it's gone. So there you go, highlighting every other row using a combination of the mod and row functions.
4. Formatting Cells Based on the Value of Another Cell: All right, So let's get started on demo number two formatting cells based on the value of another cell . This is a fun one. And it's a really useful one just because there are so many opportunities to apply similar rules in all sorts of different contexts. So in this example, we're gonna be using the price checker that we built during the look up in reference section. And if you recall, we used an index match function to return a price from a certain array in this case C three G seven, based on selections for product and size. So what we want to do here is create a custom formatting rule that searches for the selected price in eat 10 and highlights it in the array from C 33 g seven eso. What we're gonna do is start by just selecting the array that we want the rule to be applied to go into home conditional formatting new rule. We're gonna use a formula to determine which sells to format. And now, as always, we're going to start with an equals and just going to select the top left cell in my array . So sell C three set that equal to sell e 10 and I'm gonna leave it like this for now just to show you what happens if we leave the references fixed. So in the case of this form, it was true. What formatting do we want to apply? Well, let's choose a nice bright yellow and a bold font just to make the selection pock, so impress. Okay, that will apply the rule. And as you can see, nothing's happened. So right now we have extra small sweater selected, which returns price of $30. So in theory, we would expect self C seven to B, displaying the custom formatting rules that we just set. What happens if we choose extra small socks, which is $4 which sets sell E 10 equal to C three? Now we're seeing the formatting rule applied to the entire array. And to understand what's going on, let's go back into conditional formatting manage rules. There's nothing in our current selection, which is Selby 10. So I need to change this to this worksheet. There's my rule, gonna select it and have at it. And so now just thinking about what this rule is telling us it's basically just saying any time that cell C three and Onley seat three since it's fixed is equal to sell, eat 10. Apply this formatting and remember, we're applying it to the entire array that we had selected. So Excel is actually doing its job because in this case, when I have extra small socks selected, C three does, in fact, equally 10. So this formatting rule is being applied. It's just not working quite how we want it to. And the key is to remove the fixed references here, which will allow the C three to shift toe any cell, any row, any column within the array that we've set. Now that's a relative reference. So now when I press OK and hit, apply Onley, the four is selected, and now to test this out, we can choose a number of different products and sizes and watch that highlighted, so shift accordingly. So there you go, just one example of formatting and cell based on the value of another cell
5. Formatting Cells Using Statistical Functions: All right. So for this next demo, I want to show you some examples of how you conform at cells based on statistical rules or functions. So let's jump back into the Section Eight Excel workbook into the state data tab. Um, and the first example I want to show you is highlighting cells within a specific column that meet a certain value. So in this case, let's just highlight the cells where total population is greater than five million. To do that, you select C two control shift down to C 52. I'm going to select conditional formatting new rule. Use a formula now I'm gonna do is type equals. Si two is greater than five 1,000,000. Now. The one piece that I need to change before I submit this is to convert the row reference from fixed to relative. So the sea is OK. In fact, it sees the only column in this array. But the row two needs to be relative because I need to check the population value in every row, not just wrote to someone delete the dollar, sign there and then select my format. And let's just do kind of ah, light orangey shade here Press. OK, I'm gonna press. OK, there you go. Now that we see that just within this column that I've applied the rule to all of the values about five million are now highlighted. I can also select the same range, going to conditional formatting. It's at a new rule using a formula this time I'm gonna say equals C two again Making the row reference relative equals the max of entire column C. And what I want to do here is basically identify which row and column C is the maximum value and format it accordingly. So choose a formatting rule. Let's go into border. And, uh, let's give it full outline. Border press. OK, I'm press okay now, As you can see, the value of 33.8 million, which is California's population, is the greatest value in column C. Therefore, it's the only one that receives that formatting room. Um, so let's go ahead and going to manage rules for the whole worksheet and let's delete those two. And now what I want to show you is if we basically take the same rule where total population is greater than five million, but rather than just highlighting the cells. In column C, I want to highlight the entire row in which total population is greater than five million. What I can do is basically right, the same rule. But before I do select the entire array from eighth or D rather than just C two through C 52 eso again conditional formatting new rule use the formula and then same exact formula equals C two with a relative row reference greater than five million. Format it with light orange show. And now, as you can see, we're still identifying the roads. Were total populations greater than five million? The only difference is that in this case, the formatting is applied to the entire row. Because when we created the formula based formatting room, our selection included all of the columns from a through D. Um, and now we can go ahead and very easily edit this rule, So I'm gonna go ahead and edit it. So let's say if we wanted to, rather than select Rose, where total population is great in five million. If you wanted to select Rose where total student population is greater than five million, I would just change the sea to a D press OK and apply. And as you can see, there's only one state where the student population was greater than five million and that was California. So last but not least, I can also create additional statistical formulas here. So rather than just checking greater than or less than or equal to, I can edit and save. Let's check if the ratio of students to total population so d to divided by C two with a relative or a reference inference. Disease is greater than 25% 250.25 Now I press supply. As you can see, it's updated to include many more rows. And to check that, you know, we can add a new column called Student Top Percentage, which is just equal to D. Divided by C, format it as a percentage and apply it down. And then, as you can see on Lee, that rose better greater than 25 have applied the formatting, so the rule seems to be working properly. Um, so there you go just a few examples of how you can use the statistical functions that we've covered as part of formula based formatting rules
6. Formatting Cells Using Text Functions & Logical operators: So for the final demonstration, I want to show you how you conform at cells based on text functions as well as logical operators. I'm so we're gonna go back to the state data tab. I've eliminated all of my existing rules were to start from scratch. And the first thing I want to do is just highlight all the state names that begin with the letter I. So I'll select a to control shift down to a 52 conditional formatting New rule. Use a formula. And now, since I want to return the value of the first character I need to use, they left function. So type equals left open the parenthesis. Select a to but allow the role reference to move. So make it relative. And now, one little side note Here you may have noticed that as I moved through these formulas attend to click to choose different positions as opposed to using a keyboard arrows. That's because if you use the arrows, it kind of automatically tries to insert references for you, which can be really frustrating if you don't want references. Eso My recommendation is when you need to do things like change certain characters and a function just to use your mouse and your cursor to try to jump right into that piece of the function and edit it directly. You've probably already run across that issue and realized that it could be really frustrating. Sometimes it's a little tip there. In any case, we're saying the left of a to and the number of characters were looking at is just one. Someone do comma. One close. The parenthesis equals that letter I in quotes. So that's it. So the function is left of a 21 character equals I. And so when this formula is true, the format will be, let's say, bold with a blue background purse. OK, and there you go. It's highlighted. Idaho, Illinois, Indiana and Iowa. So working properly now, one other tip. If you're calling the last lecture, we apply to function or format two column C and then in order to apply it to the entire row , we deleted it, made a new selection and then rewrote the formula. This time around, I want to show you a quicker way to do the same thing. And to do it, I'm gonna go into manage rules. Check this worksheet so I can see all of them select my rule. And then you see where it says applies to a two through a 52. Just gonna choose the selector, and I can either drag a new array out. Or it could just click in and change the A to D, and then when he hit, apply it applies the formatting room all the way out through column de as opposed to just column a so much quicker way to do the same thing. That's the first rule. Um, now what if we want to get a little bit more complicated and insert some logical operators in here? So what I want to do now is say, Let's highlight the Rose where the state name starts with an I and ends with an A. So we're both Those conditions are true, so we'll manage. My rules. Go into edit this one. And if you recall that one criteria one and two need to be true, I need to use an and statement. I'm going to start right after the equal sign, take my and opened the parenthesis. The left function is conditioned, one in a press comma to get to condition, too. And so, if my first condition is that the left most character is I, The second condition that I need to meet is that the right most character is a use a right function here. Same reference here. I'll just do a two, and I can either go up and select the A to and then delete the dollar sign. Or, you know, I could just type this by hand as well. Pretty simple a to and then a single right. Most character, close apprentices Bruce equals and then lower case A in quotes Close the princess and now having and statement that includes two components left. Most one character equals I, and the right most one character equals lower case A. I've hit OK and apply. As you can see, the formatting is now on Lee applying to the two states which meet this criteria Indiana and Iowa. So just a few random examples, but again, a virtually infinite number of applications for this kind of stuff. So that wraps up the formula based conditional formatting section. Feel free to reach out if you have any questions. Next up we have a ray functions, so prepare yourself
7. HOMEWORK: Formula-Based Formatting: All right, You've wrapped up chapter eight. You're cruising along. You finished the formula formatting section. Now it's time for your next homework assignment. Open up the Excel homework exercise file, head to the formula formatting tab and take a look at the instructions. We've got six steps here that are gonna allow you to transform this data range from a 12 G 25 using tools like the format painter, conditional formatting rules, color scales, icon sets and then some advanced formula based formatting rules based on values and text. Good luck.
Chris Dutton, Founder, Excel Maven
