Create Conditional formatting with formulas || 5 Useful Examples

[Music] conditional formatting changes the appearance of cells based on set conditions it makes it easy to visually analyze data particularly large data set there are a number of built-in conditions in excel but they have some limitations however you can easily create your custom conditions with formulas let's take a look at five examples the first step is to select the data excluding the headers go to conditional formatting in the home tab select highlight cell rules format cells that contain the text i can type human resources here or just link it to the cell then choose a style from the options here okay easy right the only issue is that the entire row was not highlighted now to highlight the entire rule you need to create the rule with a formula first i'll clear the rule so we can start afresh go to conditional formatting and select clear rules now the basic rule is that the cell is formatted if the condition is true and if false the range is not formatted so excel evaluates each rule and the rule is triggered when the logical test returns true i'll explain with some dummy formulas is the department equal to the value in c3 i'll press f4 once to make that an absolute reference now this is a logical test and it will return true or false when i press enter it's false now i'll copy the formula down you see conditional formatting will be triggered right here i'll copy the formula across to see how column c was evaluated it's false all through because we are testing the items in headcount instead of department now to correct that all you have to do is lock the column reference being tested column b this is the basic rule to highlight an entire row of data so i'll edit the formula press f4 three times to lock column b now when i copy the formula you can see that the entire row is now true this is where the rule will be triggered so copy the formula so you don't have to type it all over again select the data go to conditional formatting select new rule use the formula to determine which cells to format and paste the formula click format to set the colors let's give it a green fill and make the fonts bold click ok so when i change the department you can see how excel evaluate the cells right here and that's it let's try this for numbers highlight departments with less than 70 employees now it's easier to write the formula and then copy and paste it in the conditional formatting box until you get comfortable with it so c23 remember to lock the column reference is less than c19 make that cell absolute and copy down cool copy the formula select the data go to conditional formatting and paste the formula let's give it a blue fill okay let's test it cool you can delete the dummy formulas if you don't need them but for the purpose of this tutorial i'm going to leave it let's move on to the next example we're going to highlight cells that fall between january 2018 and june 2019 this can be done with the help of the and function the and function will return true when all arguments are true so let's write the formula and d7 remember to lock the column reference is greater than or equal to c3 press f4 to lock that that's the first test comma d7 is less than or equal to e 3. copy the formula down so you see how it's been evaluated by excel if you want to check the result of the formula you can press shift and f3 to view the result of each argument now copy the formula select the data excluding the headers go to conditional formatting and paste the formula let's give this a yellow fill and change the font click okay now i can apply a filter here so i can filter or sort by color let's move on to the next example the goal is to highlight employees that are below 30 off paid hourly this can be done with the all function all returns true when one condition is true so let's write the formula or e7 remember to lock the column reference is less than c3 press f4 to lock that as well that's the first test command f7 is equal to c4 now copy the formula down to see how excel evaluates each one the next step is to copy the formula select the data excluding the headers go to conditional formatting and paste the formula let's give it a feel click ok let's test it easy peasy right let's move on to the next example the goal here is to highlight every third row such that when rows are hidden by a filter it will still highlight the third rule this can be done with the mod and subtotal functions now if you want to highlight the second row convert your data into a table and activate the banded row option you can watch my video on excel tables to learn more about that let's take a look at the mod function the mod function returns the remainder after a number is divided by the divisor for example i want to divide three by two number is three divisor is two so the remainder will be one if i try to divide two by two mod will return 0 because there is no remainder so let's move to the data for the first row it will be mod number will be 1 and divisor will be three when i move to the next row the number will be two and the divisor will be three this is not dynamic so it's not very useful now watch this closely to make the row number dynamic i can use the row function however the row function does not ignore hidden rules now because i want only the visible rules i will use the subtotal function and selects the county function as its first argument the subtotal function ignores hidden rules that are not included in the result of a filter and the counter function counts the number of cells that are not empty now to count the rules all i have to do is lock the first cell reference so it counts from the beginning please note that you can select any column for this exercise i'll copy this down now when i filter the data it counts only the visible items cool now i'll copy the formula and paste it in the mod function as the number argument and the divisor will be the number of rows take a look at the result it's 0 at every third rule now the logical test can be wherever the mod function is equal to zero okay let's move to conditional formatting copy the formula select the data excluding the headers go to conditional formatting and paste the formula let's give it a fill and change the font let's test it cool let's move on to the next example let's write the formula d7 remember to lock the column reference is less than c7 lock the column as well let's copy down now copy the formula select the data excluding the headers go to conditional formatting and paste the formula let's change the font color and make it bold okay now let's quickly add icons to the percentage change select the range go to conditional formatting new rule format style and select icon sets from the drop down you can change the style here go with this the green icon should be displayed when the value is greater than zero change the type to number when the value is zero the orange icon should be displayed if you want nothing displayed you can select no icon and when it's less than 0 the red icon will be displayed you have the option to display icons only if you check this box i'll leave it unchecked okay it looks really good right now if you want to edit any of the rules go back to the conditional formatting window and select manage rules all the rules are here you can go ahead and edit the rule that's all for today i hope you found the video useful if you have any questions please leave it below don't forget to subscribe click the like button see you in the next video bye [Music]

Channel: YouExcel Tutorials

Views: 159

Rating: 5 out of 5

Keywords: #microsoftexcel, #msexcel, #conditionalformatting, #dataanalytics, #youexceltutorials, #exceltraining, #excelbasics, #Excel

Id: X1Wev0-C3s4

Length: 12min 24sec (744 seconds)

Published: Tue Aug 10 2021