Style and Conditional Formatting

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

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Related Scripting

Related Posts Plugin for WordPress, Blogger...

Disqus for Functions