3.3 Conditional Formatting

Learning Objectives

You now have all the calculations you need in your CAS 170 Grades spreadsheet. There is a lot of data here. To make it easier to pick out the most important pieces of data, Excel provides Conditional Formatting. The best thing about Conditional Formatting is that it is flexible, applying specified formatting only when certain conditions are met.

Excel places blue bars on top of your values; long blue bars for larger numbers, shorter ones for smaller numbers. This makes it easier to see how well each student did in the class – without having to look at the specific numbers.

Another way to apply Data Bars is to:

 Mac Users: Alternate Steps:

• On the Home tab select Data Bars from the Conditional Formatting tool  .
• From there you can select data bars of different colors and opacities (see Figure 3.19).

It is even more important to highlight the students who are failing in the class. To practice further with Conditional Formatting we will do that in two places, in the Percentages column and on the Letter Grade column.  To start with, we want any F letter grades to be formatted with a light red fill color and dark red text.

1. Select the Letter Grades (R5:R24).
2. On the Home tab, in the Styles group, select Highlight Cell Rules from the Conditional Formatting tool (see Figure 3.20).
3. Select Equal To
4. Fill out the Equal to dialog box so that cells that are equal to: F have Light Red Fill with Dark Red Text (see Figure 3.21).

Let’s try that one more time – to highlight those students who are passing the class.  This time we will use the Pass/Fail text in the Pass/Fail column.  If the text for a student is Pass we want the cell to be formatted with a yellow fill with dark yellow text.

You do not have to use the default styles to make your data stand out. You can set any formatting you want. When you do, it is probably a good idea to include other styling in addition to color. Your spreadsheet might be printed in black and white. You would hate to lose your Conditional formatting.  Now we are going to use conditional formatting to display any Percentages that are less than 60% with red text formatted in bold and italic.

1. Select the Percentage grades (P5:P24).
2. On the Home tab, in the Styles group, select Highlight Cell Rules from the Conditional Formatting tool (see Figure 3.20).
3. Select Less Than
4. Fill out the Less Than dialog box so that cells that are less than .6 will be have conditional formatting. But, instead of using the default red text on a light red fill, press the down arrow at the end of that box and select Custom Format.
5. On the Font tab of the Format Cells dialog box, in the Font style box, select Bold Italic. In the Color box, select Red (see Figure 3.22).
6. Press OK.  Then press OK again.

Conditional Formatting is valuable in that it reflects the current data. It changes to reflect changes in the data. To test this, delete DeShea’s final exam score. (Select N5. Press Delete on your keyboard.) Suddenly, DeShae is failing the course and the Conditional Formatting reflects that. This is a little unfair to DeShae – who has worked so hard this quarter. Let’s give him back his grade. Press CTRL Z (Undo). His test score reappears and the Conditional formatting reflects that as well.

Making Changes

What if you have made a mistake with your Conditional Formatting? Or, you want to delete it altogether? You can use the Conditional Formatting Manage Rules tool. In our example, we want to remove the conditional formatting rule that formats the Pass text with yellow.  We are also going to modify the minimum passing percentage for the conditional formatting rule that is applied to the percentages.

1. On the Home Tab, in the Styles Group, select Manage Rules at the very bottom of the Conditional Formatting drop-down list.
2. Show formatting rules for: This Worksheet (see Figure 3.23).
3. We don’t really need to highlight the students who are passing the class, so select that rule in the Rules Manager and press the Delete Rule button. Mac Users should click the minus symbol – at bottom left corner to delete the rule.
   

   

   In a previous exercise (the IF function), we decided that students were failing if they got a percentage score of less than 70%, so the Conditional Formatting rule in the Percentage column needs repair.

4. Select the rule that reads Cell Value <0.6.
5. Select the Edit Rule button, and change the .6 to .7 (see Figure 3.24).
6. Click OK (or Apply) twice. Double check that your completed workbook matches Figure 3.25.

Setting The Print Area

Before you consider this workbook finished, you need to prepare it for printing.  The first thing you will do is set the Print Area so that the table of Letter Grades in A27:B32 does not print.

Next you will preview the worksheet in Print Preview to check that the print area setting worked, as well as make sure it is printing on one page.