8 Expert Tricks for Conditional Formatting in Excel

here are eight tricks that will put you on the path to being a master at conditional formatting in Excel I'm gonna show you techniques that range from the relatively simple to the more advanced so that you will feel comfortable creating all sorts of complex functions and formulas across tables and worksheets and putting all that into your conditional formats before we start check the video description and click the link to teach Excel so you can download the files for the tutorial and follow along and make sure to subscribe and accept notifications so you can see all the new tutorials here I have a very simple table we're going to use for this tutorial it's got a part label then as a part number manufacturer stock price value and over here we have a master worksheet we'll use for one of the later examples so for the first example let's talk about relative and absolute cell references these are so important when we're dealing with conditional formats so you may already know how those work let's do a simple example here equals let's say sum doesn't really matter what we do and if I copy this down the sum is now applied to every row just like we want but if I go here and I hit f4 or just put the dollar signs in front of all the references and copy it down the cell references do not change and this works exactly the same for conditional formatting so let's start with a very simple very basic conditional format and this will help you better understand the concept let's highlight these cells any one of them that are over 5,000 now in this tutorial we're not going to be using any of these default rules that's too easy and you can already figure out how to do that everything is going to be a custom rule so you could go down here and click more rules or just go to the bottom right here click manage rules new rule doesn't matter how you get to this screen but once you get here we're going to use a formula to determine which cells to format so everything we do is going to be a custom formula or function here now what you want to do it's a very good practice you don't want to enter that formula by scratch here you want to enter the formula first in the worksheet to make sure everything is okay and happy so we equals we want to see if this cell greater than 5,000 hit enter now what we can do I like to copy it down for the data set just do a little checking true yes yes yes make sure everything looks good now let's go and grab the sky copy select the range where we want to apply the conditional formatting Home tab conditional formatting new rule use a formula to determine which cells to format paste it in there don't hit the arrow keys or do anything else here it's very easy to mess this up once you've entered it go to format let's give it a fill make it Orange hit OK hit OK and now everywhere that this said true we have highlighted cells nice neat and perfect now if I go here to conditional formatting go to manage rules we can see this rule here you can see the formula over here equals f2 greater than five thousand and you can see that it applies to the entire range f2 to f8 now let's redo this rule so I'm going to go ahead and delete this I know this seems basic but it's gonna be more important when we do the later examples now let's say I have dollar signs here when I copy it down you can see everything becomes false just like if I copy and paste this into the conditional formatting new rule formula copy paste format Orange okay okay nothing happens this is the number one issue with applying conditional formatting across ranges cells rows column everything is that people forget if they have a dollar sign in there or not if it's absolute relative and they think what the heck is wrong I click the cells go here manage rules everything looks okay it looks almost exactly the same as the last time applies to f2 to f8 Range f2 greater than five thousand except for the dollar signs so if we go here let's remove these dollar signs hit OK hit OK and everything is back to normal so be very careful with absolute or relative cell references now that we have that down let's move to the next one it's gonna be a lot more fun let's do conditional formatting on multiple cells at the same time so remember for conditional formatting custom formulas the only thing that has to happen is it has to evaluate to true or false that's all that we need so the formula for this conditional format evaluated to true this one evaluate to false this one evaluate to true so that's all we need is to have a formula or a function that outputs true or false that's it we can make it as complex or as simple as we want after that so let's go ahead and apply some interesting rules here let's say that we want to check if the manufacturer is ASC and if their stock is less than 50 maybe we want to initiate a purchase order if it gets to that point lots of different things we could do so let's go down here I'll make it a little bit bigger and let's enter our formula so all it has to do is evaluate to true or false in this case we can use the + function we don't have to use the if function here so and so we want to see this equals ASC this less than 50 now we have our formula perfect and then copy it over here now let's copy it down and let's check it make sure everything's ok true ASC 40 and looks like there's no other one ASC 6890 okay let's make another one so it goes below that let's say 45 oops perfect you can see this conditional format automatically updated as well I love conditional formats all right so now we have it for two cells and we just have to figure out what we want to be highlighted do we want this to highlight or this to highlight we probably want the stock or maybe the part number so you know exactly what you want to reorder so let's select stock conditional formatting new rule down here paste our formula choose our format let's make this one maybe read it okay perfect see it's so so easy now when you copy paste the formulas make sure that you do double click the cells so you can copy exactly what's in there and of course make sure that your ranges are relative or absolute based on what you need to do for instance if we weren't going to hard code ASC in here maybe we were going to reference another cell like h1 up here that's a like this c2 equals h1 you would want to put the dollar signs around h1 so that it didn't update when we copied it down so let me do this now copy it down type ASC so you want the h1 to stay the same for every formula so that's a case where you're going to mix absolute and relative cell references and how you might do that sometimes you want to have a little drop-down menu over here and when you change the options you can change it from ASC to BSC then the conditional formatting within the table itself can update and it's actually a really neat way to have your table seem kind of dynamic and look really cool especially when you're presenting it to other people but it's really not that much work now let's go ahead and I'm gonna show you a couple different ways to do this I'm gonna go ahead and put a s see back in here ASC copy you down okay so we've got this formula a nice needs super simple now let's say that we want it to be two-tiered so let's say that we want to have a reorder purchase order made if the ASC manufacturer parts are below 50 but for the BSC if they're below 100 maybe there is a higher purchase rate for the BSC so we need to make sure we have more items in stock and we want to do both of these at the same time now I'm going to show you perhaps a logically more simple way that somebody might do this and then how we can do it a little bit more like we just did the last formula so how a lot of people are going to do this is with the if statement and you are more than free to do that we simply use the if now we make our logical test we can test and actually let's go ahead and just paste this in here because this is our first rule so equals if-then that so if it equals ASC and is less than 50 we want to initiate a reorder so true then comma if it's not true if it doesn't equal ASC or it's not below 50 then we want to do one more check so if and now we want to do another and we can say C 2 equals BSC comma and the stock is less than 100 close the and comma if that's true if it equals BSC and is less than 100 we output true so it will highlight so we can reorder it if it does not we will output false close the second if statement close the first if statement hit enter okay that's a lot of stuff looks really long but it's not too big it's just too and functions inside of an if function and another if function so let's copy it over here test it out copy it down make sure copied perfect now let's see true should be true yes we go to the BSC BSC less than 100 yes reorder here ASC over 50 nope here BSC over 100 nope no reorder so it's much more helpful to troubleshoot it like this in your spreadsheet as I've already mentioned and now we've got this nice neat long complex formula that we can copy in here for conditional formatting so I could go ahead copy this guy go over here you don't really have to select all of the cells but I just find it a little bit easier to not have issues if you do that so here manage rules double click just highlight it delete everything and paste in the big long formula hit OK okay and there we go now I've showed you two ways you can do something like this using the and function because it returns true or false using the if function with some hand functions in it this might actually logically be a little bit easier for you to follow and troubleshoot in the future that's why I showed you how to do it this way but you can also use the method that we did just a moment ago so here's one and and what you want to do this time it's put it inside of an or function so one and there let's close that go back here copy the second one we have both and functions within an or function so the or function says if one or the other is true I want to output true and the end function says both of the things inside of me must be true for me to output true so you can see when I copy this and let's go over here paste it it should be exactly the same perfect so you can use the longer.if or the or and the and functions now when you're working within the spreadsheet itself it's oftentimes not very useful to output true or false so you're gonna stick with an if function and here where it says true and then true or false you'll output some text or something useful for the user but when we're dealing with conditional formats all you need is true or false yes or no so you can eliminate the if statement but like I said if it's a little bit more confusing for you to use or and and like this stick with the if statement it doesn't matter if it's longer so long as it makes it easier for you to understand troubleshoot and change in the future now we're building up a lot of formulas and I'm going to delete them from the spreadsheet now but at the end of the tutorial I'll put them back in in a way so you can see all of the examples because I think showing you examples of different formulas and ways you can do conditional formatting will help to give you a better idea of what you can actually do on your own for your own data set so the next thing is I want to show you that you can use the text manipulation functions just very simple so let's say here we want to see if the manufacturer is ASC and the part is also AFC but the part is in the very middle of this cell you can see down here we have two CSCS so first things first let's get the middle part out I like to do formulas in segments or little chunks to make sure everything works before I create the larger formula here we're going to use the mid function so mid text start number I want to get the ASC you can see that that starts at 1 2 3 start number 3 how many characters ASC 3 characters long three and we have a SC perfect now since the data is formatted all the same here it makes our text manipulation functions very simple but you can use whatever text manipulation functions you want to do whatever you need to do now we want to test if this equals this super simple so let's combine the two perfect now go ahead and copy this dude up here true ASC ASC ASC ASC and then a bunch of false let's go ahead and copy this guy we shall put them right here conditional formatting new rule paste it in everything is okay because we have relative cell references hit okay hand I Ford got to choose the formatting manage rules double click formatting make this light blue okay okay okay it can be a bit tedious so that's I can use the text manipulation functions you see it's just like you would use them within the spreadsheet it's just that you have to make it output a true or false so to do that that's where we use this equal sign we're using a comparison operator here so you can use equals not equals to greater than greater than or equal to less than less than or equal to now here we just want to see if it equals it or if it doesn't equal it now let's get into something more fun let's do highlighting of entire rows you should pretty much understand how to do this now but there's a little bit of a caveat so let's go ahead and copy this guy actually and make a set of this data move it over here and I'm gonna say entire rows go ahead and clear all of the rules very quickly entire sheets I hate these little green arrows they're so annoying converts your number numbers sort ignore error okay so we want to highlight an entire row first we have to figure out a condition what makes us want to highlight the entire row let's go very simple as stock the stock is getting low so let's just say so thing like this this cell is less than 50 all right so we have a few rows that are going to be highlighted here I'm not putting any other constraints on it let's just keep it nice and simple now let's go ahead and copy this data first you want to select your entire table conditional formatting new rule right here here copy it in format give it a nice light blue hue and let's see what happens wow this table looks really messed up doesn't it can you tell what the issue is the problem here is that we copied this for an entire table all of our other examples we've just been doing the rows so I messed up on doing absolute or relative cell references like I said number one way that you're going to cause issues and this doesn't it's not obvious what the problem is here this just looks really wacky so let's go over here we don't want the column to change we want the row to change right we wanted to update for each row but we don't want it to change based on the columns that we copy it to so the columns are gonna stay the same so we put the dollar sign in front of the D and we don't put the dollar sign in front of the to leave it like that now this right here nothing's gonna happen here it's gonna look the same but let me go here copy that let's go ahead and put it back up here conditional formatting manage rules delete paste it back in hit OK okay and there we go so our table looks nice neat and perfect and all we had to do was add that one little dollar sign so you can see you can make the entire row highlight based off of any one of the columns doesn't matter which one you use just make sure that you have a dollar sign in front of the column and then select all of the table everything you want to highlight so as many row as many columns as you want to highlight and then apply the conditional formatting so it's really simple and let's say this was updated so no more roll row highlight now go ahead and clear this once again I'll have this formula at the end of the tutorial listed really nicely let's do conditional formatting across worksheets so here's my master worksheet and you'd have a bunch of stuff here that could summarize all of your data and combine it and present it however you want it and let's say one of the cells one of the pieces of data that you want is something to do with inventory not only do you want to list the inventory perhaps but you want to have it all added together and you want to know if you need to go and rebuy something restock something so over here in the stock table we want to see if anything over here is low enough that it needs to be repurchased let's keep it simple so it's not going to be specific to manufacturers here once again we want to make our formula in a separate cell equals men go over here select our stock close parenthesis now let's hit let's say less than 50 once again and so what we did here is remember I told you we need something to compare the left on the right side something to compare this function with over here so we've compared the minimum amount of stock for the cells there with 50 so if I take this out we can see that the minimum is 20 which means something needs to be reordered okay now that we have it output true or false we want to copy this select the cell conditional formatting new rule down here same thing coffee pasted in so you'll notice that when we copy paste it in here it still has the reference to the other worksheet so the formula is the same as it would be in the worksheet when you're working between multiple worksheets nothing changes just make sure that you build this guy in the worksheet first and make sure it works and then just copy paste it here into the conditional formatting window get our format read okay okay now we have a nice visual reminder on our master worksheet that the inventory is low and we need to go to the stock worksheet and figure out what needs to be repurchased or reordered and that can really make your master worksheet quite nice now let's copy this guy and I'll show you another cool thing create a copy okay this time let's do formatting just like it would be done if it was a table so if it had a table format sometimes you don't want the table format but just don't want it to look kind of cool like that so conditional formats clear rules from entire sheets now let's have the rows alternate this is really cool how you want to do this there are a couple different formulas I'll show you the old one and the new cool one so we need to figure out which row is odd which row is even so that we can actually alternate them so the original a is equals mod open row comma 2 equals 0 copy it down you can see false true false true false true so that's how you can alternate the row colors and if you wanted to change that up reverse it it could change the equal sign to a not equal sign like that and here we go and just like the example two examples ago you can understand how we're gonna alternate alternate the row colors now we're just gonna use this formula select the data table and pop it in there this function that we're using here just cover it real quick it's modulus function it's used all the time in programming it's basically just gonna divide the row by whatever number you put here and return the remainder so this is saying if there is a remainder or is not a remain kind of confusing now what we could have used instead is this equals is odd returns true if the number is odd and we use the row function again which returns the number of the current row close parentheses and you can see we get the same result or equals is even row same thing I'll have all these formulas for you at the end of the tutorial no worries but this is how you do it let's choose this formula is odd row we don't have to worry about cell references here so I'll elect everything conditional formatting new rule paste it in format light blue okay okay now we have a nice neat table formatting without having any of that other table garbage on there which yes though can be helpful can also be rather annoying if you don't need it or want it now the last two things that I'm going to show you are how to work with conditional formats kind of so let's say that you have the conditional formats like this it's set up and I want to quickly and easily copy it to another worksheet so let's go ahead and make our new worksheets and then what we're going to do is we will remove all the conditional formatting from this guy say it doesn't have any and let's go here and we want to apply it to this worksheet or however many other worksheets there are so we go here we want to select the other worksheets that we want to apply it to so you hit the ctrl key and you select the other worksheets or you can simply hit let's get away from that shift and select the worksheet and then every worksheet in between these two will be selected as well then we select the data we go to the Home tab fill across worksheets then choose for and let's say the data is different on each worksheet format okay now we can go here to our second worksheet and it's automatically copied over that's a really neat tip that's gonna save you a lot of time note though that all of the data has to be in the exact same place on the worksheet so the data doesn't have to be the same so it has to be in the same location now the last thing I want to show you is how to find out where all your conditional formats are so here it seems pretty easy but someone might actually not know that these are conditional formats but let's go back to our stock tab and you want to know where all the conditional formats are well we could go to conditional formatting and click manage rules and if you have a cell like this selected without any rules it's going to show you nothing is selected but if we go up here show formatting rules for change current selection you can change it to this worksheet you're going to see all of the conditional formats that are applied on this worksheet you can see where they apply to so the cells and the ranges and the conditional formats themselves and you can go ahead and double click them if you want to change anything about it I do however I do recommend that you do not change the formulas and functions inside of this little window right here if you want to change this just copy it paste it into the worksheet change it there then copy paste it back it's gonna save you a lot of headaches you can also view the conditional formats for the other worksheets but I find it's not really as useful if you're not viewing that worksheet so I prefer just this worksheet so you can go ahead and see some of the output see the formulas here and adjust it as you want now after that if you didn't want to use that feature what you can also do go to the Home tab find and select go to special and in this window right here click conditional formats hit OK and that's going to highlight every cell that has a conditional format applied to it so you can see that within the worksheet so it's really cool and if you wanted to you could leave them select it go up to conditional formatting hit manage rules select this worksheet or current selection will actually select all the ones right there but if you're not sure if it all selected maybe you clicked another cell in between then and going here you can still click this worksheet and it's nice it creates a more pleasant visual representation of where all your conditional formats are and allows you to edit and change it in a nicer way so that was a lot of conditional formatting here those eight tips took quite a bit of time but I wanted to make sure that I showed you the versatility that is available within the conditional formatting so you don't just have to apply it to this worksheet or a single cell or a range here or there you can apply to tables entire rows you can use different worksheets there's all sorts of things you can do and though I didn't cover every topic in this tutorial there's quite a bit more you can do with conditional formatting I wanted to get you comfortable with doing custom conditional formats making sure the absolute and relative cell references are correct and that your formulas always return true or false so the biggest takeaways I would hope that you get from this tutorial are always use your formulas make your formulas within the spreadsheet first make sure the output true and false test them on your data to make sure you get the desired result then apply them and make sure that you have two absolute and relative cell references correct so you don't get any weird or funky errors so after all that I hope you feel more comfortable in making complex formulas and functions for your conditional formatting rules in Excel I hope you liked the tutorial if it was helpful don't forget to give it a thumbs up and make sure to subscribe and accept notifications so you can see all the new tutorials

Channel: TeachExcel

Views: 159,182

Rating: 4.9095478 out of 5

Keywords: excel training, excel tutorial, excel help, conditional formatting excel, microsoft office, teach excel, microsoft excel help, microsoft excel training, formatting in excel, conditional format, online training, online tutorial

Id: lIqifDg2xfE

Length: 30min 57sec (1857 seconds)

Published: Tue Sep 04 2018