In this workout, I want to apply conditional formatting that conforms to a rule or formula that I'm going to input. What I want is I want to highlight the names of an employee when the number of units they've sold was above 70 and when the sale amount was above 200. So I firstly select the cells I want to be highlighted and I now go to conditional formatting. The shortcut is Alt H + L. I want a new rule, so that's N and I want to use a formula to determine which cells to format. I now have to input my formula. So I'm gonna press equals, and then the up arrow so I can see my cells I want to say equals and open bracket, G5.

I want to unlock that. So I press F4 a couple of times until the dollar signs have gone away. I wanna say when G5 is greater than 70, comma and added in my second rule, H5, and I unlock that is greater than 200.

So if both of those rules are correct, i.e. G5 is above 70, and H5 is above 200, then that word Hallagan in D5 should be formatted in a certain way. So I press enter and I now click on Format, and I want it to be formatted in green. I want the cell highlighted in green so I press OK, OK. And I can see that there have been three instances when this has happened in D5, D8, and D12.

I want to do a second example. Let's imagine we have scenarios. We have three scenarios: best, base and worst case. And what we're going to do, we're going to offer the ability to choose between these. I could have a dropdown list here but I'm just going to type it in manually. What I'd like to do is when the scenario picker has been chosen as best, I want these three cells to be highlighted in green to make it really obvious to users what's happening. I.e., the number 10,000 is going to be used. So I need to select all of my output cells and I hit Alt + H + L to go to my conditional formatting, and then N for new rule. I then click on use a formula to determine and I now need to input my formula. So I'm gonna say equals B19 but I wanna unlock the 19 because I do want this to go down. I want to leave the B locked.

So I want to say when B19 equals D17, I'll leave that locked. So I press enter. I now change my formatting again to green and that will highlight the cells in green if the rule is followed. So we press OK and OK again. And I can see that when best is written in D17, then best and the number 10,000 is highlighted. Let's change best to base. Fantastic. It's moved down to the baseline. And if we change it to worst, it moves down again to the worst line.