Create conditional formatting rules using formulas

- [Instructor] So we know that Excel has great built-in conditional formatting rules and we know that we can create our own custom rules, but now we want to take a look at how we can use formulas in our conditional formatting rules. After watching this video, you will know how to create a rule with a formula, and we'll be using and, or, and weekday formulas. Let's take a look at our rules formulas workbook. This is our worldwide sales for 2019. We have three questions that we're going to answer. The first one is if the order was shipped on a weekend, color the cell red. So we're going to be working with the shipped date column. I'll go ahead and click in H6. Go to conditional formatting and create a new rule. Use a formula to determine which cells to format and as I mentioned, we'll be using the weekday formula. Now if you're not familiar with the weekday formula, it takes a look at the date and looks at a calendar and says did this particular date fall on a weekend? And when you're using weekday, you can tell Excel which day of the week is the first day. The most commonly used is the number two to signify to Excel that Monday will be the first day, and if Monday is the result from the ship date that we're looking at, it will be day number one. So because we're looking for a weekday, which would be Saturday, we'll include a number five in because Saturday is the fifth day if Monday is the first day of the week. Now I'll be reviewing the weekday formula in the next chapter, so that is where you'll get more information about weekday. So using weekday, look at the shipped amount in H6. We don't want the row to be absolute, just the column. I'll put a comma in, and I'm going to insert a two because we're using that second option in Excel. Monday will be the first day of the week. I'll go ahead and close my parentheses and I want to know if the number of the day is greater than five. So Monday, Tuesday, Wednesday, Thursday, Friday. Friday's the fifth. I think I misspoke a moment ago. Saturday will be the sixth, so as long as it's greater than that five in my formula, if it's a six or a seven, that means it is Saturday or Sunday. And we want to go ahead and color the cell red. Let's go to format, fill, and red. Click okay, click okay again. That first cell did not meet that criteria, but let's apply it to the entire range of ship dates. We'll go to manage rules, applies to, I'll take that out of there, and select all the way down to row 206. Now let's go ahead and apply it, and we do see that we have some days that meet that criteria. Okay, our next question is change the font to red if the ship date was greater than 15 days after the order date and the ship date was greater than the required date. Once again, we can work with the shipped date column. However, if we change the font to red, we won't be able to see what we've applied, will we? Well let's go ahead and write the rule and then we can see where we want to apply that. So we'll start back in H6. Go to conditional formatting, new rule. Use the formula to determine which cells to format. So this is going to be an and statement, and the and statements and the or statements work exactly the same. With and, it has to meet both sets of criteria. Ship date has got to be greater than 15 days after the order date, and the ship date is greater than the required date. That means that we missed that required date. If it's an or statement, it just has to meet one set of criteria for the formatting to be applied. It would be that the ship date was greater than 15 days after the order date or the ship date was greater than the required date, so that's the difference in the two. So let's go ahead and start our formula. Equals and and open our parentheses. If the ship date was greater than the order date plus 15 days, so that's how you write that so that it reads it, look at the order date, add 15 days and let me know if it's greater than that. Comma, and this is where we put our second statement in. Is the shipped date greater than the required date? Greater than the required date. Now because we'll be applying this all the way down, let's make sure that just our column reference is absolute. Go ahead and close our parentheses, and we want to format this in a red font. Red and click okay. Let's click okay, go back to our rule, manage rules, and have it apply to the entire shipped column or we could have it apply it to the entire row. Just the invoice number, there's so many different ways that you can apply this. Let's have it do the entire row, so we'll go ahead and select the entire range, click apply. We do have some that meet that criteria. We'll go ahead and click okay. Perfect. Let's move on to our next question that we needed to answer. If the sale was in England, and it was greater than $10000, we want a green font displayed. So we want to highlight the sales, so let's work with the order amount. Conditional formatting, new rule. So this is going to be an and statement also. The sale will be in England and it will be a $10000 sale. Equals and country. Remember, we just want the column to be absolute. That equals, and then in quotations, we'll put the word England, and this should be an equals sign. Equals England, comma, and the amount of the sale, the order amount is greater than 10000. Go ahead and close our parentheses. And we'll format the font in green. Okay, and okay. Let's go back in and make sure that that rule is applied to everything in the order amount column. Okay, here we have some sales from England that are greater than $10000. So those are some conditional formatting rules using formulas that you can practice with and apply it to your own data. Just make sure you're comfortable writing formulas in conditional formatting rules and you'll be well prepared for the exam.