Subtitles of the Movie

Conditional formatting has been improved in Excel 2007. You will find the Conditional Formatting command here on your Ribbon in the Home tab and the Styles group, you will find the Conditional Formatting command. When I select that it will expand down to a menu that gives you even more options to choose from. Let's go ahead and apply some conditional formatting to the following example that you see here on my screen. The first thing that you need to do with any format that you apply is designate the data that you want to apply the format to. When I highlight this, I'm indicating to Excel that this is where I want to apply the format. I'll come up here to my Conditional Formatting option, select that, and then I'm going to go ahead and apply Data Bars. As you hover over these various options you'll see that reflected in the data on your worksheet. I'm going to go ahead and choose the purple data bar and when I select that by clicking it, it is applied to my worksheet. Now another nice feature about conditional formatting is that you can apply more than one rule. So this visually gives me a good indication of how sales are doing and which product is selling the most, as well as which product is selling the least, but I can also highlight products that are selling over a certain number, so again, I'll go ahead and indicate the data that I'd like to apply the format to, then come up here to my conditional formatting option, select that, and I'm going to choose the very first menu choice of Highlight Cells Rules, and here I want to choose Greater Than; notice you can indicate less than, between, equal to, text that contains, a date occurring, or duplicate values, as well as more rules which allows you to customize the rules that you are applying. I want to just choose Greater Than because I want to know what products are selling greater than 1,000 dollars. I'll go ahead and select this and in the Greater Than dialog box that appears I'll go ahead and type in 1,000 dollars into this first field. I want it to really contrast with the other format that I have applied so I'm going to choose a green fill with dark green text and when I click that and then say OK you'll see that now it's very easy to see which products are selling over 1,000 dollars. This is a real nice feature. It really helps your data stand out visually. Conditional Formatting also helps you easily analyze and interpret your data. Let's go ahead and apply some conditional formatting to our Monthly Sales Worksheet that we created earlier in this Chapter. Open that up, or if you would like to start with a file that reflects the development of this course up to this point, open up 0715 Start file. This is our Monthly Sales Worksheet that we've been working on and what we want to do is apply conditional formatting to show which products have sold at least 10 units or more on a weekly basis. So we're going to look at our Quantity column here in Column B, and again, the first thing that you want to do to indicate to Excel that this is the data that you want to apply the format to you need to highlight it. So go ahead and highlight this column down to B36 and then come up here to your Ribbon to your Home tab and the Styles group and choose your Conditional Formatting option. Now we want to indicate which products are selling over 10 units. So choose your Highlight Cells Rules option, and the Greater Than choice, select that, and it will open up again the Greater Than dialog box, and go ahead and type in 10 and then choose how you would like it to display. I'm going to choose a yellow fill with dark yellow text and then click OK, and notice that now all products that have sold over 10 units are very easily identified and displayed, visually displayed, on your worksheet; nice feature. Please save this worksheet and let's move on to the next movie.