This lesson introduces Conditional Formatting.
So what exactly is Conditional Formatting? Well Conditional Formatting applies a format to a cell based upon the value in the cell.
In this example, I've used Conditional Formatting to change the formatting of a cell to red if the value within it is under 5,000, and green if the value in the cell is over 30,000. And you can see that the three cells that are not less than 5,000 and are not more than 30,000, retain the default formatting. But Conditional Formatting doesn't only work with numbers; it can also work with text and dates.
Let's look at text first of all. In this example I've used Conditional Formatting to highlight all of the text that contains: C-100. You can see that the cell is highlighted wherever C-100 appears in the text within the cell.
And now let's look at an example using dates. In this example, today's date is the 30th of May 2014. And I've used Conditional Formatting to identify today's date in Column B.
Realize that if I open this worksheet tomorrow, I'd see the 31st of May 2014 highlighted. And if we look in Column C, you can see that I can also highlight yesterday's date.
In Column D, tomorrow's date.
In Column E, all of the dates occurring in the last seven days.
And in Column F, all of the dates occurring last week.
So now that you understand what Conditional Formatting is all about, let's open the sample file for this lesson. And for this lesson you need to open the sample file: Sales Report, from your Sample Files folder.
Now this worksheet is very badly formatted and, before we move onto Conditional Formatting, we're going to use some of the skills that you've learned in previous lessons to make this worksheet look really attractive.
The first thing that's wrong with this worksheet is that some of the columns are not wide enough to correctly display the values in the cells. So I'll use the skill you learned in Lesson 3-6 to automatically correct this. I'll select cells A4 to B26, hold down the Control key, and select cells D4 to E13, and with Control still held down, select cells D19 to E26. Then I click Home on the Ribbon and, in the Cells group, Format, and then AutoFit Column Width. And the column widths are all now the correct size.
Now I'll use the skill you learned in Lesson 4-3 to apply the Comma Style to Columns B and E. You can see that the value in cell B5 would look a lot better as 762.60. And the value in B6 would look a lot nicer with a comma after the 33. That's exactly what the Comma Style does, of course. So I'll select Column B, hold down Control and select Column E, then Home on the Ribbon and, in the Number group, the Comma Style. And all of the values now look a lot better.
Now I'll use the skill you learned in Lesson 4-6 to Merge Cells. I'm going to merge the cells in the headers for each range. So I'll select the Header cells first. A3 to B3, hold down Control, D3 to E3, and with Control still held down, D18 to E18. Then Home tab on the Ribbon and, in the Alignment group, the right hand side of the Merge & Center split button, and then Merge Cells. And at the top of each range I now have one large cell instead of two separate cells.
Now let's apply some styles to the cells using the skills you learned in Lesson 4-9. First I think I'll apply the Title style to the Title in cell A1. So I click in cell A1, then Home on the Ribbon, in the Styles group, Cell Styles, and then the Title style.
I'd also like a style for the main range headers. So I'll select cell A3, hold down Control, then D3, and with Control still held down, D18. And I'll bring up the Style Gallery again, that's Home, Styles, Cell Styles.
The main range headers should come from the Title and Headings Row, so let's preview them one-by-one: Heading 1, Heading 2, Heading 3, Heading 4, Title. I think the best would be Heading 2, so I'll select Heading 2.
Now the sub-headers for each range. First I'll select the cells. A4 to B4, hold down Control, D4 to E4, Control still held down, D19 to E19. And once again I'll bring up the Cell Styles Gallery, and this time I think I'll apply the Heading 3 style.
I think just one more style to finish things off for the Totals. So I'll select cells D13 to E13, hold down Control, and A26 to B26, and with Control still held down, D26 to E26. Then it's Home on the Ribbon, in the Styles group, Cell Styles again, to bring up the Gallery, and this time I'll apply the Total style. And the worksheet now look very presentable.
Now let's apply those Conditional Formats. The first task will be to conditionally format the values in Column B so that any country with sales below 5,000 will be highlighted in red. So I'll select the cells in Column B; that's B5 to B25, then Home on the Ribbon, and in the Styles group, Conditional Formatting. And the first option, Highlight Cells Rules. And the Conditional Format that I need is to highlight any country with sales below 5,000. So the rule I need is the Less Than... rule.
A dialog now appears and I can enter the value I need; that's 5,000. And already you can see that the cells with values below 5,000 are highlighted in red. But it's also possible to use other colors. For example, here's a Yellow Fill, and here's a Green Fill, and there's even a custom format option that enables you format the cells in any way it's possible to describe. But we'll stay with the: Light Red Fill with Dark Red Text. And I'll click OK, and the Conditional Format is applied.
Now you're not only restricted to a single conditional format. You can have as many conditional formats as you want within a range. And we're going to now apply another conditional format to the same range, so that any country with sales above 30,000 has a green fill.
I'm going to apply this Conditional Format in a slightly different way. So I'll begin by selecting the range, B5 to B25, and notice that a little icon has popped up at the bottom right corner of the selected range. This is called the Quick Analysis button, and it's a brand new feature for Excel 2013.
Unfortunately the Quick Analysis button is nowhere near as powerful as selecting conditional formats from the Ribbon, but, in this case, we can use the Greater Than conditional format from the Quick Analysis button.
So I'll click the Quick Analysis button and you can see that I have a very limited number of conditional formats that can be applied in this way. But the Greater Than conditional format is available, so I'll click Greater Than, and a very similar dialog pops up to the Less Than dialog. This time it's cells with values greater than 30,000. And this time I'm going to highlight the cells in Green. So I go for the dropdown and Green Fill with Dark Green Text. And I'll click OK to apply the Conditional Format.
And you can see that the two countries that had sales above 30,000 are Austria and the USA.
So once you've applied Conditional Formats how can you remove them? Well I'll now remove the Conditional Formats from the values in Column B. To do that I'll select the values first of all, then Home on the Ribbon, and in the Styles group, Conditional Formatting, and Clear Rules.
Now you can see there's two options. I can either clear the rules only from the selected cells, or from the entire sheet. And in this example it wouldn't make any difference which you selected, but sometimes you'll want to only clear Conditional Format Rules from certain cells on your worksheet. So I'll select: Clear Rules from Selected Cells, and the conditional formats have gone.
All that remains now is to save your work. And I'm going to save with the new name: Sales Report-1, and as usual I'll save to the folder above my Sample Files folder.
I click the Save button and you've now completed Lesson 4-15: Use Simple Conditional Formatting.