Jan's Working with Numbers

Step-by-Step: Conditional Formatting

---

What you will learn:

to create conditional formatting to create conditional formatting by formula to test conditional formatting to change the theme to see how it affects conditional formatting to use the sheet to find cells that have conditional formatting

---

Start with: trips35-Lastname-Firstname.xlsx(saved in previous lesson)

Format Cells: Conditional Formatting

You will use conditional formatting to give the TotalPoints and Bonus cells different formatting when there is a bonus, and leave the formatting you already applied when there is no bonus due.

1. Open trips35-Lastname-Firstname.xlsx and select the Bonus sheet, if necessary.
   
2. Save as  trips36-Lastname-Firstname.xlsx  in the excel project5 folder of your Class disk.
3. Select cell TotalPoints (D11) and from the Home ribbon tab, click the button Conditional Formatting
   A drop list of pre-designed rules appears.
4. Hover over Highlight Cells Rules to expand the choices.
5. Click on Less Than...
   The Less Than dialog appears with the current value of D11 filled in.
    
6. Set Condition: Set the value to 50 and leave the formatting at the default, Light Red Fill with Dark Red Text.
7. Click OK again to close the dialog.
   When this condition is not met, you will see the original formatting.
8. Save.
   [trips36-Lastname-Firstname.xlsx] 
9. Test your new rule by changing the Tahiti trips tickets to 0 and press ENTER.
   The total number of points changes to 47 and the cell gets the new conditional formatting.
10. Undo the value change (but not the conditional formatting).
      
    Copy conditional formatting: You can copy conditional formatting with Format Painter or with Paste Special > Formats, BUT you get ALL the formatting, not just the conditional formatting rule. Sometimes it's worth doing even if you have to repair other parts of the formatting.

---

Conditional Formatting by Formula

What you really need for this sheet is to format the cell Bonus based on the value of a different cell, TotalPoints. That requires creating a formula and doing a custom format.

The new drop list for conditional formatting is cool and useful but it's made it less than obvious how to do a rule like this one!

1. While cell G9 Bonus is selected, open the Conditional Formatting list again and select New Rule...

   The New Formatting Rule dialog opens.

2. Click on the type Use a formula to determine which cells to format.

3. In the text box at the bottom, type =TotalPoints<50

   This is the formula. Looks odd, doesn't it!

   You must start with = and then you can write a condition that uses values from other cells.

4. Click on the Format button.
   The Format Cells dialog appears.

5. On the Font tab, change the Color to the darkest version of Accent 2, which is either a red or an orange, depending on your version of Office.

6. On the Fill tab, change the Color to the lightest version of Accent 2.
   These colors are not exactly like the ones in the pre-designed formatting you used for the Bonus cell. They are the closest choices that use Theme colors.
   

7. Save.
   [trips36-Lastname-Firstname.xlsx] 

---

Test New Conditional Formatting

1. Test < 50: Change some of the trip numbers to change the TotalPoints to less than 50.
    
2. Test = 50: Use numbers that will give exactly 50 points.
   Does the formatting change as planned in both cases? (This is super cool!)
    
3. Test > 50: Put in numbers that make TotalPoints greater than 50
   
4. Undo your changes.
    

---

Change Theme

It's a good idea to see what effect changing the document theme will have on your carefully selected conditional formatting. The formatting you applied to the Bonus cell will change since you use Theme colors. But the formatting for the TotalPoints cell was from the pre-designed choices. What will happen to it? Let's go look!

1. If necessary, open the Clipboard pane again at the left of the window.
   This will move the table over so that the themes gallery does not completely cover it.
   
2. On the Page Layout tab, open the Themes gallery and hover over various themes.
   Look at what happens to TotalPoints and Bonus as Live Preview shows the effects of different themes.
3. Change the values for trips to make TotalPoints less than 50.
   

4. Repeat the Live Preview of different themes.
   What happens to TotalPoints and Bonus? They do not change in the same way. The font changes for both but the TotalPoints cell does not change its font color or background color when you change the theme. All of that changes in the Bonus cell.

   You could have created a custom format for TotalPoints for the Less Than dialog instead of accepting Light Red Fill on Dark Red Text. In the drop list for that dialog, Custom Format opens the Format Cells dialog.
   

5. Undo your changes of the values.

---

Use the Bonus Calculator 

1. Header: There should already be a header to the Bonus sheet - your name and the date in the left section, the file name - sheet name in the second section, Excel Project 5 in the right section.
2. Gardner:  With Gardner's data back in place, show the formulas. (CTRL + ` or on the Formulas tab, Show Formulas button.) Widen column G as needed to show complete formulas.
   Print the sheet on one page in Landscape orientation. Do not print the comments.
   After printing, hide the formulas and resize column G to its original width, 77 pixels. 
    

3. Heinz:   Change the name in cell G1 to Heinz.
   Fill in the trip numbers for Heinz.
   (Use the values on the sheet Specials like you did for Gardner to figure out what he sold.)

   Heinz does not qualify for the bonus.

   Print page 1 in Portrait orientation, without formulas.
   Do not print the comments.

4. Save.
   [trips36-Lastname-Firstname.xlsx] 
   

---

Find Cells with Conditional Formatting

How can you tell later which cells have conditional formatting? Many cells might have your conditional formatting applied, but not show any format changes because the conditions are not met at this time.

There is a way to highlight these cells, but it will take a few clicks.

Experiment: Find ALL cells with conditional formatting

• Select any cell on the sheet.
• On the Home ribbon tab, at the far right, click the button Find & Select.
  A menu appears.

• Click on Conditional Formatting.
  All cells with conditional formatting are highlighted. They may be hard to spot.
  

  The highlighting changes the colors, but is it enough that you notice the difference? Look for highlighted column and row headers.

  Remember that the selected cell has the selection border but not the highlight.
• Use the Find & Select menu to find all of the cells with formulas (which include the concatenation formulas), all of the cells with comments, all of the cells with data validation, all of the cells with constants. How easy is it to see which cells are selected? Remember to look at the row and columns headings!
  

Experiment: Find cells with the SAME conditional formatting

• Select a cell with the formatting you want to match.
• On the Home ribbon tab, at the far right, click the button Find & Select.
  A menu appears.
• Select Go To Special...
  The Go To Special dialog appears.
• Click on the radio button for Conditional formatting AND the radio button for Same under Data Validation.
• Click on OK.
   
  You cannot try this one unless you add a conditional format to several cells. So far the sheet has two different conditional formats.

Close trips36.xls when you are done.