Hi, we see one of the features of conditional formatting, where we are able to color the cell or the entire rows based on a certain user input. And let me give an example. In this case, the division names I'm putting there in the yellow cell, if I write h, f d, notice what is going to happen as I press Enter or click outside all the rules within the data, which is a part of the HFT division is getting colored. Now, this could be very useful for your EMI support when you have the control over the look and feel of the report. Now, we see how to make that happen. I will show you a way which is slightly easy, but will not cover the entire group of cells for the same row, only one cell instead.
On the other hand, if you go one step ahead, using formula based condition formatting the entire who will also be covered. So quickly, let me put my cursor in the exercise sheet one Each cell which contains any of the cylinder is my, I press Shift control, right, it quickly chooses the last cell, Shift Ctrl down. And since I want to go to the top of the data to see the header, I would want to press Ctrl backspace key. Now this is not compulsory, this is only for your convenience. Once I've chosen the data in which you want to apply the color, I will go to conditional formatting, go to new rule, within which let me target the last option which says use a formula to determine which cells to format. And the best part of this option is you can write your custom formula, you can write your own customized formula in this box, the only condition is the output of that formula should be in true or false.
So if it happens to be true, only then that particular format which you have decided will be applied. So now let's see how to build this formula which will enable me to color the entire rows based on the predefined user input. Write a formula equal to. And then I choose that one cell, one cell from the data itself. I want to compare this cell, in fact, all the cells below it with the user input. Now imagine if I were to compare this, I would want this to move, go down, go down, go down, and so on so forth for the individual comparison with the user input.
And when does that happen? Certainly not when I'm applying dollar c dollar eight, because that indicates it's fixing and locking the entire range. So I'm going to press f4 f4 once again, such that I get an indicator of dollar c eight, I would want that a to go to nine through nine to 10 through and at the same time, column C should hold to its current position and not go to column D. So is dollar c eight equal to equal to the cell which contains the user input? Now you would have guessed correctly, yes, I am not going to remove the dollar from This l dollar B dollar three Why? Because come what may all the user input that is there for comparison must be done from this particular cell. So be very careful, make sure it is dollar c eight, and why eight because my selection has started from a through.
In fact, I've seen people, they press Ctrl A, which also selects the seventh row, but by writing the formula, they put eight. Now, that is inconsistent. So make sure from wherever whichever row you are choosing, it should be consistent with the formula. In this case, since my selection is from the A through it says dollar c eight. Now, let me quickly apply the fill color or the format. Go to fill option, apply your favorite color, okay.
And as I press OK, notice immediately our ad has been colored. In fact, just to test whether my approach has been correct Let me press h f d and duck. Hmm, perfect. So this is how you can apply a basic formula in the condition formatting, be careful about the dollars and that will all lead you to a very wonderful EMI support. We'll see more of these tricks in the next few videos.