Define conditional formats

- When you analyze and summarize business data, it's not always easy to tell how one value compares to another or how a value compares to a benchmark. In this movie, I will show you how to create additional formats that change the appearance of data based on a value comparison to rules, such as key performance indicators. My sample file is the conditional Excel workbook. You can find that in the chapter one folder of the exercise files collection. This workbook contains a single worksheet, and on it I have a summary of data.

I have values for each of two types of hotel room in my hotel chain. Those are for Cambridge and Piccadilly. And you can see that I have data for the years 2014 and 2015. At the top, I have my monthly revenue goals and my yearly revenue goals for both rooms. And we'll get to those in a couple of minutes. First, I will define a rule-based conditional format. And I'll do that for cells C5 and C6 and C9 and C10.

Those cells contain my monthly data. So I will select cells C5 and C6 just by clicking and dragging. And then I will hold down the control key and select C9 and C10. So C5, 6, 9, and 10 are all selected at the moment. Now on the home tab, I will go to the styles group, click the conditional formatting button, point to the top item of the dropdown, which is highlight cell's rules.

And let's say that I want to create a conditional formatting rule that displays a color in the background of any cell that contains more than $1,200,000. For that, I would click greater than. Then in the greater than dialogue box that appears, I would edit the left-hand value. It currently says 1,317,470. But I want to edit that to 1,200,000.

And you can see that the worksheet is being updated in real time over on the left. So currently, I have 1,200,000 as my greater than rule. And Piccadilly for 2014 and both 2015 values are formatted. Let's say that I want to change the formatting that is applied if the rule is true. So I'll go over to the with box, or the format box, really. And then click the list box's down arrow. And I see a number of built-in formats.

Or I can click custom format. Doing so displays the format cells dialogue box. And I can change pretty much any aspect of the cell format that I want. In this case, I'll keep it simple. I'll just go to fill tab. And I will select at fill color. So let's say that I'll go for a light green. This might not be bright enough to show up for you, but I want the numbers, which are black, to show up well against this background color.

So I'll just go ahead with it. I'll click okay. And click okay again with my custom format. Then click away from the selected cells. And you can see the format. And I'll press control + z to undo the format. Another way to create a conditional format is to base it on the result of a formula. Remember that I have my month goal and year goal here at the top of the worksheet. Let's say that I want to compare my monthly goal to the values in C5, C6, C9 and C10.

First, I will create the rule for cell C5. So C5's selected. I'll go to home tab, click conditional formatting. And this time, I will click new rule. In the new formatting rule dialogue box, I'll see the bottom type of rule, which is user formula, so I'll click that. Now I can edit the rule description. So ill click in the format values for this formula is true text box.

And enter my formula. In Excel, formulas always start with an equal sign. So I'll type that. And now I want to compare the value in C5 to the value in C1. I want the conditional format to be applied if the value in C5 is greater than or equal to the value in C1. So I will type C5. And note that this cell reference is relative. That means when I copy the format to other cells, this cell reference will change.

So I have C5 greater than or equal to the value in C1. That reference shouldn't change. The comparison value will always be in cell C1. So I'll make that an absolute reference. So I'll type dollar sign C, dollar sign one. Yep, that looks good. And now I can define my format. So I'll click the format button. And this time, I will go one shade darker. If you're not on the fill tab, click it now. And then I'll click the olive green square, the second from the bottom, and click okay.

So now I have my format, and I'll click okay again. And it looks good because I did not highlight or add the fill to cell C5. That's because the value in C5 is less than the value in C1. Now I want to copy the format from C5 to C6. So I will click cell C5, if it weren't already. Then on the hub tab, I'll click the format painter. And then click cell C6.

And you can see that that applies the conditional format, and it is correct. It displays the cell with a green background because the value is greater than the value in C1. And I can do the same thing for cells C9 and C10. So I'll click the format painter again. I have cell C6, which has my conditional format applied, so I can do it this way. Format painter, and then select cells C9 and C10.

And they are also correctly highlighted. The one great thing about using a formula-based value is that if I change my target by editing the value in C1, then the conditional format will be updated automatically. So, I will click cell C1, and instead of 1,200,000, I will type 1,250,000. So 1, 250, and then value that with three zeros. And enter.

And you can see that the format has been applied based on the new value. C9 and C10 are both over 1.25 million, so they are highlighted with a green background. And C5 and C6 are not, because the numbers there are less than the value in C1. These types of conditional formats are extremely powerful. If you use a formula-based conditional format, make sure you get the copying right and you use relative and absolute references as required.