Ch02-AdvConditionalFormatting

in this video I want to talk about a couple of kind of advanced conditional formatting and the first one I want to talk about is the order of your conditional format we did a little one an example in the previous V video I wanted just do another one uh currently so what we want to do is we have a uh um a balance sheet here uh amount Ed and how late these items are and we have a bunch of of uh customers so I'm going to click on D6 and I'm going to use my old control shift down command shift down and we're going to create three uh conditional formats um so we're going to have things that are over 30 days over 60 days and over 90 days so we'll do the first one so we'll do highlight greater than 90 days and we'll just make make it um make it red and we'll click okay and then we'll do the second one which is over 60 days so highlight cells greater than 60 and we'll make this make sure you change the colors I would do yellow and then we'll do one more greater than 30 and we'll make this green now you'll notice that everything is is green because what's highlighted except for these uh two on row eight and nine for Connell and Lynn they're 29 days out the uh everything else fits that that conditional formatting it is greater than than 30 days but we wanted to have some other steps we wanted to have those that are 90 days and those that are 60 days so let's go see what was going on with the conditional formatting so we go up here to conditional format go down to manage rules you'll find you'll work here a lot let me scooch this all within the screen and you'll notice that we created the 90day then the 60-day and then the 30-day so the first one created will be the last one applied so what I want to do I want to take this 30-day and I'm going to bounce it down and then I'll bounce the 90day up so however way you want to work it um remember at this point if I no longer want a greater than 90day uh formula then I could delete that or conditional formatting if I want to change anything I can edit the rule but we're going to keep it the way it is now uh you may find in some dialogue boxes that there's an apply button and what that does is it actually literally applies your changes but it doesn't close the box so I'm gonna hit apply that allows you to give it give a quick overview so maybe you don't like the yellow it's like oh one second thought I really don't like that yellow I I want all the text to be black so maybe over here with 90 days I'll edit that rule I'll format it I'll go to font and I'll make it black and then I will apply it to see if I like it oh yeah well works works a little better I can do the same thing for the uh the greater than six yeah edit the rule format notice it leaves me with the last tab that I worked on so again we'll just do black um you may decide that oh I want this to be also bold so I could actually hit bold we'll see what that looks like um we'll click okay and we'll click okay again and then we'll apply it so look like it changed everything that's interesting don't know why it did that I think I'll take that away that sometimes things do some really interesting things we'll just do it regular because this was I was only editing the one for 60 days I don't know why everything there we go we'll just put it back just so it looks good and then we'll just click okay and then that accepts that so when you are creating the uh conditional formatting you do want to take in consideration the order and basically if you don't like it if it's just no it's not work working right then you just go back to conditional form formatting and then down to manage rules and that'll allow you to edit those um so this one is actually using a function called Date diff and it is a function that is used for compatibility with lotus 123 I don't know why after 25 years that there needs to be compatibility I guess people are still using some older files and so when you use this this date diff so if I type in equal date def you'll notice it doesn't show up in that list if I just backspace over you see how we get that little that little list of of functions date diff is not part of that uh function that function list you have to completely fill it out so just a little side note there um I'll have a video on that as well all right so let's go to museum Advanced and this one we're going to do a little bit different with our conditional formatting so if I go back to the art museum and you'll notice that I have my conditional formatting that anytime a certain cell met a certain criteria um it would give it a particular formatting that we we had apply but it only give that particular cell that application what I want to show you is how you could actually have it highlight the entire row which can actually be kind of useful so I'm going to go back to art museum advance so it's really the same data um I changed the numbers in the first one so what I've done is I've highlighted the entire spreadsheet the entire table and we're going to have to create these from scratch so I go to conditional format and there's nothing up here that'll allow me to do what I want to do I'm going to actually have to create a new rule so I'm going to do a create a new rule and I want to do one that's using a formula so we have all all different types things are containing bottom top bottom all that good stuff but I want to use the bottom one here that says use a formula to determine which cells to format so I'm going to click on that and what I'm going to do is I want to start with the equal sign and we're going to make it dollar sign H um let's see two so I'm picking out that first that First Column and I want the H the column heading uh identifier to be absolute because I want to be able to use G and F and E Etc so that's how I make that that absolute and uh the two will then obviously as we move it down or as we continue working down it it'll it'll change to the ro we're using and then we want to just have this be greater than um and let's say let's say 20,000 make sure we get enough zeros 1 2 3 and then we'll format we just do something simple going to do a fill and we'll make it this kind of this light green so we'll click okay we'll click okay so not only did it highlight the actual cell it highlighted the entire row which actually could be actually useful when it comes time to print so if I go over here to uh print preview you can see that it will it does highlight the entire um the entire column let me go to lay page layout and change the orientation so you can see things a little better so the conditional format was based on the appraised value but then it just applied that formatting to the entire column so let me just show you that we're going to edit this um so we'll go to home conditional formatting and manage rules and basically it was equal dollar sign H so this is the column that we want to base our um our criteria on two because we're starting with column two or excuse me row two and then what we want it to do greater than less than equal to however we want to do that and then we did we did the colors and you'll notice it applies to and this is the important part it applies to A2 to h116 so if I want to go and change something here I can edit the rule I could change this to uh 15,000 10,000 however way you want to do it you could change the color let's say we'll do a kind of a nicer blue H let's do the yellow and then we click okay and okay again and as I mentioned before the conditional formatting is is dynamic so if I take this first entry here and I change this to 21,000 you will see that it applies that conditional format so if I go here and I change this to 5200 so it's no longer meeting that criteria and if I hit enter it removes that so this is kind of neat when you have a very wide uh spreadsheet and you want to highlight the whole the entire row so you can you can work with that so that just a couple of quick little Advanced um uh formatting conditional formatting thank you

Channel: Joe LaMontagne

Views: 3

Keywords:

Id: d30i70dQ7U8

Length: 10min 4sec (604 seconds)

Published: Sat Jun 15 2024