Master Conditional Formatting in Excel (The CORRECT Way)

conditional formatting is one of the most powerful features in Excel yet 90% of excel users don't know how to use it to its full capacity that's why in this video I'm going to show you not just how to use it but also the best tips and tricks so you can get the most out of [Music] it first up let's start with the basics and to find conditional formatting you just need to go to the Home tab and click on condition formatting now let's get started with this data set which you can download for free in the video description so let's suppose that our manager is asking us to highlight the revenues that are above 20,000 in green so for this we would select them with control shift down all the way to the bottom there and go to conditional formatting highlight sell rules and let's use the default greater than let's type in 20,000 here and let's say we want them in green as that's positive if they're higher and we'll click on okay that was easy enough but now what if we also want to highlight those that are exactly 20,000 like this one over here so changing it from Just greater than to greater than or equals two for this if we go to conditional formatting again under highlight cell rules you'll notice that we don't have greater than or equals to together as one so we'll actually just go over to manage rules so we set up this rule for the greater than 20,000 as you can see over here but now we want to edit it so under edit rule we're going to switch from greater than to greater than or equals to and hit on okay hit on apply and okay again now you can see that 20,000 is also included we show this to our manager but he doesn't like this color instead he wants to use our branded orange color so for this what we'll do is go to conditional formatting manage rules and we'll change this by going to edit again and this time around we'll click on this format button and here we have a ton of different options to customize but we want to go for the fill color let's say we change that to an orange you can see the preview down below we'll hit on okay and okay again and hit on apply there now you can see what that looks like overall what's awesome about conditional formatting is that it's fully Dynamic so if I go ahead and change this Revenue figure to 10,000 C you'll notice that it's no longer highlighted in Orange as our formula is updating and speaking of formatting another great way to visualize our findings is with templates like the ones hopspot is kindly providing us using the link in the description below you can get multiple Excel chart templates completely for for free in the download you'll find an Excel file with the instructions on using the template alongside the chart types you might need to visualize your data from here you can easily modify the data and the charts will automatically change these templates can either have one column of data or multiple depending on your needs I personally find this template useful for deciding which chart type showcases my data best as it's quite unusual ual to have a template that shows that many chart types at once so if you want to check these out head over to the link in the description below to download them completely for free and level up your Excel game and thank you to HubSpot for sponsoring this video one thing that seems to bother our manager is that we currently only have the highlighted cells in column H instead he would like the entire road to be highlighted when it's supposed to be greater than 20,000 so let's put the goal over here as 20,000 and from here let's go ahead and select this whole area so all of the values with control shift down all the way to the bottom under conditional formatting let's go over and first clear the current rule so we don't get it mixed up and now we'll just create a new one from here we're starting from scratch so we'll click on this last one use a formula to determine which cells to format and let me move this a bit and the formul that we're going to use is that this value over here has to be greater than the 20,000 gold that we set that said we want this to move down so we're just going to take that dollar sign there and delete it from the number so this way we only have it on the H meaning it stays in this column but it can move down then under formatting let's just go ahead and change the fill color to Orange so we can see what it's actually doing and we'll hit on okay there now now you can see we don't have just that one cell selected but rather the whole row as well so I can change this to say 10,000 and you'll notice that it does automatically change as well similarly because the goal we typed it on the side for a cell here I can't change this to say 50,000 and you'll notice how it all changes same thing with say 35,000 next up suppose our manager is asking us to highlight the top 10 Revenue values so we would select that column over here here with control shift Down and Under conditional formatting we can use the other default which is the top bottom rules and go for the top 10 items let's go ahead and put those in green and hit on okay we show this to the manager but he's changed his mind and he's saying just the top five now so we can go back in there and under manage rules instead of the top 10 that we have there we're going to click on edit and it's very simple to change that to a top five for example we can even switch to a bottom or make it a percentage of the range we're happy with this so I'm just going to hit on apply and close now you can see we only have five selected actually we have six selected here I think that might be because these are the same value so if I change this to say 30,000 you'll notice that we now have just five one feature very few people know about is conditional formatting for dates so over here we have some due dates let's suppose that we need to pay our employees by this specific date so what we can do is select the whole area with control shift Down and Under conditional formatting highlight cell rules and click on a date occurring so over here what we can do is say everything that's this month for example we want it highlighted in Red so we don't forget so this month in red same thing with in the the past few days maybe you've yet to pay them or sometime in the future so let's leave it at next month for now and what's nice here is that it's Dynamic currently I'm in February but when I open this Excel file in March the conditional formatting is going to update to then next up moving to some more advanced features over here you can see that we have the percentage change and our manager is asking to add some icons so we can see whether it's up or down so what we can do here is go to conditional formatting and click on icon sets let's suppose that we're going to use um this one right over here so we'll just click on that it all looks okay but when you look closer at the data it doesn't look very good in that some values are positive like these over here but they're still showing even as red or yellow so something needs to change here we'll go to conditional formatting and under manage rules let's go ahead and Ed edit this so we can see what's going on so you can see right now it's based on percent which isn't quite what we would want instead let's suppose we want it by number so when the value is greater than zero let's change this to just greater than 0 is green when it's greater than or equals to zero in number format it's going to be yellow and basically when it's less than zero it's in Red so we're happy with that we'll hit on okay and apply and now we can close out of that so you can see now it makes more sense at zero it's yellow when it's negative it's red and when it's positive it's in green finally there is one last table over here you can see that we have the sales by country for a specific month now let's suppose we want to see these January sales you might think of going to conditional formatting and just going by color scales I personally don't like them too much that's because they're quite overwhelming with the number of colors that they have even the ones that are in a single color I can't really tell the difference there between 105 and 92,000 for example that's why in my opinion a better alternative are data bars so we'll go up over here and now you can see much better exactly how big these numbers are let me click on this one over here one downside of data bars is that they really only apply well for something like a month not so much a trajectory over time that's where our bonus feature comes in which are spark lines so let's take a look at how to use those over here to the side let's go over to insert and click under spark lines to the line there so the data range that we want is this data over here let's suppose all of Spain across the 7even months and we want it to be over here as the result we're okay with that so we'll click on okay now we can see the full trend for Spain we can even drag this down across all of the other countries and the total that's not all though I can go to the spark Line tab and switch from line to a column and even highlight things like the high point as well awesome now that you're proficient in conditional formatting you might want to test your Excel skills with an Excel interview test that you can find for free over here or if you're not quite ready yet you can take our Excel course over here hit the like and the Subscribe and I'll catch you in the next one

Channel: Kenji Explains

Views: 36,022

Keywords: conditional formatting, conditional formatting tutorial, conditional formatting excel, conditional formatting step by step, conditional formatting from scratch, data bars excel, color scales excel, excel icons, icons in excel, highlight cell rules, highlight top 10, conditional formatting dates, conditional formatting tips, conditional formatting tricks, master conditional formatting, learn conditional formatting in excel, ultimate guide conditional formatting, excel tips

Id: ZsjQiZPdIs8

Length: 10min 36sec (636 seconds)

Published: Sun Feb 18 2024