Excel Magic Trick 787: Conditional Formatting Basic To Advanced (30 Examples)

welcome to EXO magic number 787 hey if you want to download this workbook click on the link below the video when you download this video you'll find a topic sheet 30 conditional formatting tricks this is an epic video as you can tell now to make an epic video like this is going to be well over an hour if you look below the video there's a show more button if you click that you'll see this huge list and it will have a time and you can click on any one of the topics you want and jump to that if you watch the video from beginning to end you will get kind of most of what there is about conditional formatting we have seven built-in tricks which are really one click and then a bunch of formula methods of creating conditional formatting this is where the real power is the versatility and breadth of conditional formatting applications is a huge one you can do a formula a true/false for it so there's a bunch of examples not only that but when you download this workbook there's some links here so if I want to jump to this greater than 600 I click on that and it jumps to the sheet CF 4 these are links so if I want to go straight to the highlight row an actual exceeds budget I click on this and boom I'm right there let's start on sheet CF 1 let's click on the sheet CF 1 it all comes down to this conditional formatting applies formatting to a cell right okay so I'm looking through the list and I want to say above average well that one's above average this one's above average this one right here is above average and I'm going to add red now notice what did I do I asked a question and the question I asked is is it above average the only two answers to that question is either true or false so all conditional formatting applies to formatting to a cell and the logical when the logical tetes evaluates to true formatting is applied logical tests evaluates to false formatting is not apply in this is for built-in or formula conditional formatting rules I'm going to control Z here all right above-average sales let's highlight this whole column control shift down arrow conditional formatting and in 2007 and 10 they have a bunch of amazing built-in features now look what we're going to do above average sales so we go to top or bottom rules and then above average it's simply going to ask you what color here is the drop down you can see the default but down here is the real power custom format now custom format and the olden days had fill border and fonts now it has numbers two thousand seven ten this is awesome this means you could change the number formatting I'm just going to add for this particular one red and then the font color will be white you can do whatever you want once it's true it gets applied alright so that's pretty cool there later we'll see how to do above average of sales with formulas and highlight the whole row let's go over here the next one is below average sales so I'm going to highlight this whole column go up to conditional formatting top and bottom rules and let's go below average I'm going to accept the default let's look at another one CF three this one I want to see the top five so control shift down arrow conditional formatting top and bottom rules notice it says top 10 items top 10% let's do top 10 items and I'm going to select five now this isn't connected to a cell if you knew how to do formulas which we'll learn how to do then you could put the number here and when you change it if if this is applied with formulas it'll change alright but now there it is the top five boom one two three four five conditional formatting trick for greater than 600 now this is a built-in feature and we will be able to connect it to a cell ctrl shift down arrow conditional formatting and highlighting cell rules great then less than between equal to text that contains so if you were had like birch Street and there was lots of addresses with birch Street that would be a great one date occurring duplicate values let's go to greater than and I'm going to not accept that number but notice this anytime you see that that means you can connect the text box to a cell so I'm simply going to click on that right there and then click OK the beauty of this is now I can change this to 400 and instantly they update still these are built in features let's click on C f5 this one is called data bars and I'm going to highlight this whole column here and what I want is and you see I put rank over here and the biggest one is going to get a data bar which means the cell will fill up with color number one will get the most color meaning the longest bar number two will get the second longest number three the third longest etc so it basically works off of rank now the logical test here is hey is the cell equal to one is the cell equal to two and if so if it's one it's going to be the tallest bar two it's going to be the second tallest bar so we just go up here in data bars in 2007 there was only solid fill you could see the solid fill on the screen and in 2010 they have this gradient fill now let's go ahead and change this here's the biggest I'm going to change this 1000 right and instantly it updates next one this is just so silly why am I even showing this color scale and in essence is going to do not one two three four five but it's going to group them so it's going to say like zero to 2002 2008 one color zero to forty percent will get another zero to 60 so it's like percentiles and it depending on which one you select it could be five categories three categories four categories I'm going to go up here color scales I've never used this ever don't like it but there it is so the biggest ones get in the top group get green and then the next biggest group gets next green still further let's go over to seven here's another total silly one but very popular icons again it's going to do that same grouping either groups of three so the top third will get a particular icon the middle third will get a particularly con and etc let's go up to conditional formatting icons and there it is that's three there's four so two groups of 25% groups of 33% I'm going to select this one there's five down here twenty percent so there it is the top group gets a green up arrow the bottom group gets a red down arrow and the middle group gets a arrow to the side now on to the real heart of conditional formatting we're going to click on the sheet number eight our goal on this sheet is we have a date with criteria here and we want to be able to put whatever date here and highlight the number so for example this cell we need to ask the logical test is this date in this row equal to this and then we go down one this cell needs to hit say hey is the date over here equal to that one this cell needs to say hey is the date over here equal to that one so if we were to go up to conditional formatting highlight cell rules and go to date occurring I don't see a way to tell this cell to look over there so I'm going to switch over to formulas I'm going to click cancel right here formulas just means we can't click one of these built-in ones we're going to have to click on manage rules or I'll teach you the keyboard shortcut and put a true/false formula into a dialog box this dialog box will in memory copy the true/false formula down so that every one of these cells has a logical true/false formula now you can't put the formula in the cell because there's a number there that's why there's a dialog box let's just see if we can think of how to do this over here okay equals well I need to say is this date and this row relative cell reference is that equal to this date right here and I need to lock it I'm going to lock using the f4 key I hit the f4 key twice to lock the row reference that dollar sign there means as I copy down that will be locked I'm going to ctrl enter I can see I get a true if I copy this down I can see I got true true and there's a true true there because sure enough this row right here has the date 525 now the concept here is this cell needs to look over here and so that's why we switch into formulas now let's just check this out is this column of trues and falses exactly the same size as the column over here yes it is now you absolutely do not have to put the formula in the cell the advantages of building it in the cell as you can see visually where you're going to get your truths when we copy and paste this formula into dialogue box you never see the formula because it's behind the scenes stored in memory all right so I'm going to make sure that I copy the very first formula and I'm going to highlight it in edit mode and ctrl C for copy escape and now I'm going to highlight the exact same parallel range now very important that's called the active cell that active cell has to be exactly parallel to the cell we copied the formula from if I went like this now if I open up my dialog box for conditional formatting and pasted the formula from this cell in memory it would go here and it wouldn't work so when you copy your formula you have to highlight make sure the active cell is right there I'm going to go up to conditional formatting and manage rules and then I'm going to click new rule and then I'm going to click on use formula now I'm going to show you the keyboard shortcut which I will use going forward I'm going to highlight the range in alt OD alt Oh D that's from earlier versions there's a longer keyboard shortcut if you memorize it here I'm going to always for formulas go Aalto d new rule and then there's a list of things here I'm going to say use formula to determine which sells a format and there it is there's our beautiful text box it says format values where the formula is true we mentioned that before evaluate to true the format is applied false it is not applied I'm going to control V there it is that allows us to go and get cell references but we don't need to do that because we just copied and pasted it and here it is here's the format button so form true/false formula click here and then you can do whatever type of form formatting you want and the cool thing about 2007 and 10 is now there's a number tab so you can apply number formatting and if you know anything about number for mean especially custom number formatting oh is that powerful now once you figure out what formatting you want click OK click OK and there's a list of conditional formatting you can have more than one in the order here matters we'll see later when we apply multiple levels of formatting we'll talk about that hierarchy there I'm going to click OK and so there we have it on 512 and the beauty of this is I can change this to 6 and I should see none because there aren't any records on the six I should change it to the seventh and there's two all right now what we've done here is a true/false formula for and we've applied a color to the cell for a single cell but that's not usually what you want in this case maybe you're auditing or you have you're looking through records and you just need to type something here and quickly have a visual cue this is called visualizing the data so what we'd really like is to highlight the whole row ok now let's go over to this next sheet number nine here now this is the end result on this sheet we have a date here and notice the whole row in the data set is highlighted now this is going to involve the same formula we did in our just a moment ago except we're going to have to think about cell references now if you don't know about the four different types of cell references relative absolute mixed with a column we're locked and mixed with the row locked I'm going to click on the CF 9.1 there's a video right here it's a cell reference video link this link if you have if you've downloaded the workbook you can click on it or just type this name into Google and you'll find the video you got to watch this video because the remaining 20 tricks in this video are going to involve formulas and you have to know about cell references and really that's why when I teach Excel classes I always remind people you know some 70 to 95 percent of the Excel users in the world have no idea about mix cell references and basically you're just not allowed to do all the cool advanced tricks so boom there it is alright so we want to do this we want to highlight the whole row well get this it's going to be the same formula let's try it over here equals is this date right here equal to this now let's think about this I'm going to copy this formula to the side because what we're going to do and let me suspend this for just a moment is we're going to highlight the whole data set like this this cell this cell this cell this cell this cell this cell and this cell all have to ask the same question is the date in this row equal to this all right now that's why I've pre formatted this because we're going to pretend like this formulas in the dialog box but we're going to copy it over and down and watch how it works now let's think about this right now let's look in there now when I start this formula in the dialog box I'm going to put it into this cell and copy it over so what do I ask every one of these cells when I copy this cell reference here needs to be locked on this one now we're going to be copying across the columns so I hit the f4 key one two three times to put the dollar sign in front of the column reference now when I copy it down I need the blue box to move down and since we're copying across the rows no dollar sign in front of the number reference or eight now this one has to be locked in all directions so I hit the f4 and the column and the Oh reference now control enter and copy it over and down and the beauty of doing it this way when you're learning how is you can clearly see the entire row gets true and it's because conditional formatting requires you have a true/false formula for every cell and so look at that this pattern of throughs is perfect and down here this is perfect when we get down to another date down here true too true there it is it's because that date right there is for this row the 25th and matches there so I'm going to copy remember the top one top left one I'm copying this formula and I'm going to highlight this whole range here now just a moment I'll show you how to do conditional formatting without putting into the cells but when you're learning and and when you're doing really hard one true/false fun it's really helpful to do it in the cells and visually see if the true and false is our match up and then copy it highlight it the active cell is parallel to this one because you have to build the formula in this cell from the point of view of this cell which it is we did over there alt OD I'm going to click on new rule right here and I'm going to paste it right here there it is and I'm going to format it some color whatever color you want or whatever formatting in those four tabs alright and so now the whole row is highlighted if I change this to six it better show me nothing if I should because there is no match so change it to eight boom that is beautiful let's see another example here CF 10 now let's do it the same thing I'm going to highlight this and we're not going to build the formulas for in the cells but again that's a good way to to learn and for the complex one even myself I don't bother building in the dialog box I build it in the cells but this is an easy one that cell right there is the active cell so now with that in mind I'm going to alt OD alt n for that new rule arrow arrow arrow tab and I'm going to build the formula in the dialog box whoops my mouse is not working all right so what am I asking oh well I happen to be in this cell and I need to match for this whole row here is this cell in this row equal to that so I'm going to click there and notice it puts in two dollar signs I'm going to hit the f4 key once and twice lock the column reference but not the row and then I'm going to say is that equal to this locked in all directions notice we're matching for this row we have one criteria format and I'm going to add some formatting you could get as elaborate as you want click OK and there we have it so only invoices five one zero five zero one get this formatting so if I change it to two and the formatting changes let's try another one number 11 number 11 all right so earlier we did above average but now let's do the whole row above average I'm going to highlight this and there's the active cell right let me blow this up there's the active cell all tody alt n arrow arrow arrow tab now I have to build it from the point of view of this cell but the cell with the number we need to compare is way over here no problem we're in essence putting in a formula in this cell that's looking at the sales number in this particular row so I have to hit the f4 Lock the column but not the row and I'm going to have to say remember this is above average that we're doing not equal to I may not have said that earlier correctly but I I need for this whole row to be highlighted when it's above average sales above average and I'm going to say greater than or equal to so that's really not above it's greater than or equal to alright so that formula works we add some formatting I'm just going to be quick here you can get us elaborate as you want and there we have it above average for the whole row all right now if I were to change one of these right and then it would change because the average is jumped up right control Z so that is nicer so there's above average for the whole row now let's go to number 12 let's look at another trick here sometimes people want every other row to have a particular color right and so that can be pretty tedious especially on a huge template no problem we can do this with conditional formatting but it's going to involve that we learn how to use the mod function now what is the mod function mod function gives you the remainder after doing if only I could spell and type so what's 3/2 1 remainder 1 what's 4 divided by 2 2 remainder 0 5/2 2 remainder 1 let's just do this one equals mod what if I took 10 divided by 7 what is the remainder 3 so that's what the mod function does does in that bizarre right you learn how to do remainders back in grammar school and here it is out there in your high paying business job and you have to figure out the remainder again well we're going to get tricky here we're going to not use the numbers like that notice that Excel is set up in rows 1 2 3 4 5 6 7 8 9 10 and we can group all numbers into two categories odd or even alright so we can actually take the row function now what's the definition even you / - and there's remainder 0 so as I copy this down the number in the denominator or the top of the fraction being divided it's going to be 3 4 5 6 7 8 9 10 11 12 well we're just going to divide it by the divisor or the denominator - what is that going to give us well as we see here one zero one zero one zero one zero you got to be kidding me and guess what one is true zero is false the conditional formatting dialog box will interpret one is true and as false I'm going to actually let's yeah yeah we can copy this because no matter where we put this the row function by the way the row if I put it right here it gives me six down here and if I copy this right just it's still in row six so it's always just going to tell you what row you're in alright so now we come over here and highlight however big you want or however big it is and it doesn't matter where the active cell is I don't even prove that tab tab tab tab tab enter enter alright all tody new rule formula right here control V that formula works everywhere it'll always give us a one in an odd row and a zero in an even row if you had to have the well let's just see how this works so I'm going to click format and click some something like a more color whatever again go hog-wild there alright now what if you really wanted to have the even rows you'd have to amend this formula maybe I'll just do it over here a little to set right here all tody new rule formula and you have to say well 0 is false but we can make 0 true by saying hey anytime that's equal to 0 and now the even rows will get formatted some formatting whatever color there's a nice lavender okay so now only the even rows are highlighted that's a great trick there our 13th trick ah this is a very useful trick I've been using this for long as I've been use in Excel and keep in my checkbook which is a long time here's reconcile I want to be able to type a Y here and have it turn yellow alright so I'm going to highlight this whole range right here alt OD new rule formula and I'm going to say what well we want the whole row so again we're thinking mixed cell references the formulas here but it always need to look at boom right there so I'm going to click there notice I'm building the formula from the point of view of this cell so I have to click on the Y in this row and I'm going to lock this with the column but not the row any time that's equal to in quotes Y and then I'm going to format it some color I have a huge checkbook that I've been keeping for years it has thousands and thousands of rows right so as soon as I type in a Y even it even if you type it in down here where there's you're not supposed to it'll turn that color alright let's go to number 14 now in this case so far we've just had a value like it's equal to this invoice or it's equal to the letter Y but here we want to highlight remember we did the top five and highlighted the actual cells I want the whole row to be highlighted so I don't I don't know built-in formula here the first thing is let's say we want the top three I'm going to click right here and we're to use the I want to find the third largest value before I think of a true/false for me so I'm going to use not the max function max will give me the number one biggest I'm going to use the large the great thing about the large function is you can give it a particular K one two three four five so if you said three it will give you a third biggest five it will give you the fifth biggest so now I'm going to highlight this whole column ctrl shift down arrow and then f4 to lock it in all directions comma and I'm going to click on this cell now I'm going to leave this here if you wanted to copy and paste this into the conditional formatting dialog box you would but it would have to run it for each cell I'm just going to keep it here so actually you don't in this cell and then refer to it so actually we don't need any lakh dollar signs there they're kind of not necessary all right so right now I'll give me the third-biggest if I typed in two that's the second biggest five it's the fifth biggest I'm going to type three all right now let's think we need to do the whole row right and we're asking a question about this and we're going to compare it to this so every single time we need to say is this value in this cell greater than or equal to this is this value in this cell greater than or equal to this but remember we need to do it for the whole row so I'm going to highlight this cell right here equals is this now remember I'm simulating the dialog box so this cell is parallel to here and I need to as I copy this way be locked here so I'm gonna hit the f4 key lock the column reference but not the row anytime that's greater than or equal to this and I'm going to lock this in all directions control-enter this whoops control Z this formula will pick up duplicate so if there's a tie for third place which there is here it'll pick up all of the values that are tied for third also I'm going to copy this in edit mode ctrl C escape I'm going to highlight this range I'm going to click there ctrl shift right arrow control shift down oh that's just a quick way to highlight and then alt OD alt n arrow arrow arrow tab control V so that's our little formula and then I'm going to format it say something like whatever click OK click OK and there we have it now watch this this picked up duplicates right which is good because if if there's a tie for third we want to see that record if I change it to 6 look at that there was duplicates there 5 all right so no duplicate there so that's a nice way to visualize your data when you're searching for the top 5 or top 3 we saw earlier how to do the cell here's how to do the row all right conditional formatting number 15 now we switch on to a different topic and that's where you have two criterias so far we've been matching a certain cell with a certain criteria either with the equal sign or comparative operator like greater than or equal to here we have two criteria so for the row to be highlighted this cell has to say mo and this has to be greater than or equal to 500 right false so you got a false and a true here you got a true it's equal to mo but false it's not greater than or equal to 500 here you have a mo which is true and a second logical test it says this greater than equal to 500 true so if you have true to Truths the whole row is highlight so an criteria always means all logical tests must evaluate to true I'm going to click here and we'll just simply use what function the a n function I'm going to say is this cell right here and I'm going to lock the column reference is that equal to this criteria and lock it in all directions that's the first logical test comma and then the second logical test that remember the column reference locked is that greater than or equal to 500 and that is locked in all directions that and will only evaluate to true when both logical tests yield it true or evaluate to true so now we can see sure enough the whole row right there the whole row right here you can see sure enough our cell references work perfect now I'm going to very carefully highlight the active cell just to show you I'm going to highlight this one down here and then I'm going to highlight it like this notice it's the active cell so wherever the active cell is you have to build it from that point of view all tody all 10 arrows arrow arrow tab ctrl v format and add some color click ok click OK click OK alright so now that's mo and I greater than or equal to 500 let's try Jo and sure enough it updates number 16 alright now this is going to be or criteria so for a row to be highlighted it has to be Jo or mo so I'm going to click here and use not the and function but the or I'm going to click on this and lock the column reference equal to this locked in all directions comma same cell again lock the column reference equal to this criteria now what's happening here is the or function you can have one or more but usually you're going to use two ah two or more you could have five of them 10 of them only one of them needs to come out true for the or deliver to deliver a true alright so I'm going to copy this down and over and sure enough it looks like anytime there's a mo or a Jo we're getting throughs all the way across so I'm going to copy this highlight the range ctrl shift right arrow and then down arrow all tody I'll to end down arrow tab ctrl V so that little formula there will work format click OK click OK and change this to soup and sure enough that's or criteria now 15 we did and 16 we did or now we're going to do them both together or now we're going to do between right because sometimes you want to highlight you have a certain time period and you want to highlight all records between now this is just an criteria right so equals and and we're going to say this one lock column reference in time that's greater than or equal to the smaller date and the same date so we're asking two logical tests about the same cell column reference lock less than or equal to this date locked on all directions all right so anytime it's during that period we are getting a truce for the whole row so I'm going to highlight control shift right arrow down our alt OD l to end down arrows tab control V and then highlight whatever colors you want or whatever formatting all right so there are looks like it's working let's change this to 30 there's our 30 27 all right number 18 now we'll see number 18 where we have and and or together so here it is we want to see 'but we have date criteria so between these two dates lower and upper and or one or the other so in this case we have really we have three ands but one of the ands that has to come out to be true is going to be an or so what we're going to do is we're going to build and use the and function and do this test this will be our second and test and the third and test will be an or function so equals and so it has their the given record has to be between these two dates so we'll lock the column let reference and say greater than or equal to the lower date and same date column reference lock less than or equal to the upper date all right so look at the screen tips very helpful comma we don't want to get confused just remember we have to somehow doing or inside this lot third logical but we can put the or function in here no problem and inside the or we're going to have to write because we have two names we're going to ask this column reference locked equal to Joe locked in all directions comma equal to Moloch done all directions now the or second logical test one or the other so we only have to get one true when we get two falses or we'll deliver a false when we get just one it'll deliver a true it's never going to get true because you can't have both in one cell now I'm going to close off the or here and now I can see them back to the and and that or is logical three in the and whew I'm going to copy this and then highlight that range although D all to end down arrow control V in this text box and then format okay so it looks like Wow so it looks like it's working if I change the range here you always want to test it 28 then there should be some there's a 28 form Oh change this to chin and then there's chin or Jo so this is an and and it wore together number 19 Oh 19 to 21 actually I clicked on 1920 well let's click over here and just look at what we're going to do here a picture tells a thousand words if I type 1225 here and our louses are 1 I need to find the tax amount of 15 so what do we need to match or what are we doing with a conditional formatting wow we need to highlight the whole row which we saw how to do and the whole column and the intersecting value so two things or three things we'll see new here we'll see how to do it for a column in the same exact logic will work for a column we're going to match this is a whole column we need to for every one of these cells here when you say is whatever is right here equal to that but look about the row the rows not the same as what we did before because we don't have an exact match earlier when we did dates and invoice numbers we were matching a criteria exactly but here we need to do not an exact match but an approximate match that's where the vlookup function will come in handy I need to match this and for right now it will match this row highlight it we'll see how to do that and then the intersecting value will be easy because we'll take the logical test for the column and a second logical test will be our logical test for the row and put them into an and function and that'll give us this so there's actually one two three conditional formatting is working here now I'm going to come back over here first thing we need to do is think about what the vlookup function does now the vlookup equals vlookup it's a lookup function it actually works just like we look up things by hand if you have an income to look up you say you put this into your brain you'll remember I'm looking up 1250 you come over you through some table and when you bump into the first number bigger than it you jump back one row and you say oh that thousand now normally you usually then go over and get some number like a tax or Commission rate or an employee hire date or something like that but in this example we need to return this thousand because the vlookup is going to deliver a thousand and we're going to build a logical formula out of it which says the delivered value thousand member based on this is it equal to the row header so let's just see if we can get vlookup to work I'm going to click there and lock it in all directions with the f4 key comma our table array is going to be just this first column and f4 table that's the table that contains the values you're looking up comma and now if you know how to use vlookup it's very uncommon that you have one column because usually you look something up and then return something to a subsequent column but here we're looking it up the lookup column and the return column are the same so we have to put column index one that tells the vlookup which column has the value I want to return for us it's one now the last argument comma approximate or exact you can leave it off the default is approximate so I'm not going to even put it on in a backspace close parenthesis now when I hit enter the result is going to be really confusing because in every single cell it's going to return a thousand now if I change this to two thousand five hundred or I'm sorry - love it - two zero zero right so then it's returning this two thousand here now why do we have it like that I'm going to ctrl is easy because now we can ask the question is this remember because we're trying to get the whole road to say true well only this row here has a row header called a thousand so we're going to say equals remember this is the active cell here I'm going to click on that one and I'm going to lock it we have to though every single row has to look at the row header so I'm going to lock the column ref control-enter drag it over drag it down and you can see we get throughs only for the row that has the value so now I'm going to type in 2,750 and I better get Troost for this row right here which is this one so you and if you have if you don't use vlookup it'll work for an approximate or an exact match because remember what is the logic it takes this value the vlookup takes this bumps into the first value bigger 3000 and jumps back one row alright I'm going to copy this and just this is a logical formula now we've used vlookup in a comparative operator equals a cell reference to create a logical formula it delivers true or false copy highlight the range the active cell is right there open up my dialog box all tody new rule ctrl V and I'm going to add yellow click OK click OK now there's one we're going to build three and we'll talk about the hierarchy here but let's just see if this one works that's pretty cool that's pretty cool now the column should be no problem in fact we'll build it in our dialog box all tody ulta n down arrow tab I'm going to say now remember we're looking at column headers now right is this column header and now when I copy this formula down it needs to be locked but when I move to the next column it needs to move so I'm locking with the f4 key only the row reference not the column any time that's equal to this allowance locked in all directions I add a color now this is the second test so it will be applied on top of the yellow and that's what the hierarchy means the one at the top will preside over this one so you can see the hierarchy means the orange is on top now I'm going to highlight that same same range all tody you can come back and edit these all tody now if you made a mistake you can click on this and edit or you can double click and edit it right and then change it or change the formatting editing is very important let's just see what happens if we change the order there it is move up all it's going to do is now the yellow is going to be applied because it's on top I'm going to ctrl Z because I want the orange Oh Maxine whichever way maybe control Y may be that what yellow on top looks better actually it doesn't matter because we're going to have one last test here and it's going to be the red and that will preside so now I'm going to copy this again without the equal sign copy I'm going to highlight this and now actually we could go like this how about this since this is a little bit hard here let's build it in the cell remember all we have to do is take column test row tests combine them together in and and and D logical one comma logical to click on this it needs to be locked going down across the rows but not the column so f4 to lock the row but not the column whenever that's equal to this locked in all directions so I hit f4 there's two logical tests I close off the and control enter double click I mean copy it over and then down and sure enough only the intersecting value let's just change this to two and sure enough look at that all right now I'm going to be very careful and copy the one from the uppermost left corner and when I highlight this range the active cell is the uppermost left corner conditional formatting whoops I did it the long way ctrl V and now I'm going to put a read always it read as a dark value color so I always put some light font color like white so that you can actually read it and there it is that is how to do a intersecting value and a column in a row I just absolutely love it now when I change this to 1500 it jumps down when I change it to 4 it jumps over there alright now so that was three cool things let's look at our next one 22 to 24 now this one has three criteria but notice oh I'm sorry I'll come over here when I change one of these so I'm going to change it to section for now so property 1 and property four have to be matched here for a row to be highlighted well that's no problem we can use in a and because this cell has to equal that and this cell has to equal that and then it's a simple matching of April 2 April here and then the intersecting value will be the two tests together so we come over here I'm going to highlight and I'm going to build this one inside the dialog box just to show you that it is possible active cell right there all tody and I'm going to say equals and open parenthesis well I know I'm testing the row header so I'm going to click in this first cell here and lock the column reference but not the row and say whenever that's equal to this locked in all directions comma and because we need two of them not this one but this one right here f4 lock the column reference anytime that's equal to this close parentheses so we have to now I'm going to copy this because I know I'm going to use it again later format let's just see if that works sure enough that works now I'm going to highlight that whole thing now have that copied a little bit in memory but I'm going to do the column now Alto D alt in let's do the column so I'm matching this so these all have two even though these will never be colored I still want to start I want to start there so that the the conditional formatting range is the same for all of the formulas I'm going to say anytime that and this is a column so I need a lock going down but not to the side so I'm going to just lock the row reference anytime that's equal to this locked in all directions and to that same boring combination orange right and then I'm going to add a third one new rule and this is going to be formula now I need to combine both the row and the column so I'm going to say and and actually no I'm not I'm going to go equal sign and control V now that is just the row but all we need to add to it is one other bit so I'm going to come to the end comma and build that same little piece again that I'm going to lock the row reference not the column it's equal to this locked in all direction so all we do is add a third one and I'm going to format it red and why because yellow and orange mixed together is red click OK click OK and now we have our three values 1 2 3 and the hierarchy I'm going to click OK and let's test it I'm going to change it to February absolutely amazing now in this particular situation if you select property 1 and section L there is no property one section L so it won't add any formatting but as soon as you change it to section 2 then it works all right now our next example 25 to 27 now what we're going to do here is called a Gantt chart it's actually a cell chart if I come over and click on the answer we can see what our goal is here we want to have a start date a number of days and an end date and have it highlight now all the trues and falses are here just to illustrate but this is really what what it looks like so if I change this to six it has to highlight six days it also has to know in this particular chart that our weekend is Sunday and that this is defined as a Monday so we were going to have two sets of conditional formatting let's go back over to the sheet right here first trick is we want to find the end date now I'm going to use Excel 2010 functions because the functions date functions for workday and networking days are absolutely awesome what you had to do before 2010 was quite complicated alright let's figure out the end date I have a start in a number of days we don't want to just go like this that's a serial number and add because that will do just fine but it's not skipping over Sundays because week or weekend is Sunday and our holiday so we're going to use equal work day now there's two work dates this one existed 2007 2003 and earlier this is a new one workday international 2010 only now workday just considers the weekend Saturday and Sunday but workday International will give us lots of options now what does work they do it just we give it a start date a number of days and it tells us what the actual date is so start date right here comma number of days six now here's the problem we're going to have to subtract one because we don't want it right now it's going to add six other days and we want to include this day this is one of the workday so I'm going to subtract one comma and here's the amazing thing weekend it just gives us the option before this function here you had to do some pretty fancy things to figure out the end serial number date so I'm going to click 11 comma and holidays check this out and you can highlight a single cell or a bunch of cells I'm going to highlight a bunch and then if I come in later and add some here it will consider them now I'm going to f4 to lock that in all direction and close parentheses all right so that's the end date why do we do and and begin because we need to the now do conditional formatting and ask the question for this cell right here given a start and an end is this date greater than or equal to this and is this date less than or equal to this so we can do equals and we're always comparing this date and now this is a column header when I move this way it needs to move but when I copy it down it needs to be lock so I'm going to lock the row reference I hit f4 twice greater than or equal to this now this one when we copy this direction has to be locked on the row header but when I copy down it needs to move so I'm going to lock the column reference but not the row comma and then the the next test is the same date I'm going to lock the row reference less than or equal to this lock the column reference close parentheses copy it all the way over and all the way down all right so we should see here just two truths for for this one right here we should see true true all the way to here but now notice that will be the first conditional formatting we actually need to eliminate this Sunday and this Monday in essence we need to somehow come up with the true/false that will say and I want a different color there that's going to say this is one of the weekend days a or it's one of the holidays so we're going to come down here and we're going to actually look at the actually maybe I'll do this maybe I should move this over here I was going to build it off to the side but I built it right there that's just in fact let's do this one first and then we'll come down and talk about the next one so I'm going to copy this I put it right in the actual cell later will delete the formulas I'm going to highlight this whole range here all tody new rule formula control V and then say green so green is for go that's when the project is on all right so now we have one date that's exactly right our bunch of dates there we just need to fix it it should be green they're green there and now this next we're gonna have to figure out another true-false tests just to have true for a different color there actually might as well build this right in the same cell let's say no I'm not going to do that and then we'll be able to see the truths come up only in the green range only for a weekend or holiday well we're going to use the net working days now the same two things existed this one is an earlier version Saturday and Sunday for weekends only this one gives us the options now what is networking days it's going to tell us the number of working days between two dates and here's the trick we're actually going to four start and end it's going to be just this column header and what it will do is since network and ace counts actual number of working days it'll tell us one when it is a working day and zero one it is not because we're only giving it the possibility if the start and the end are the same so I'm going to click here and lock the row reference that's the start comma the end row reference it can only give us a zero or one now that's not going to quite work but let's just see if we can get that to work and see a string of ones and zeroes comma now what's the weekend office is so easy comma and then the holidays are down here locked in all directions now let's copy this over copy this over and now it should show zeroes because remember what is the networking day do it tells us number of working days so it's we instructed it with the weekend argument right here to skip over Sundays and we instructed it with the holidays to skip over holidays so of course because our start and our end date are the same day it's going to give us a zero luckily there's something called the not n OT function and not turns true to false and false to true and since one is true and zero is false we can simply wrap the not function around this and it will give us our truths exactly where we want remember I did a video with a really complicated way and then one of the comments at YouTube was what why don't you try this cool not networking day is totally awesome and there it is look at that within our green we see our truce now we're going to slap this formula up there and say please give me red and that will highlight for our Gant cell chart the weekends and holidays again it's the same idea you copy from the uppermost left cell and when you highlight the active cell has to be the upper leftmost Aalto D I'm going to add a new rule formula ctrl V and I'm adding a color okay okay okay now let's check this out ooh that is not going to work that's not what we want we have in essence for the whole chart shown our Sunday and Monday which could be fine but I don't for right here I don't want to see those Reds there so guess what this is similar to that intersection we did a while ago so we're going to have to criteria it's the one that got us the green and the one that got us the red now I'm going to remove all of these formulas here on the answer sheet over here I did them down here so you can look at them if you want but now I'm going to go back up here I've highlighted the the correct range all tody and I'm going to cheat here I'm going to double click this to edit it and I'm going to copy it not the equal sign ctrl C escape and now I'm going to add ooh I have two right there okay so I'm going to put this down in the cell just paste it right there and then I'm going to cheat all tody I should have kept these somewhere else double click and I'm going to copy this one I'm actually going to put these together in a Cell down here it's easier than doing it up there so now control V so I got an and those two things have to be true and remember it's it's an intersection so I have one two for that first one and this one so I'm going to get rid of the comma the closed parenthesis for the and I can see logical two so I'm going to type a comma there's the logical three is that big not and then close parenthesis here and I can see up here that it's pointing up all to the right cell so I'm building this formula down here but it's as if it's right in the top left most corner ctrl C enter I'll leave that there now when a highlight active cell has to be in that upper wherever the formula was built has to be paralleled all tody new rule or paste to here control V format red like okay and now I'm going to delete this one delete and I want this one on top so it is on top click okay and then we have our cell chart is that totally cool now let's do a little test turn I'm going to delete this right here oh no error so that means we're going to have to fix this and this conditional formatting I'm going to double click here I'm going to use the if error function if error is great you can just slap your function in there come to the end comma and the value if error is going to be double quote now this will mess up our conditional formatting but now I'm going to come over here and highlight this range here and actually going to have to add another condition all tody to each one of these these are Ann's I'm going to have to say this is not blank oops escape new rule I'm sorry double click that was right and I'm going to add a new condition now it's probably safer to do it in the cells but there's the active cell I'm going to click on that and I'm going to lock the column reference not less than greater than double quotes double quotes or blank and then comma I'm actually going to copy that a little bit I'm going to click OK now I'm going to double click this click right there and click on that cell reference right there oh I could have copied the whole thing and control-v so in essence I did the replicated that little bit right there click OK click ok and so now we have only those so now we can fill this out so I'm going to do one other test because we're going to have another problem here 5/20 9 that is Sunday and I'm going to type 2 here now what's happened is this is two days and we need two greens but we're not getting it what I want to do is I want to disallow anyone putting a holiday or a Sunday there so now I'm going to come down here we're going to use the and we're going to have to build a true/false phone up but we're not going to use conditional formatting we're going to use data validation now a lot of times you know people use data validation list but you can use data validation for all sorts of things including a logical formula so let's think of a logical formula it's simply going to be that networking days international we're going to do that same trick this cell is the start comma this cell is the end notice we're leaving them relative because it's going to be in a dialogue box just like conditional formatting but it's going to be as if if it were copied down the column comma the weekend is going to be sunday comma and the holidays are going to be here F for closed parenthesis now what is this going to do before we use not right now it's going to say this day is allowed right it's one that one is a lot but watch what happens when we come down and we hit a Sunday or a holiday which would be 5/30 zeros and as we know one is true zero is false so I can simply use this formula not for conditional formatting but for data validation custom so I'm going to highlight this range data validation is data validation validation Alt alt DL have and in this list right here you want custom there's the formula it's the same idea as conditional formatting except for its if it's true you're allowed to put it in the cell if it's not you're not allowed I'm going to control V and then I'm going to come over here to error alert and say something like enter validate or enter you can put whatever you want holidays and Sunday is not allowed something like that hopefully I spelled things right it click OK and so now what happened it let me have those in there well you gotta do the download $8 validation first I'm going to type five slash twenty nine and there it is holidays and Sundays not allowed pal retry so now I can do 5/30 one and two and there we have our days alright so that was a wild one there two last ones real quick this is a nice easy one 28 and 29 we want to highlight dupes I just got to show you the built-in feature so I'm going to highlight this and down here okay I'm going to accept the default notice that both 12s get highlighted and sometimes that's not what you want you really want the extra one right I want this one but I want to color this one because I don't want it so let's look at a formula true/false from their winnings count if and I'm going to use the name error there's no such thing what does the name error mean it means there's no such thing as count motive count if and now right now if I had was counting 12 I would get one here but down here if I was going down and adding them up as I went along I get it two here so let's see how to do it let's do an expandable range this range is the values that you want to evaluate I'm going to shift : and then I'm going to comma the criteria is going to be relative cell reference right there here's the trick we need an expandable range because right now it will give us one there's 112 if I were to copy this formula down it would be ridiculous because here it would still be there but we're going to do see how c4 c2 we want it to be locked on c2 so I'm going to lock the row reference this is an expandable range now so I copy it down I can see that I get the number two I can see the blue range is expanded expandable range that will work what do we do we say greater than one so that way anytime it gets one two three four however many it'll get a true that's greater than one if I type another twelve here right so just so they're parallel this countif delivers a three one two three but it's greater than one all right so this is the formula to use copy if you want highlight duplicates but not the first one all tody new rule control V format some color click ok click OK click OK and there you have it the first one is not highlighted nor is the first 76 alright that's pretty while 29 actually we better make it 30 so on cf-30 the sheet here we have we want to compare actual dollar spent to budget it so here's our expenses is the actual amount that we spent here's our budgeted and here's the difference now I have a randomizing formula here so if I hit the f9 key it's changing these numbers but the difference is well we actually spent 536 and we only budgeted 500 so we have a minus here and what we're going to do is highlight the whole row alright so no problem we've done this a lot of times in our last video this should be second nature by now highlight the range the active cell up in the corner I'm going to alt OD open up conditional formatting new rule formula click right here and I'm gonna say Boop that one right there with the column reference locked is less than 0 so that's our true/false logical formula anytime it comes out to be true less than 0 I'm going to fill some amount click OK click OK click OK and boom now if I hit f9 you can see any time we are running spending more than we've budgeted it will turn red actually let's do one other things since I never showed you that all toady let's amend this now this is important to know here if you want to edit this you can either say edit or double click that way you can if your logic isn't working and change that but let's come over here number format let's do number we did I none of the 30 examples they did number I'm going to put currency so a currency number format will happen only when there's a negative number which is kind of silly here but let's see how this work click OK click OK so there we have it when I hit the f9 the rest are regular general these ones have red and currency alright that was 30 examples of conditional formatting epic time here we'll see you next video you

Channel: ExcelIsFun

Views: 298,529

Rating: 4.8830218 out of 5

Keywords: Excel, 2007, 2010, excelisfun, Mike, Gel, Girvin, Highline, Community, College, Slaying, Dragons, Conditional, Formatting, Basic, To, Advanced, Highlight, Entire, Row, for, invoices, sold, Biggest, Values, OR, AND, Criteria, between, Approximate, Value, Exact, Match, Intersecting, entire, column, Weekends, and, Holidays, Data, Validation, Custom, Formula, preventing, Sunday, Holiday, Dates, to, be, entered, in, cell, VLOOKUP, MOD, NOT, WORKDAY.INTL, NETWORKDAYS.INTL, function, Actual, Exceeds, Budgeted

Id: LOaezdoyJDE

Length: 69min 30sec (4170 seconds)

Published: Fri May 27 2011