Highline Excel 2013 Class Video 40: Conditional Formatting Basic To Advanced 50 Examples

welcome to Highline Excel 2013 class video number 40 if you want to download this workbook for week 8 click on the link below the video or go to our class website wow we got to talk about the amazing conditional formatting we are going to do basics all the way to advance and there's 50 examples now here's our topic list and this is huge if you click on the show more button below the video this table of contents is there with time hyperlinks and you can click on any topic the time hyperlink it will take you to that part of the video hey if you want to learn basic to advanced watch this from beginning to end now there's two basic types of conditional formatting built in conditional formatting which was amazingly enhanced since Excel 2007 and then there's logical formulas that's the real power of conditional formatting so we have lots of examples of both now let's go over to the sheet see f15 and talk about what they both have in common conditional formatting comes down to this some logical test that comes out true or false true means the cell gets the formatting false the cell does not get the formatting so if we're testing whether the cells contain a number greater than 1000 we actually have to look at every single cell and ask a question are you greater than a thousand true so we add the formatting are you greater than a thousand false so we don't add the formatting are you greater than a thousand false are you greater than a thousand true so we add the formatting every single cell that you want to add conditional formatting to has to get that same true/false logical question true we get the formatting false we don't get the formatting controls easy all right so let's try this I'm going to highlight this whole column net sales and then home styles conditional formatting now there's a bunch of built-in options highlight cell rules different comparative operators greater than less than between we can do text we can do dates we can do duplicates top and bottom rules data bars color scales icons we'll look at all of these all right let's do greater than and for this one I'm simply going to type in a thousand and accept the default click OK and so now if I change this to a thousand point zero one it becomes greater than a thousand so the conditional formatting shows up control Z if I change this to one instantly the conditional formatting is not apply because that comes out false control Z how about contains I want to highlight all of the text items that contain fun home styles conditional formatting highlight cell rules each one of the cells we're going to ask a question text that contains contains what fun now notice I'm typing this in for both of these examples here that collapse button for our text box means you can link it to the cells and usually you want to link it to the cells because then the criteria is not hidden in this dialog box alright I'm not going to accept the default I'm going to go to custom format fill border font number you can go wild pretty much every time I'm just going to click yellow but any formatting you select you could pile it up and it will be applied click OK so now we have text that contains fun if I change this to fun instantly it updates control Z now most of the time we want to link it to the cells highlight cell rules greater than and that collapse button is great because now I can link it to the cell I'm not going to accept the default if we want to do the contains conditional formatting contains and I'm going to link it to that cell all right so now I can change it now I can change it to quod notice contains means either though whatever you're putting something here and this cell can contain it either partial or in full if I change this to two thousand instantly the conditional formatting updates so that's beautiful that we can link it to the cell controls easy alright let's go over to our next sheet CF six and seven how about top three so I want to see the top three with conditional formatting we use our top/bottom rules top ten items and I'm going to just Boop boo Boop notice this one is not linked I don't see that collapse button alright and then we select whatever formatting we want instantly the top three and that's dynamic of course above average this is a great one you got to be kidding it will calculate the average so above average I'll just accept the default there and instantly only the numbers that are above average get that conditional formatting let's go over to CF eight to 11 we're still talking about built-in features data bars will rank the number with length of the bar so I have these grades highlighted conditional formatting data bars there's some sort of translucent ones at the top and then solid colors I'm going to select this one that is beautiful now sometimes you don't want to see the numbers and bars together no problem we'll do what's called a cell chart I'm going to highlight the range equals one cell to my left control enter to populate those then I'll go up and apply my data bars and now I need to edit this and so we need to learn about conditional formatting manage rules not only can you edit you can add you can move them up and down if you have mold rulz all right so I'm going to say manage here's our manage window new rule edit rule delete and you can have multiple rules and apply conditional formatting on top of each other we want to edit this and so right here show bar only we do not want to see the numbers we just want the bar check click OK click OK that is so cool here's another cool trick right control-c control-v just to have a little fun like christmas lights flashing on and off i'm going to highlight those numbers equals the randbetween function and do something like 0 up to 100 so I'm going to randomly select numbers from 0 to 100 and the ran between is volatile control-enter that means when you f9 it recalculates and look at that we get a bunch of singing bars up and down up and down that's pretty cool hey let's go over to the sheet CF 12 to 13 color scales it actually ranks the color by number and sort of divides it 2 into either 3 or 4 parts let's go up to conditional formatting color scales and I'm going to select this first one now there's sort of four sections red is the bottom then orange then yellow then green so the darkest green is the biggest number and then the next darkest green and then it's sort of this yellow green which transmutes into yellow lighter yellow so color scales the only good thing I've never used color scales very much the only good thing I can think of color scales for is the old ran between trick Oh actually I'm going to do equals brand clothes not even a close parenthesis this is an argument less function control enter f9 it creates a number between 1 and 0 with 15 digits and I'll go up in data bar no color scales soup and so now if I hit the f9 key it's kind of like Christmas not only that you can pile up whatever you want icons is coming next you can just pile some icons going to pick that one right there and so now if you hit f9 oh that is wild will pile it up let's do some data bars - all on top this is just silly right here look at that f9 and it's kind of like your Christmas tree you can actually go one stepping further my 7 year old loves to do this after doing R and a conditional formatting he goes up to charts more charts and then actually all charts and the bubble chart is under XY scatter it used to be its own category here and I'm going to select these 3d bubbles here that one will be fine click OK and so now not only do you get I'm going to control and roll this is totally silly I'm going to delete all the elements here and then f9 look at that is that a wild little excel animation trick alright enough of the silliness let's go over to CF 14 to 15 we want to look at icons sometimes you have grades or stock values or stock changes up and down and we want to add an icon like an arrow there's a bunch of silly icons like these cell phone strengths and quarter moons but I'm going to try the arrows just flat out three categories green is the highest then yellow then red and that's pretty cool not only that we can put it next to the cells here are some grades it could be grades or stalked or daily sales or whatever and you want to rank them Green would be the best yellows in the middle the red ones are probably not so good so I'm going to do that same trick icons and then we want to learn the keyboard we learned that we can go to manage rules but once you start doing this a lot we want to learn that the keyboard is alt OD now we want to edit and we want to say show icon only and notice here's where the percentages are listed and check this out you can do number percent even a formula or a percentile I'm going to keep it as a percent and click OK so you have some power with these arrows here click OK oh that is totally cool all right built in conditional formatting a bunch of cool things but the real power is going to come with formulas I'm going to go over to CF 16 now logical formulas we've done a bunch in this class any formula that comes out to true or false you can use in the conditional formatting dialog box true gets the formatting false and doesn't get the formatting not only that but array formulas working conditional formatting dialog box our last example will show an example of that errors are ignored so later when we use the match function for either-or criteria or to check whether two lists are the same or different we'll get na errors and they are ignore they're treated as false any nonzero number later we'll use the match and we'll get int lots of different numbers only zero is treated as false any nonzero number positive or negative is true by the way that's exactly the same for the if function any nonzero number is true zero is false that means get formatting is either true or any nonzero number not get formatting is either false or zero or an error now let's look at a bunch of examples we're going to start simple and get more complex go over to CF 17 let's start off by trying to format numbers less than 0 it will start off with built-in features and then we'll go to fullness I'm simply going to highlight these these are our stocks here's a profit and we want to highlight red for the numbers less than 0 conditional formatting highlight cell rules less than we could just type in zero and usually this is perfectly okay because losses are less than zero by definition so that's probably not going to change and then you could format it however you want we could also highlight go up to conditional formatting highlight cell rules less than and link it and that might be nice because maybe your hurdle is five hundred and so now we have highlighted and read all the ones we're not interested in control Z ah but we can also just build a formula but here's the deal each one of these cells still needs a true/false formula now we're going to build our formula off to the side copy and paste it and then highlight open up the dialog box and there's a place in the dialogue box to paste your formula the key is make sure when you build it off to the side that the range you're building in in is the same size over here then we'll build our formula see if the patterns of trues and falses are working and then we'll copy and paste it there's actually a couple advantages to building your formula off to the side before putting it in the dialog box the first is it's easier to build formulas in cells than in dialog boxes the second thing is the dialog box as you will see is very small all right here we go in the top cell here I'm going to say equal and I'm trying to build a logical formula it tells me true or false when the number in the cell is less than zero so I click relative cell reference are you less than I'm going to click on this zero and F for that is a comparative operator anytime you have a comparative operator you get a logical formula that spits out true or false control-enter and copy it down so we could see our patterns of trues and falses if I change this to 500 working good control-z now know something we copy this formula down this column here now we want to copy the very top formula in edit mode control C and then we want to highlight this range now this range is going to need each one of these formulas now these are going to have nothing to do with it but we're going to paste that formula into the dialog box and in memory it will copy it down as if it was in the cell and tell each cell either true or false now with a range highlight and end it's very important notice the active cell if I highlighted it in Reverse this would not work why because that active cell has to be parallel to the cell we copied the formula from all right so I've highlighted it I go up to conditional formatting new rule and down at the bottom it says use formula to determine which cells to format and then in this text box format values where this formula is true I click and ctrl V remember I mentioned it small it's pretty small in this dialog box now let's add some formatting whatever the formatting is now this formula will be copied down in memory into each one of the cells now it's not going to actually be in the cells because those values are in the cells but the dialog box will do its deed and deliver a true or false to each cell when I click OK instantly the conditional formatting based on our formula is working if I change this to 16,000 instantly our conditional formatting is working ctrl Z now these formulas over here are not connected in any way we can highlight them and clear them alt eaa the formula we copied from here and pasted in the dialog boxes in the dialog box now I'm going to ctrl Z and leave that there these are not determining the conditional formatting they're just for us as we're learning or as we're building our formulas alright let's go look at some great examples on 18 to 21 here we have budgeted and actual and we just like to format the actuals that are bigger than the budgeted amounts all right so we highlight and let's do built in first conditional formatting highlight rules and we can do greater than now notice in this situation I highlighted and the active cell is right there well I'm going to tell this text box to look one cell to my left now notice something the default for cell references when you start clicking is absolute so if I want it relative I have to hit the f4 key one two three times right now format cells that are greater than and that's a relative cell reference in memory it will be copied down and a true or false will be delivered to each cell click OK that is Beatle so instantly we can see the actuals that are bigger than the budgeted amounts now let's come down here I wasn't supposed to have this formula right here let's do it with formulas now when you can do it with the built-in ones that's fine oftentimes though you have complex criteria or later we'll see that we want the whole row like you we're going to do this example later where you want to highlight the whole row when actual is bigger than budget well there's no built-in feature that for that alright so let's see if we can do this one with a formula hey this one's easy equals relative cell reference are you greater than another relative cell reference control-enter copy it down notice our patterns of trues and falses it looks like the formula is working we've highlighted off to the side a range that's exactly the same size of as where we want our conditional formatting I'm going to go to the active cell at the top copy it in edit mode ctrl C escape highlight and now we want to learn the keyboard to open up conditional formatting new rule alt HLN arrow all the way down to use formula and tab control V and now we can format with whatever formatting you want click OK click OK that is beautiful in memory that formula got copied down each cell got a true or false throughs get the formatting false do not now here's a beautiful thing about a budget right here's our template we've built next month I simply highlight delete and then come and type my new values in that conditional formatting will totally remain so so I go down the column oh we're all over budget this is terrible nope that one's under budget and then the point is we can clearly see that conditional formatting even though we clear the content the conditional formatting is still there now I'm going to ctrl Z Z Z a bunch alright now let's go look at another example of formulas on 2223 now here we want to highlight net sales but only net sales that occurred on 1/5 now here's the deal each one of these cells needs a true/false formula but the true/false formula will just be looking at a relative cell reference the date for this transaction so I'm not even going to build this off to the side I'm going to go straight up to the keyboard alt HLN arrow arrow arrow tab now let's see if we can build this formula it's got to be built from the point of view of that cell so I'm going to click on the date for this record f4 three times to make it relative ru equal to and then I click on a four so that one comes absolute which is what we want and then format and so instantly we have format and only the sales numbers where the date is 1:5 now the cool thing about conditional formatting we saw just a second ago we can delete everything we can also sort the column so right click sort biggest ones on top and instantly all the numbers every single cells still looking at a relative cell reference the date in this transaction all right let's go look at another example on CF 24 same thing but we want to do invoice all to HLN arrow arrow arrow tab invoice and I got to make it relative with f4 key three times are you equal to the invoice absolute add some formatting totally beautiful let's go over and we want to see this invoice in the date example we just did but now we want to highlight the entire row so I'm going to go over to CF 25 now here's the deal if this entire row is going to be asked the question is the date in this row equal to 1/5 our criteria date then every single cell in this row has to look at the date but remember this is a formula we're going to build it off to the side it's got a copy over and down well if it's looking if this whole row is looking at this date well this row needs to look at the date in this row or this record that means we need a mixed cell reference so you ready we'll build the formula in the cells once we see our patterns of trues and falses are working we'll copy and paste it into the dialog box alright so ready in h6 I'm going to say equals the date f4 one two three times lock the be the column but not the row remember as we copy it this way every single cell on this roof for this record needs to look at that date but the 7 is not lock so I'm going to move it down boom it will move to the next date in the next record are you equal to a four and now any teams the f4 key because I'm in the cells that formula will work all the way across and down now the trick to highlighting rows with criteria with conditional formatting is always going to be mixed cell references control-enter copy it over and copy it down now we're going to see if our patterns of trues and falses are working and sure enough they are look at that every single cell in this row or for this record is locked on that date locked on that date locked on that date but watch this formula when I go down boom it moves to the next date mixed cell references to conditionally format a row all right upper left cell we put it into edit mode that's our true/false formula copy in edit mode highlight control shift right arrow down arrow now this active cell is totally important if it happened to be over here and you pasted the formula from this cell in a dialogue box it wouldn't work so you have to make sure that the upper left if you copy it from here it's got to be there if you copied it from here then no problem then the active cell would have to be right there I copied it from h7 so this a 7 is the active cell alt h ln arrow arrow arrow tab control V there's our formula here's our formatting click OK that is totally beautiful and again if we sort this on whichever column right-click smallest on the top instantly the whole row for all the transactions on one 5 are highlighted and what's so nice about conditional formatting is that we can do stuff to this data set it certainly is nice to be able to sort and be able to visualize our transactions on one-five that's what conditional 4 formatting can do so well help visualize the data now let's go to our next sheet CF 26 let's do the same thing notice I've already highlighted the active cell is right there and I want to highlight each row or record based on this invoice number all to HLN arrow arrow arrow tab let's drag this down here and I have to build the formula from the point of view of that invoice number in that cell so I'm going to click on that a 7 hit the f4 key 1/2 times the column a is locked but not the row D that means is it copies over in memory everything in this row gets a 7 but when it copies down it will look at invoice number and a 8ru equal to a 4 and that's locked absolutely format whatever formatting you want click OK click OK that is beautiful and if I sort it right click sort totally perfect I come over and change the invoice instantly when I hit enter the conditional formatting is applied to the row for that particular invoice that is amazing a great way to visualize now let's go over to CF 27 we want to see our next example hey this is our budget example but we want the whole row including the expense name so over here I'm going to build my formula in the cells remember we're comparing is actual bigger than budgeted ah but each cell in order to get a yellow formatting for the row has to have a true so each one of these cells will have to compare actual to budget it so in the active cell equals actual F for three times lock the column but not the row are you greater than budget it F for 3 times to lock the column but not the row now remember that formula in the active cell will be in the conditional formatting dialog box related to that cell when we copy it over to it will end up in the budgeted amount in actual amount I'm going to control enter to populate the formula into the highlighted cells control enter it looks like our patterns of trues and falses are working if I go to the last cell and check it that is beautiful each cell in the row are still looking at actual and budget now I'm going to copy the upper-left formula in edit mode ctrl C highlight alt HL n arrow arrow arrow tab control V format it however you want click OK that is absolutely beautiful and this is dynamic if we change it to some number bigger instantly we have tagged that line with formatting cost of goods sold at an actual number greater than budget control Z now let's go to our next sheet C f28 and here we want to not highlight the whole row but the whole column hey row or column conditional formatting you're going to use mixed cell references alright I'm going to highlight the whole range here there's the active cell all to HLN arrow arrow arrow tab and now let's build the formula in the dialog box remember the formula here has to relate to that cell the active cell so I'm going to ask is the column header and each cell in this column has to look at that column header so that means the v has to be locked up and hit f4 one ton but the a is not locked because in memory this dialog box will copy the formula over and then January needs to move to February ru equal to whatever's in b3 and I'm going to add some formatting click okay that is beautiful if I select the drop-down and select January boom January is yellow mei-mei is yellow that's highlighting or adding formatting conditionally to the whole column now later in this video we'll see how to do the row and the column to get an intersecting value alright let's go over to cf-29 here we want to talk about or we have talked about single criteria so back here single invoice number on 26 but now sometimes you want to say one or the other now there's two ways to do or criteria we can either use the or function or probably more efficiently it's best to use the match function especially when you have lots of or criteria we're going to start by looking at or and I want to do it just to the sales here equals or the invoice number and this is just we're just going to highlight just the net sale so it's a relative cell reference ru equal to the first one f4 that's the first logical test comes out true or false comma the second logical test got to look at the same exact invoice number again but this time ask the question are you equal to the second invoice number in our criteria area f4 to lock it true in the logical to or true in the logical one either one will trigger the or to dump and/or into the cell control-enter copy it down and so now we have our pattern of trues and falses it looks like it's working if I change this so it looks like we have true for the 42 and true for the 40 if I were to change this to 45 instantly we can see our patterns and trues and falses are working just fine control-z now i'm going to copy the cell in edit mode the topmost cell ctrl c escape control shift down arrow alt h ln arrow arrow arrow tab control V and then format it however you want and just like that we have applied or criteria to deliver a conditional formatting if I change this to 5 instantly the new invoice row sales number is conditionally formatted yellow ctrl Z now we use the or here we want to look at the match that will go up to cf-30 we've talked about the match function oftentimes in this class it's used for looking to see whether something is in a list or not now what I really want to do is ask the question is this invoice in this list now this kind of seems silly it's just to write but match will look this up within this list if it finds a match meaning it finds the actual invoice number it will deliver the position in this case it'll be 1 or 2 if it doesn't it will show an n/a so for to or criteria you know the or is quick and easy but if you have 5 6 7 8 9 10 which we will later in an example then you definitely want to use the match alright so the match needs a lookup it's looking up that invoice number within this list and I need to lock this with the f4 comma zero because it may not be sorted and that's it control enter copy it down now we mentioned at the beginning of the section about formulas and conditional formatting what triggers a true to apply the formatting either true or any nonzero number the n/a which is an error will be completely ignored so what triggers the formatting to not be applied false zero or an error so this formula will work just fine when it sees one or two boom the formatting will be applied I'm going to copy this from the top cell in edit mode ctrl C escape highlight the whole net sale control shift down arrow alt HLM arrow arrow arrow tab control V format I keep doing yellow I know that's boring but you can do whatever formatting you'd like click OK and just like that if I change this to 5 boom the conditional formatting changes control Z now let's see how to do this with lots of invoice numbers and we want to do it for the whole row alright so in the active cell I'm going to build my formula equals match and this time we need to look up this invoice but it's not a relative cell reference every single cell for this record in this row needs to look at the invoice number but when we copy the formula down it needs to move to the next and then the next and then the next invoice number so what do I do f4 1 2 3 times column reference locked row reference 9 comma looking it up where oh I like this much better because think about how we would have done this with the or we would have had to compare this to this comma this to this we would have had to build five individual logical tests here I just highlight the whole range as the lookup array argument F 4 comma 0 because they're not they may not be sorted control-enter copy it across and then down and so now we should get the relative position of the item in each row so nothing but once member any nonzero number is a true look at that even these ones down here wow that is beautiful we do not need to use is number here because conditional formatting interprets that arrow as false and any nonzero number as true control see the upper left formula highlight just the data control shift right arrow down all to HLN arrow arrow arrow tab control V and then format it however you'd like that is amazing if I change this one to 41 instantly 41 if I control Z conditional formatting this apply not only that but what's great about this method is we have highlighted this whole range I can delete any one of these and it will work perfectly so control Z one thing you might want to consider is designating your criteria area with a few rows below so you can just come here and type whichever ones you want and boom the match function will conditionally format based on or criteria now that's a few examples of or criteria let's go to see F 32 and look at and criteria so net sales column we just want to conditionally format the numbers that represent a sale that was made between the lower and the upper gate now we're going to do this in the dialog box control shift down arrow alt H Ln arrow arrow arrow tab now let's build our formula right in the dialog box each one of these cells has to contain a formula with two logical tests and they both have to come out true well we're going to use the and function here each one of the logical tests will have to look at the date and see if it's between these two all right you ready equals and open parenthesis I'm going to ask the question is the date for this record oh it came out absolute and because we're just doing the column we want out relative so I'll hit f4 a bunch of times are you greater than or equal to the lower day f4 is fine comma now notice the screen tip doesn't come up when you build the formula in the dialogue box so you have to be fluent with your function if you're building it in this dialog box the logical two argument comes next I say hey is the date less than or equal to the upper date and that will work close parentheses and we'll do some formatting click OK it looks like they were all sold for this period I have no idea what's going on I'm going to alt OD to get back to rules manager double click or alt e so it looks like I forgot to make relative so I'm going to click here and hit f4 f4 f4 now both are relative when I click OK click ok boom just the sales that were between the upper and lower date are conditionally formatted if I instead of 5 put 3 instantly the conditional formatting is applied ctrl Z now let's do that same test for between two dates but for the whole row click in the upper left corner ctrl shift right arrow ctrl shift down arrow alt HLM arrow arrow arrow tab oh now we're going to have to build the formula with mixed cell references equals and open parenthesis hey I'm looking at this date and I want only the column lock so f4 twice the column B is locked but not the 7ru greater than or equal to the lower date locked in all directions comma to get to logical test to date from this record locked on the B only but not the 7 are you less than or equal to the upper date locked in all directions close parenthesis let's see if I make sure this time looking good click format add some formatting click OK click OK that is amazing so now I can simply come up here and define an upper and lower and instantly the transactions for that period or conditionally format it control-z now let's go over and look at more and criteria on cf-30 for here the end criteria are going to come from different columns now we've done this many times before we've done this with pivot tables in formulas some ifs countifs d functions but here we're going to use the and function and ask is the product quad and our net sales greater than 3000 all right I'm going to click in the top left cell ctrl shift right arrow down arrow alt HLN arrow arrow arrow tab equals and the quad the product and that has to have the D lock but not the 7ru equal to quad locked in all directions comma are the sales f4 f4 to lock the e but not the 7 are you greater than 3000 now right off the bat I can already see a problem that's not going to work because I have the greater than symbol there and I don't I do not want that so no problem excels beautiful that way you build your formulas I can change the inputs later close parentheses and then format it with whatever formatting you'd like click OK click OK nothing but now in I backspace and get rid of that comparative operator instantly quad where the sales were greater than 3000 or conditionally formatted now let's change the criteria fun fly so fun fly greater than 3000 there are none how about greater than 250 and there's probably a few the try fly is an inexpensive boomerang only a couple bucks each so if I said 100 three transactions control Z Z Z alright let's go look at our next example on CF 35 now in this example we want to look at and and/or criteria together so we want product quad or fast fly where the sales are greater than 2000 now I'm going to change this to 250 because we know that that greater than symbol is not going to work now we're going to have to use the and and/or function together and we did this earlier in the class mixing in andorra when we did accounts receivable accounts payable credit analysis equals am and i'll do the net sales calculation first every cell in this row needs to look at that sale so I lock the column and not the row are you greater than the sales and f4 now comma now we put either the or or the match function here and I'm actually going to do the match and we are going to run in trouble with the match but we will see a way to fix that hey that's going to be locked with the column so f4 three times and I'm going to look that up within the lookup array bring f4 to lock it comma 0 because the list is not sorted now the one problem with match here is match gives us n/a or a number and the end function is not as polite as the conditional formatting dialog box which had no problem with the heirs so I'm going to close this off control enter will copy it ver and down and you'll see that the n/a the and function won't be able to handle the n/a so I would use the or here if it's two but you know what I'm going to show you the more robust method because if you have lots of or then it's no problem what do we do we go is number so when you're using the match inside of the and to do or criteria got to put it in is number because is number will say true when it's a number anything else will be false alright so that formula will work control enter copy and over and then double click and send it down now the upper left corner copy ctrl C escape ctrl shift right down alt H L n arrow arrow arrow tab control V and then do some formatting so this is mixing and and/or wow that is cool 250 bucks or more we got the quad or the fast fly now let's scroll over and we want to see some more conditional formatting for rows I'm going to go over to CF 36 now here I just want to do above average and I've already calculated the average in the sale there so I could visually see now I'm going to do this one over here equals are the sales f4 to lock the column and not the row are you greater than or equal to the average the average has to be for this whole column f4 and locked in all directions alright control enter copy it over and down now if you can have the average into off to the side in a cell that's like right here then you can just refer to it in your conditional formatting formula we wouldn't use this we just go cell b4 and lock it if you're not allowed to have it in the cell then certainly this way will work the only downside to this is that you have to recalculate the average in every single cell all right I'm going to copy this in edit mode escape control shift right down arrow alt HL n arrow arrow arrow tab control V and then some formatting aha so the whole row gets it when it's above average that's pretty cool now let's go over to see F 37 and here's a wild one what would you do if you wanted every other row to be like green like the old counting paper templates every other row would have a formatting now you could do this manually you can even write a macro to do it but I don't know how to write a macro no problem we can do it with conditional formatting we first want to remind ourselves of our number incrementer meaning a formula element that will give us 1 2 3 4 5 6 as we copy down so rows I'm sitting in a 4 so I'm going to do a dollar sign 4 : a 4 now what's going on here is we've locked the floor but not the 4 here so as we go down that 4 will turn to five six seven rows tells you how many rows there are in a range of cells right now it's one this whole row will get 1 and watch this we can see one in every single cell but now when I copy this down it's going to be 1 2 3 4 and the whole row will get it look at that now remember we need some trigger true/false or some or 1 0 or some nonzero number well what if we took every single one of these numbers and divided it by 2 and looked at the remainder well 1/2 the remainder is 1 2 divided by 2 remainder is 0 3 divided by 2 is 1 with a remainder 1 so by dividing every single one of these numbers by 2 and getting the remainder it's going to give us 1 0 1 0 every single even number will get a 0 every single odd number will get a 1 so with the active cell at the top F 2 and we've seen this a few times the mod function M OD is the function where you give it a number you tell it what you want to divide it by meaning that's the denominator and it will give you the remainder control-enter to populate that formula into all the cells that is amazing that is a pattern of ones and zeros that we can use for conditional 4 now I'm going to hit f2 copy this in edit mode ctrl C escape delete because I don't want them in the cells alt HL n a bunch of arrows tab ctrl V and then we could do some formatting will make our old accounting templates before Excel you go to the store and buy your green and white accounting template papers and it will look something like that that is absolutely amazing now one thing about conditional formatting that we haven't looked at if you copy a Cell ctrl C and paste it somewhere copy copies everything it copies the conditional formatting if there was data validation it would copy it the number the formula whatever so actually you can do this on a couple rows or if you needed to extend it further you just drag it now there's no content in the cell but the conditional formatting is being applied as we copy it down that's pretty cool using the mod function to conditionally format every other row let's go over to CF 38 and here we want to look at a great example I've been using this example for 15 years in my checkbook I've been keeping my checkbook at least that long in Excel longer than that probably and i reconcile often more than one time a month i just want to put an x in this column and have the whole row turn yellow so when i come back next week or next month to reconcile all of the rows that don't have yellow will visually tell me that I still need to reconcile that all right so I'm going to highlight not the balance column just the whole template right and this is very simple we ask the question for each row is there an X in the e-collar all right so you ready OOP the X column and I'm going to hit f4 one to lock the e but not the six are you equal to in double quotes X and that's it click format click yellow click OK click OK and instantly if I take these X's away not reconcile ctrl Z when I come back and finally get this check number cleared I can put in X I love doing checkbooks with conditional formatting for reconciling I'm going to ctrl Z now let's go over to CF 39 now we would like to highlight or conditionally format the whole row but only when the number is in the top 3 now I've done the large function over here the large function says go get the third largest if I give the large function a 1 it goes and gets the max if I give it a 2 against the second biggest 3 is the third biggest so I'm going to highlight this whole table active cell is up in the corner alt H Ln arrow arrow arrow tab and I want to build my formula now I'm going to refer to this so I'm going to assume that I have this in the cell if you didn't we do just like we did a second ago with average we put the large in here but I'm just going to say this number right here and the whole row needs it f4 f4 Lock the column but not the row are you greater than or equal to the third biggest click some formatting click OK click OK and just like that if I say give me the only the two biggest instantly there's the two biggest numbers three well it gives me 4 because there's a tie for third so highlighting the whole row when we want to look at the 3 or some n value biggest numbers alright let's go over to CF 40 and now we'd like to highlight for each row want a separate conditional formatting I want to say conditionally format the smallest number and then when I go down to the next row conditionally format the smallest number I want a visual indication that this number right here is the smallest actually this formula we did back when we studied lookup this formula is looking up the suppliers name for the lowest value now notice we use the min here so the match function looked up the min which in this case was 32 64 it found the relative position using match right which would be 1/2 and then the index went up and got the word BAE erica's it was the second occurrence in this list here so we want to use a lookup format but then I want to visually see the smallest number with conditional formatting all right I'm going to highlight active cell at the top alt H L n a bunch of arrows tab equals min and watch this since we need to re-evaluate the minimum of value each time we copy the formula down I'm going to lock the whole range only on the column f4 f4 see the a and the ear lock so each cell in this first row will be looking at a 5 to e5 but as soon as it gets copied down to the next row the fives will turn to sixes now that will determine the min value for each row so each one of these cells would have the min now we need to say ru equal to relative cell reference 1 2 3 so this min will pick out the min and compare each one of the numbers in each row as soon as it gets a true it will apply the formatting I'm going to click format click some format and click OK click OK it's amazing now this one is kind of hard probably we should look at it over here just equals men and the active cell and watch this if we lock just the column references F for three times and ctrl enter to populate that formula you could see it delivers just the min for the row but into every single cell now I ask the question f2 to put an animal are you equal to relative cell reference control enter to populate that formula and only the one minimum value in each row will get a true so right here boom all right let's go over and look at our next example CF 41 now here we want to talk about intersecting cell so Sheila dawn March so that would be Sheila dawn March so I'd like to coops Sheila dawn March so I want that 82,000 well this one is just an function because I'm comparing the column headers to whatever's in cell b4 and the row headers to whatever's in cell b3 so let's build it down here equals and is the column header and this whole column here is going to need to look at January so I hit f4 twice lock the row but not the column now because the B is not locked when we copy it over here the dancing ants will move to February are you equal to March and lock it in all direction with the f4 that's logical test one logical test two I say oh the column header and that needs to be locked one two three times with the f4 key so when we move this way it's locked on Fred but when we copy down the seven will move to eight and move to sue are you equal to the name up here f4 close parentheses control enter copy it over and and let's check that out look at the one little loan true right there if I were to change this to Mei instantly now I get Sheila dawn may so now we simply have our patterns of true and false as we copy the upper left formula ctrl C and edit mode highlight alt HLN arrow arrow arrow tab ctrl V alright this is so cool look at this February whoa it jumped right over there for Sue boom that is beautiful conditionally formatting an intersecting cell now this one was relatively easy for that if we know the and function and mixed cell references right we can get a little bit more difficult because sometimes like for taxes you don't have an exact lookup so let's go look at an example over on CF 42 now our goal on this sheet is to type any income here and highlight the appropriate row these are taxes here's our income here's our number of allowances so four thousand five hundred if we were doing a proximate vlookup we look this up it raced out in this column bump into the first number bigger jump back one and then go and get something but here we're going to do something really strange we're actually going to vlookup this number and retrieve the item from the first column because we cannot do an exact match we can't say is this equal to one of these row headers so first we have to do vlookup retrieve the item from the first column which would be two seven five zero and then use that as our row header all right let's see how to do this equals vlookup I'm looking this up f4 in all directions comma and here's where the weirdness starts to happen the lookup table is simply one column this is one of those rare circumstances where you're looking something up and retrieving it from the same item f4 what this means is vlookup will take this race through the first column it bumps into the first bigger one it jumps back comma now in column index when we say one it will actually look that up that 2750 is what will be returned back to the cell the range lookup we leave out because we're doing approximate match and that's the default and this is going to be one weird formula control-enter every single cell will get the same number now what's so beautiful about that because now we can f2 and say are you equal to the very first one because remember I'm building my conditional formatting in that top left cell the active cell and so I have to compare it to this row header and what do I need to do lock it f4 f4 f4 lock the column but not the row so every single cell here will be asking is that 275 equal to 0 false is all the way is equal to 500 falses but when it gets down here it'll all be throughs ready to populate the formula and the active cell I hold ctrl + Enter that is why I look at that every single cell got the vlookup looking up that 275 but now that a 10 with the a locked only is looking at that row header now I can copy this ctrl C escape alt H Ln arrow arrow arrow tab control V format cells this one's so special I'm going to do something different look at that I'm going to go front color white and dark blue you got to be kidding me now watch this when I put my income of 650 instantly the whole correct row is selected control Z now on this next sheet 43 we want to do conditionally format the row and the column and the intersecting cell CF 43 all right here we're going to conditionally format the row the column and the intersecting cell now the hard part is the row right because we have approximate match I'm going to build the formula down here and repeat what we just did equals V lookup I'm looking up this f4 comma within that range right there f4 and that weird comma column 1 don't need the 4th argument close parenthesis are you equal to the row header active cell first row had our 92f four one two three times lock the column but not the row control enter that formula will work for the row f2 will come back and use that formula and put it into our dialog box but right now we're going to build the formula for the intersecting cell notice for intersecting cell we have to get a true right there but we also have to get a true from the column header too so this vlookup is one logical test we need a second logical test if we're doing an intersection so we use and okay logical ones already done I click at the end comma and logical to well this one's not so hard active cell I click on the column header f4 one to lock the row but not the column are you equal to the allowances in cell b4 f4 to lock it there's our two logical tests close parenthesis control-enter to populate that formula into all the sales and there you go there's our true if I change this to five instantly there's the true control-z now I'm going to copy from the upper left corner control C escape highlight alt H Ln arrow arrow arrow tab control V format and I'm going to format this read the intersection is going to be fill red and font white for the row and column we'll do orange and yellow because orange and yellow make red so at the very intersecting cell boom it will be red now if I change this to 5 I love that look we're doing exact match from the column header and approximate match from the row header if I change this to 250 instantly I get tax of 0 I like that controls easy alright now let's go there's one conditional formatting we're going to pile to more and in fact if you highlight this in alt o D to go to the manage rules there's one we're actually going to add a second and the third one alright well this one's going to be easy I'm going to go steal C the logical one argument screen tip I'm going to click it highlights that argument ctrl C escape highlight all to HLN arrow arrow arrow tab control V whoops I forgot the equal sign equal sign click OK and this is going to be orange fill look ok click OK oh look at that as you add conditional formatting it's piling on top no problem alt o D to get to our manage rules once you have a bunch of rules there's a couple new things you can do you can push things up and down we want to push the orange down to have it come second we definitely want the red on top we're not going to use that this stop if true in any of our examples in this video but if you wanted to just stop if this first condition was met you could check that all right so one two click OK and sure enough now the Reds on top of the orange alright the last one fault HLN arrow arrow arrow tab this one is simply is the row header f4 to lock the number but not the column are you equal to this particular person's allowances format and I'm going to do yellow click OK click OK oh wait a second that's not going to work alt OD no problem I'm going to push this one down either one of these will be fine in any order below the red cuz the Reds on top click ok that is beautiful and watch this magic 5 no problem tax is 15 income of 956 no problem the tax of $1 controls easy we used three different conditional formatting x' the row the column the intersecting cell we saw how to move conditional formatting up and down in the manage rules dialog box let's go look at our next example 44 - 45 now on this sheet we want to see something spectacular this is called a Gantt chart we have some days at the top and we simply want to enter a project the start date 1 / 2 / 14 how many days and when I hit tab I want it to show me the end date for the project and conditionally formatting green the workdays and Red's going to show up as a weekend or holiday so if I put changes to 3 tab instantly I have green green green red because that's a Sunday green that means we're going to work alright I'm going to delete this right here delete the formulas now I'm going to start with our end date going to use the workday function now work day work date International is one we want this function came in in 2010 you simply give it a start date comma the number of the days what the weekends are and check this out this new dot international function that came in 2010 has all sorts of alternative weekends not just Saturday and Sunday our weekends are on Sunday so I double click this 11 that's the weekend comma holidays you can either select a single cell or a whole range f4 that way it'll know to skip over Sundays and this particular holiday that means it will go from this serial number to the end serial number that is absolutely spectacular now I don't want this to show up so I want to put an if function and I'm going to say if and I'm going to use count aw count accounts non empty cells I'm going to highlight the three relative cell references I'm going to say when you are equal to three that means all three bits of data have been entered then the value if true is the work date international otherwise null text string double quote double quote that tells the formula to show nothing close parentheses control enter double click and send it down that way if I enter something here the date here it instantly will give me the date control-z a bunch now how are we going to add conditional formatting well guess what each intersecting cell has to compare the start and the end date to the column header this is and a and D we're doing between the column header date has to be between the start date and the end date notice these are at the head of the row these cell references will have column locked this cell reference will have row all right so I'm going to come down here and just try this equals and I'm going to say column header and lock it f4 f4 row lock but not the column that means when it moves down every cell will look at this Monday but when it copies over it will move to Tuesday and then Wednesday are you greater than or equal to the start date and that's got to be locked as we go this way it's got to be locked but when we copy it down it's got to move to the next one so f4 lock the column but not the row that's the first logical test the second logical test he is the Monday lock the row with f4 key are you less than or equal to the end date and this one's going to lock down the column close parentheses control enter copy it down copy it over we can very clearly see this last row as a problem it is looking at these cells right here we need a third logical test and I'm going to pick is this a number because look this is either getting text or it's calculating the work day which is a date so I'm going to add a third condition comma the third condition is is number it's saying whatever's in this cell and I got to lock the column but not the row are you a number close parenthesis that'll be our third test one two three between date and is has the number been entered in the end date column control enter copy it over and copy it down there you go we eliminated all those now that's our first condition for green so in the active cell I'm going to copy ctrl C escape now come over here all to HLN arrow arrow arrow tab control V and I'm picking some green click okay that's pretty cool and let's test this I love testing this it's like Christmas and four alright so that's working their controls easy a bunch now we need to do the red thing because look that's a holiday and really what we did is this picks the actual end date but there may be some days in between these that are holidays or weekends alright so we're going to look at the net workdays dot international whoops so this one is similar to the workdays we looked at up here this is the earlier version only did Saturday and Sunday but since Excel 2010 we have these international functions which are absolutely awesome now here's a little trickery normally this counts the number of days whereas workday dot international gave us a serial number this actually counts between two dates watch this I'm going to totally took this I'm going to say column header f4 to lock the row but not the column that's the start date and the end date is going to be the exact same day that means the biggest possible count this can ever get is one it gets one or zero hey that'll be perfect for true or false now we simply give it the 11 for Sunday notice that's the same as the work day function we just did up there comma and the holiday f4 to lock it in all directions close parentheses control enter all this is going to do is say one when it's not a weekend or a holiday the zero is really what we want well we need that zero to become an either a one or a true so check this out f2 there's the not n ot function and all not does is it converts a true to false and a false to true not we'll follow the rules of and and or and if and conditional formatting if it sees zero that's false any on zero number will come out as true so we've converted because we're interested in the zeros we want the zero to add conditional formatting of red for weekend but the zero won't do it not will convert it control-enter to populate that formula all the way through and look at that boop-boop now there's one problem with this it's doing the whole column and you can see there's three cells I don't want so I'm going to have to add that's one that's saying it is a weekend I have to add all three conditions so I'm going to scoop this out of here ctrl C escape come down here and that logical test that's just that one comma logical test to will on it when I when I ctrl V one two three and four I come to in close parentheses control enter and that is the wild formula that will give us two truths for it's a weekend there another true right over here for that one that is amazing we could I could never have built this just from scratch this is one where you have to build it in multiple steps but once you paste it all together top left cell control C and Etta mode escape highlight alt HLN arrow arrow arrow tab control V and I'm going to apply red that is totally spectacular this is a project I'm going five days instantly it tells me the end date and there's our beautiful conditional formatting green for it's a work day red for it is a weekend or holiday now one thing we might want to fix here is that says five and there's six days so what the week day function is done is added five days if it's really total five days then we can simply come to our work day dot International which is a serial number and subtract one control-enter so now each one of these green cells one two three four five corresponds to exactly the length of the project all right we have a couple more amazing conditional formatting tricks let's go over to CF 46 and on CF 46 we want to see how to conditionally format duplicates now there's two ways the built-in method and the formula method hey let's just highlight we could see there's some dupes Jojo gggg go up to conditional formatting highlight cell rules and down at the bottom duplicates click OK the problem with the built-in method is it will highlight all of the duplicates and sometimes you only want subsequent duplicates to be highlighted so not the first Jo just Jo the second time third time fourth time all right so I'm going to come over here to the cell and build a formula we're going to use count if function now check this out we're going to say range is c6 colon c6 and the criteria will be c6 also now that's totally strange right now we just count one because the criteria is Jo but watch expandable range so the first c6 I'll hit the f4 key twice lock the six but not the second six control enter copy it down that means that this blue range will be expanding so it counts one for Jo but when counters the second one since it's a relative cell reference that c9 it gets a count of two same with GG and Smitty now we can amend this because what we're interested in is formatting two three four five so I could say anytime the answer from countif is greater than one control-enter double click and send it down that formula true true true will format duplicates only after they occur for the first time in the top cell ctrl C and edit mode escape highlight alt H Ln arrow arrow arrow tab control V and we can format it however we'd like click OK click OK that is totally amazing now let's go to our next sheet C F 47 and we got to talk about comparing lists I would like to highlight the items in list 2 that are not in list 1 now we've done comparing to lists many times in this class we know it's the match function we're going to say hey look up the item in list 2 comma over in list 1 f4 to lock it comma 0 because they may not be sorted ctrl enter and copy this down now we're really interested in the Naas so in order to get n/a to a true to conditionally format because Timmy's not in list 1 f2 we simply wrap is n/a we saw this back when we studied lookup functions I'm going to populate this formula into all the cell's control enter that formula will work true that means Kim is not in list 1 so in the top cell like copy it in edit mode ctrl C highlight alt H Ln arrow arrow arrow tab control V and format it however you'd like click OK click OK that is amazing conditionally format the items in list 2 that are not in list 1 now the formula is a little bit easier if we go over to see F 48 I want to find out when the item in list 2 is also in list 1 here we just have to run match and ask the question is the item over in list 1 F 4 comma 0 ctrl enter any nonzero number will be true na s will be ignored ctrl C from edit mode highlight alt H Ln arrow arrow arrow tab control V format it however you'd like and just like that this is gigi is in both lists 2 and list 1 now let's go over to see f9 hey this is our stock example I just want to highlight the whole row when the profit is less than 0 alt H Ln arrow arrow arrow tab and I'm going to build this right in the sell profit 1 2 on the f4 key lock the column but not the row are you less than 0 format it click OK click OK so now if price today on GE goes down to 21 instantly that is conditionally formatted control Z let's go over to see how 50 and this is a cool one we want we have this huge product list and we want to conditionally format the product word or text when there's nothing entered into this row well guess what we can use count ah now I'm going to assume that text or numbers can be entered in here so we use counter count ah counts not empty cells and when all the cells are empty count all will returned as zero I'm going to highlight alt H Ln a bunch of arrows tab active cell right here remember each one of these products need to look at the row so I'm going to go equals count up and highlight relative cell references the whole row f4 one two three times close parenthesis now that will deliver a zero for the row that doesn't have anything and I really want to convert that zero to a true so we're going to use not and that formula will work format and whatever formatting you want click OK click OK that is totally cool as soon as I enter anything into the row for this product now it doesn't have the formatting control Z alright let's go to CF 51 our last example we just want to look at the fact that conditional formatting if I want to highlight this and use the dialog box it can handle array formulas no problem so here i want to find the max for the given city so equals max well there's no max if so we just put if inside of the max the logical test we want to generate a bunch of trues and falses so we say anything in the city column f for our u equal to whatever is in cell e4 f4 to lock it if we get a true then in the value of true we want to dump the numbers into the max function f for i close this off i'm going to highlight this in f9 and watch what happens when i F 9 that if function has filtered out all of the values and only the values for the city Berkeley are going to be dumped into the max control Z now this formula is an array formula there's an array calculation inside the logical test that means logical tests expecting a single true or false f9 I'm dumping a bunch of true falses into that logical test that means we're making an array operation control-z logical test cannot handle that array operation unless you use the special keystrokes control shift enter now watch this closed parenthesis if I hit enter value error means you forgot to use the special keystroke we have to tell the if to make this special array formula calculation so watch this control shift and enter now it works immediately look up to the formula bar you see those curly brackets that's Excel telling you it understood that this should be an array calculation now watch this I'm going to highlight this in edit mode control C remember it's only going to deliver that number escape I'm going to highlight this in our formula is going to be an N we're going to say is the city equal to Berkeley or whatever cities in efore and is the sales number equal to the max for that city so you're ready in the active cell alt HL n arrow arrow arrow tab equals and and I'm going to start with is this city right here f4 f4 lock the column and not the row equal to whatever is in e4 and control V that I accidentally copied the equal sign is whatever the result from that array formula ru equal to the sales and I'm going to hit f4 f4 lock the column and not the row closed parenthesis so those two things are sitting inside of the and format yellow click OK and click OK now if I change this to Seattle instantly that whole row and the max for the Maxima Seattle is selected if i select oakland boom that is pretty amazing hey that was 51 examples plus a bunch of other cool stuff about conditional formatting all right we'll see you next video

Channel: ExcelIsFun

Views: 225,944

Rating: 4.9035368 out of 5

Keywords: Excel 2013, Highline Community College, Busn 214, Spreadsheet Construction, Basic To Advanced Excel, Mike excelisfun Girvin, excelisfun, Slaying Excel Dragons, Ctrl Shift Enter Mastering Excel Array Formulas, Conditional Formatting, Conditional Format, Excel Conditional Format, Excel Conditional Formatting, Conditionally Format the row, Conditional Format the column, Conditional format top 3, Conditional Formatting with Formula, Logical Formulas to apply conditional formatting

Id: GRfe4bHsjhI

Length: 88min 15sec (5295 seconds)

Published: Wed Nov 27 2013