MS Excel - Advanced Conditional Formatting

in this video we'll be looking for some advanced conditional formatting let's see that we have some three examples given I have the first example that is highlighting one column value I have some data here given that is top products that is product code quantity and price what is happening is in this column I have products like mobile camera laptop cosmetics if I type your camera in this column wherever the camera appears that should be highlighted that is what it will highlight one column values so now what I'll do is if I want to highlight one column values I'll select this entire column I will not select the entire record in dev data I'll select only one column and I'll go to this Home tab new rule and there are many options available but right now what formula will be working it I'll go to this user formula to determine which cells to format now if you see carefully I want to compare this column values with with this particular cell so what formula I'll be using it and we used ring is equal to that is dollar a five I'll remove the dollar sign because it's a relative reference equal to and I want to compare with this fixed cell so this is the only function I have to use and I'll just go to this format and change the format here and I'll click on okay so now this is the formula which I have used and I'll click on OK so wherever this value satisfies when it is true that time only this color will be applied so I'll click on OK so if you see the camera is highlighted now if I change the option here if I change it to let's say mobile Mobile enter so you see mobile it is appearing three times let us change see for type here let's say I'll type your laptop and so laptop it's appearing twice if I have if I type here perfumes enter so perfumes appearing twice so this method you can highlight the one column values next one is similarly if I let's a bit more advanced what is that if I get something let's say shoes so the entire record for the shoes should get selected so if you see highlight entire record so if you want to highlight entire record if you want to put a condition formatting for an entire record you have to select the entire database how do you select it you use your mouse and you can select this entire database or else second option is you just keep your cursor and yep you can just select this the entire database next option is you can go to the Home tab and you can go to this new rule use a formula to determine now what I'll do is I want to compare this cell fix the cell with this column values I do not want to compare with other values so I'll use a function here equal to this cell I'll press f4 f4 so what I've done is I have locked to the column I have records which I'm open it I want to compare with this cell that is f-16 and I'll change the format I will choose some other Ella color format okay so whenever this condition is satisfied the entire record will be highlighted I'll click on OK you see if I if I select a shoes the shoes is getting highlighted the entire record if I type here mobile mobile record is highlighted let's see one more camera it's getting highlighted so this is working fine now highlighting entire record this was the formula I have written here for your understanding next is if I want to highlight column and row at the same time for example if you see I have three countries names which are the column headings if I type your China this column should get highlighted and second one is if I type your laptop this row should get highlighted the laptop Row is 32 row ok if you remember we have seen this highlighting the row in the previous example now something new in this column how to how do I highlight similarly select the area which you want to highlight so I'll select this area which I want to highlight and I'll go to this Home tab conditional formatting new rule and I'll go to this use a formula to do mine select the same option which have selected earlier I'll press f4 equal to change the format okay no change in the previous function formula and this one because I've used the same formula here for highlighting records and I'll click on OK so if it comes laptop the laptop rows should get highlighted which is perfectly fine there's something new which I'm showing you how to highlight the columns similarly right now the data is already selected if you see and I will go to this condition formatting click on new rule now I want to compare this cell with this column heading so what I'll do is I'll click on this user formula to determine and I will select this F for ya this is fine because B column I have opened that is the column I've opened and row I have highlighted that is fixed I have done it so C 26b 26b 26b there and I want to compare this with a fixed cell that is this cell number that is G 27 and I'll click on I'll change the format the format would be this one let's say ello color and I click on okey so earlier if you remember that was column higher fixed now it is row fixed and I'll click on OK so if you see if I select I in China the entire column gets highlighted if I select here Singapore Singapore should get highlighted so this way you can use highlighting columns and rows at the same time also which looks beautiful beautiful so I hope you have understood how do you highlight the one column values how do you highlight the entire records and also how do you record highlight the columns and rows at the same time so I hope you understood that's all for this video

Channel: Tutorialspoint

Views: 505,907

Keywords: MS Excel, Excel, Microsoft, MS, MS Excel - Advanced Conditional Formatting, Advanced Conditional Formatting

Id: AYSW7N3Sh0c

Length: 5min 20sec (320 seconds)

Published: Mon Jan 15 2018