Streamline Your Workflow with Bilty Format in Excel for Communications & Media

Bilty format in excel for Communications & Media

hello and welcome to conditional formatting based on another cell in Microsoft Excel my name is Jeff I'm glad you're here let's just jump right in so it's relatively straightforward in Microsoft Excel to apply conditional formatting to a cell based on the value of that cell okay what we're going to do is we're going to apply conditional formatting to some cells based on the values in other cells okay so let's just go ahead and flip over to excel now what we're going to try to do is format this range based on the value of these cells okay so as an example what would be pretty straightforward is to format this status column based on the status column values so we would go to conditional formatting highlight cell rules equal to and we would say if this cell value is equal to open then apply whatever format you want we're going to go with green and click ok that is applying conditional formatting based on the value of those same cells that's not what we're going to try to do here what we want to do is apply conditional formatting to this entire range based on the values in the status column to do that we're going to use a different option we're going to go to conditional formatting new rule and the key is to use a formula to determine which cells to format and we're going to write a conditional formatting formula in this field we're going to pick our format and then we're going to click ok but before we unpack this formula we need to First understand formulas in general okay a formula can return a calculated value and we're probably used to that if we've been working in Excel for a while like you know the sum or average or count what we want to do is write a formula that returns a true or false value if the formula returns true then it will apply this format if the formula returns false it will not apply this format so let's just kind of understand what this idea means I can write a formula like equals and I can say does this equal 215. and if I hit enter the formula returns false what if I said does B7 equal 215087 and I hit enter Then I get true so this is an example of a formula that returns a true or false value and that's what we're going to want to write when we go to write our conditional formatting formula so the other thing we have to be aware of is what happens if we fill a formula down into the right okay so as an example does this equal the word open okay and I hit enter and I get true and then I fill this formula down and we should see that this works so open true we got a couple of falses for paid we got a true bunch of falses open true okay so this looks like it worked what happens if I fill this formula to the right now we see there are some changes here in our formula results so let's take a look when I wrote the formula I wrote C7 okay when I filled that formula down Excel took the liberty of rewriting my cell reference from C7 to C8 to C9 to C10 and that was exactly what we wanted but when I filled this formula to the right Excel took the liberty of changing my cell reference of C7 to D7 then to E7 and then to F7 okay so what we're trying to do is write one formula that can be filled down into the right that gives us the correct true or false values for this conditional formatting formula in other words we want all of these cells to 0.2 C7 so let's take away all this let's take away all this and let's go back and edit this and let's talk about relative versus absolute references C7 is a relative reference that just means as we fill it down or right Excel is going to take the liberty of updating the cell reference ingly if I want I can lock down the column reference by putting a dollar sign in front of the column reference this means if I fill it right Excel is not going to change C to D to E I could also or put a dollar sign in front of the seven that's going to lock down the row reference that means if I were to fill this down it's not going to change the row reference okay so as an example if I hit enter and I fill this down all of these are going to be C7 okay that's what I mean by an absolute reference Excel is not going to rewrite that cell reference and that's not exactly what we want what we really want is an absolute column reference so as I fill right Excel won't change the C but a relative row reference so that as I fill the formula down it does change 7 to 8. so let's see what happens if I fill this down and then I filled this right okay so what we have is all of these formulas are referencing column C which is exactly what I want and as I fill the rows down they are updating to refer to the updated row okay and this is exactly what I want so imagine this this is a Formula that returns true or false okay and this entire row is referencing column C and these are referencing column C column C all of these are referencing column C this is exactly the kind of formula we need to write in conditional formatting okay so let's go ahead and clear this out and let's select this range and now let's apply conditional formatting conditional formatting new rule and let's go with use a formula so here what is the formula that we want to write well the first thing to realize is that when we're writing a normal formula in a cell that computes a cell value we're writing the formula into the active cell what's the active cell well we look at this range and we see that although there is an entire range selected only one of these is the active cell and that is B7 okay and so what we want to do is pretend that we're writing this formula into the active cell into B7 and that becomes important when we're setting up the cell reference so we want to say equals we want to lock down that column reference so dollar sign C and then which row reference well it depends on where the active cell is the active cell is in row seven so we're going to use seven equals open okay if that formula returns true then what do we want to apply well we just pick whatever format we want and click OK and then click OK and as we can see we're using conditional formatting to format cells based on the value in another cell okay so can we do multiple conditions yeah we can totally do multiple conditions so let's cruise over here and let's try it again select our range conditional formatting new rule use a formula we're going to pretend that we're writing our formula into this active cell the active cell is B7 so the formula that we want is does C7 equal open okay what if we wanted to add another condition well we would just use the and function so now we can string together as many conditions as we want so does C7 equal open and is the amount greater than 5000. so is e 7 greater than five thousand and we could continue stringing along additional conditions if we want okay so if that is true in other words if both of those conditions are true then the and function returns true then we want to go ahead and apply this and by the way if we wanted either of them to be true to get the formatting then instead of and we would use or the way the or works is if any of the arguments are true then or returns true for and if all of the arguments are true then and returns true so we click OK and now we got it open 5000 yes open 5000 yes this is open it's less than five thousand so it doesn't get the formatting cool so to recap to use conditional formatting based on another cell we need to use a conditional formatting formula we're going to pretend that we're writing this formula into the active cell and that when we click OK Excel is going to fill that conditional formatting formula throughout the entire selected range we're going to write a formula that returns a true or false value if it returns true the format is applied if it returns false the format is not applied and then we need to use our absolute or relative cell references so that it continues to point at the desired column that we want it to look at cool all right hey thanks for checking out that video If you use Excel often be sure to check out the seven time hacks video this is a collection of seven time saving techniques that I wish I knew years ago this video is a production of excel University [Music]