Hi, I'm going to share an amazing ninja trick with respect to conditional formatting, and that will make your colleagues envy. We will see how a particular formula based Conditional Formatting can help us prepare this dashboard. We have a data in the vertically placed information in gummy, and that talks about branch one till 10 and four quarters and numbers how have they performed in terms of profitability. Now, they are two sales. The first one allows you to choose the branch the second one allows you to choose the quarter. Now we had seen in one of our earlier videos how v lookup match how we look up match helps you populate this answer with says branch eight quarter 306.

So if you want to know how the formula is built, what is the logic behind it, please refer our earlier videos. But right now my objective is that whenever you are choosing a certain selection from the drop down, it not only gives you the number using the lookup match, but also highlights that cell. Now, there are two strategies. Now one strategy that I follow if I choose branch one, and it says quarter 406 all the answer is 992. But the erroneous technique highlights all these two cells which contain 992. So, we will see what not to do and what to do, how to prepare this dashboard.

Let me go to exercise sheet one. Let me make sure the view of the screen is perfect. Now, once having done so, let me choose the entire block. First let me show you what not to do. After having chosen the data. I go to Conditional Formatting new rule and the second option in which I I specify the format only cells with cell value cell value equal to equal to what equal to the cell which two we look up match is giving you that answer.

Let me give a format color Okay. Now the logic means that amongst the given set of values, variable T 63 appears it will color the cells. And that's where the problem lies. You do not want to color all the instances of 363 you only want to color the ones which are falling under branch three quarter 206. This is something what we will not do. So first I will clear all the rules from the entire sheet and then guide you what to do next.

Let me choose the data again. This time we'll be applying formula based conditional formatting. Let us go to conditional formatting, new rule. And the last option which allows you to post a formula the formula should be such that the answer must be arrived in terms of true and false. Now since there are two condition conditions based on two input values, which helps decide what is the location of the cell that needs to be colored, I will be using the formula and starting with the first question, question number one. Since I choose seven through onwards, I will choose branch one cell Now you would want that this should be compared, all these values should be compared with the first input.

So the column should not shift, but rows can move down. So that branch 1234567, all of them can be combined. So take a few seconds and think, what dollar combination Should I apply here dollar a seven $8, seven or dollar $1? Seven. Keep in mind that you have to move this selection down internally for conditional formatting to be able to apply and compare this particular format for conditional formatting to be able to work correctly. Yep, you would have guessed correctly by now.

So I press f4 f4. Ensuring dollar f7 is that equal to ensure that you put the equal sign equal equal what the first input cell that has to be fully fixed, I don't want the cell to move anywhere around the user input with respect to branch name is only to be entered here. That was my first condition. Let me put a comma and this time Let me choose the first quarter, why am I choosing the first quarter? Because your selection in terms of column had started from column B. And hence, I'm not choosing some other columns, quarter name, quarter 106.

How would I fix this so that this moves sideways, but not down, the selection of the first blank cell, or 106 is able to move sideways. So b2c, when will that happen? If you keep this B dollar six, this is one of the most crucial points of the entire exercise B dollar six is that equal to equal to the second input cell which I keep is fixed, I close the bracket, I could format and apply my desired column. Okay. And I press OK. Instantly, you see the result which you had wanted, so it's not any 363 that is getting highlighted. It's only that 363 or the sell getting highlighted, which falls into quarter 206 and branch number three If you want to do a post mortem analysis then please go back to conditional formatting, Manage Rules.

First you need to tell Excel that you want to show formatting rules for this particular worksheet. And then after having found so, then you can say, edit rule. So there you go. This was Conditional Formatting using formula