Formatting With Style

Introduction

In the previous lesson, we reviewed various techniques of formatting cells. Microsoft Excel provides a faster and customized technique used to to apply a particular and user-created format that can be applied to a series of cells. As effective as that technique is, it has its own limitations. For example, it cannot create merge-and-center-related styles. Therefore, you should already remember the basic means of cells formatting, which include using either the Formatting toolbar or the Format Cells dialog box. As a reminder, we will review them here.

Practical Learning: Formatting With Styles

1. Start Microsoft Excel and fill out Sheet1 as follows:
    
   
2. Once you have finished, press Ctrl + A to select the whole worksheet and press Ctrl + C to copy
3. Click Sheet2, press Ctrl + Home, and press Ctrl + V to paste
4. Save the workbook as Styles1
5. In Sheet2, right-click cell B2 and click Format Cells...
6. On the Format Cells dialog box, click the Font property page
7. In the Font list, select Garamond. On the Font Style list, click Bold. In the Size list, click 12. Click the arrow of the Color combo box and select Blue
    
   
8. Click OK on the Format Cells dialog box
9. Using the Formatting toolbar, change the font of INDUSTRY DEPARTMENT to Times New Roman. Change its font size to 11 and make it bold
10. Again, using the Formatting toolbar, Set the following cells contents to Bold : SEMESTER, GRADE, Student #, and Full Name
11. Click cell B27 to select it, then make it bold . While cell B27 is still selected, on the main menu, click Format -> Column -> AutoFit Selection
12. Select cells D7:E7. Right-click in the selection and click Format Cells...
13. On the Format Cells dialog, click the Alignment property page. On the Horizontal combo box, select Center. On the Vertical combo box, select Center. In the Text Control section, click the Merge Cells check box
14. Click OK
15. Select cells B7:B8 and press F4 to repeat the last action
16. Select cells I7:J8 and press F4
17. Select cells B7:J8 and press Ctrl + 1 to open the Format Cells dialog box
18. Click the Font property page. In the Font Style combo box, click Bold. In the Color combo box, select White (8th column - 5th row)
19. Click the Border tab. In the Line Style section, click the thin line (1st column - 7th row). In the Color combo box, select White. In the Presets section, click the Inside button
20. Click the Patterns property page and, in the Color section, click Gray-50% (8th column - 2nd row)
21. Click OK
22. In the new gray section, click each of the cells that were not centered and, using the Center button of the Formatting toolbar, center them:
     
    
23. Save the workbook

Cells Formatting With Styles

A formatting style is a set of font, font size, color, and patterns designed to make a worksheet or one of its sections look good. Microsoft Excel is equipped with categories of styles. You can use those ones or create your own.

When applying a style, you decide to format various cells at the same time using a category of style that has been predefined. Therefore, you apply such a style to a cell or range of cells. By default, when you are typing data in a worksheet, you are already using a predetermined style made of a certain font (Arial), a font size (10), a border, and background, etc. You can modify this style or create a new one.

You select, create, or define a style using the Style dialog. The Style dialog is available from the Format menu

Practical Learning: Formatting With Styles

1. In Sheet2, press Ctrl + A and press Ctrl + C to copy the whole worksheet
2. Click Sheet3. Press Ctrl + Home and press Ctrl + V to paste
3. In Sheet3, click cell B9 to give it focus.
4. On the main menu, click Format -> Style…
5. Type Odd Grades to replace the style name and click Modify…
6. On the Format Cells dialog, click the Font tab. Change the font to Tahoma, Bold, size 10.
7. Click the Border property page
8. In the Line Style section, click the thin line (1st column, 7th row)
9. Click the Color combo box and select Indigo (7th column, 1st row)
    
   
10. In the Border section, click the right border
11. Still in the Border property page, in the Line Style section, click the double line (2nd column, 7th row)
12. In the Border section, click the left border
     
    
13. On the Format Cells dialog, click OK
14. On the Style dialog, click OK.
15. Click cell B10 to give it focus.
16. On the main menu, click Format -> Style…
17. Type Even Grades to replace the style name and click Modify…
18. On the Format Cells dialog box, in the Border property page, in the Line Style section, click the thin line (1st column, 6th row). Click the Color combo box and select Indigo (7th column, 1st row). In the Border section, click the right border.
    In the Line Style section, click the dotted line, just under None. In the Border section, click the top and the bottom borders.
    In the Line Style section, click the double line. In the Border section, click the left border
     
    
19. Still in the Format Cells dialog box, click the Font property page. Set the font to Tahoma, Bold, 10
20. Click the Patterns property page. In the Cell Shading section, click Tan (2nd column, 5th row)
21. On the Format Cells dialog, click OK
22. On the Style dialog, click OK
23. To apply an existing or already created style, click cell B11 to make it active.
24. Press and hold Ctrl. Click cells B13, B15, B17, B18, B19, B21, B23, B25, B27, B29, B31, B32, B33, and B35. Release Ctrl.
25. On the main menu, click Format -> Style…
26. On the Style dialog, click the arrow of the Style Name combo box and select Odd Grades, and click OK.
27. Click cell B12 to give it focus.
28. Press and hold Ctrl. Click cells B14, B16, B20, B22, B24, B26, B28, B30, and B34. Release Ctrl.
29. On the main menu, click Format -> Style…
30. On the Style dialog, click the arrow of the Style Name combo box and select Even Grades, and click OK
31. Press Ctrl + Home to see the result
     
    
32. Start a new workbook and, in Sheet1, enter the following data
     
    
33. Save the workbook as Red Oak High School
34. In Sheet1, press Ctrl + A and Ctrl + C
35. Click Sheet2. Press Ctrl + Home, then press Enter

Conditional Formatting

Conditional Formatting allows you to define and apply formatting to some cells, text, and numbers based on criteria that you set. For example, you can format a time sheet to point out whenever an employee gets overtime. You can also use it to track the best sales people in a company by setting a quota that makes a cell range particular.

To use, define, and apply conditional formatting, first select the cells that will be considered. On the main menu, click Format -> Conditional Formatting... You can use any criteria of your choice. The formatting could be applied to cells' values or a particular formula.

Practical Learning: Conditionally Format Cells Contents

1. In Sheet2 of the Red Oak High School1 workbook, select cells C3:G10.
2. On the main menu, click Format -> Conditional Formatting...
3. We are going to set as bad any grade that is under 12.50, then we will make it obvious on the worksheet.
   In the Condition 1 combo box, make sure the combo box displays Cell Value Is. 
4. Click the arrow of the second combo box and select Less Than. Press Tab.
5. In the right combo box, type 12.50
6. Click the Format... button. Set the Font Style to Bold. Click the Color combo box and select Red (1st column, 3rd row).
7. In the Format Cells dialog box, click OK
    
   
8. In the Conditional Formatting dialog box, click OK.
9. Press Ctrl + Home to see the result
10. Now we will set as excellent any grade that is above 15 and point it out. We will add the second conditional formatting to the first.
    Select cells C3:G10
11. On the main menu, click Format -> Conditional Formatting...
12. In the Condition 1, make sure the combo box displays Cell Value Is.
13. Click the Format... button. In the Font Style list, click Regular. In the Format Cells dialog box, click OK.
14. In the Conditional Formatting dialog, click the Add >> button.
15. Make sure that the Condition 2's combo box displays Cell Value Is.
16. Click the combo box on the right of Condition 2 and select Greater Than. Press Tab and type 15.
17. Click the bottom Format... button.
18. In the Format Cells dialog box, set the Font Style to Bold. Click the Color combo box and select Green (4th column, 2nd row).
19. In the Format Cells dialog box, click the OK
     
    
20. In the Conditional Formatting dialog box, click OK
21. Press Ctrl + Home to see the result:
     
    
22. Save the workbook