Chapter Three: Formatting Worksheets

Lesson 1: Enhancing Worksheet Appearance

Changing Character Fonts, Sizes, Styles, and Colors

Excel can use different styles on a worksheet. It can be bold, italic, or underlined.

Use any of these methods to change the appearance of your data:

Using the Toolbar:

The toolbar gives you quick access to frequently used commands. To use a Formatting tool, do the following:

1. Select the cell or the cell range you want to format.

2. Click the tool in the formatting toolbar you want to use.

   

You can use some of the additional formatting tools that you can add to any toolbar. To see these tools, do the following:

1. Click the Toolbar options button.

2. Point to Add or Remove buttons, then Formatting.

3. Click the button you want to add in the toolbar.

   

Formatting Characters with the Menu

To change the font size or font style of characters, follow these steps:

1. Select the cell, range, or multiple ranges.

2. Click Cells in the Format menu or press Ctrl+1. The Format Cells dialog box appears. Click the Font tab to display the dialog box.

   • Font List - allows you to select the font type you want to apply. Look at the preview box to see how the font will appear in the document.

   • Font Style List - allows you to select the font styles.

   • Size List - allows you to select the font size. Remember that approximately 72 points is equal to 1 inch of height.

     

     You can also apply effects such as strikethrough, superscript, and subscript. Click the check box beside the effect you want to apply.

   • Color List - allows you to select colors.

     

3. Click OK or press Enter.

Using Shortcut Keys

To format cells quickly, select the cell, range or text then, press the correct shortcut key. The table below shows which shortcut key combination applies to which format.

Format	Shortcut Keys
Bold (Toggle on/off)	Ctrl+2 or Ctrl+B
Italic (Toggle on/off)	Ctrl+3 or Ctrl+I
Underline (Toggle on/off)	Ctrl+4 or Ctrl+U
Strikeout (Toggle on/off)	Ctrl+5

Formatting Cell Content

Applying Number Formats

When the number is formatted, its value does not change but its appearance changes. A formatted number is often easier to read than an unformatted number.

You can format numbers through the toolbar, but you have more options when you use the Number tab in the Format Cells dialog box.

Using the Toolbar

Currency Style - adds a dollar sign, commas, and two decimal places.

Percent Style - multiplies the number by 100 and adds a percent sign.

Comma Style - adds a comma for every three digits to the left of the decimal place and two decimal places.

Number Formats	Shortcut Keys
Currency	Ctrl+Shift+$
Percent	Ctrl+Shift+%
Comma	Ctrl+Shift+!

The Format Cells dialog box gives you more options than the options in the toolbar.

1. Select the cell or range of cells.

2. Press Ctrl+1 or click the Format menu and choose Cells. The Format Cells dialog box appears.

3. Click the Number tab.

4. Choose a Category. The right side of the dialog box changes depending on the choice.

5. Click OK or press Enter.

Designing Custom Number Formats:

To create custom formats on the worksheet, do the following:

1. Select the cells to which you want to apply the custom format.

2. Choose Format from the menu bar. Select Cells and click the Number tab.

3. If the existing format is closed to the custom format you want to create, select that format by choosing the appropriate category from the list.

4. In the Type box, edit the number format codes to create the format you want.

5. Click OK or press Enter.

   

Formatting Date and Time

Type dates and times in the cell the way you are accustomed to in writing. Excel recognizes dates and times entered in any format.

Predefined Excel Date and Time Formats:

Date Format	Example
m/d/yy	9/3/01
d-mmm-yy	3-Sep-01
d-mmm	3-Sep
mmm-yy	Sep-01
m/d/yy h:mm	9/3/01 0:00
Time Format	Example
h:mm AM/PM	1:54 PM
h:mm:ss AM/PM	1:54:00 PM
h:mm	13:54
h:mm:ss	13:54:00
mm:ss	54:00
mm:ss:0	54:00:0
[h]:mm:ss	13:54:00

1. Select the cell containing dates or times you want to format.

2. From the Format menu click Cells. Click the Number format.

   • Click Date in the category list to format the date.

   • Click Time in the category list to format time.

3. Click OK.

You can also choose any format you like from the Custom category.

Adjusting Decimal Place

On a worksheet:

Select the cells you want to format. To display more or fewer digits after the decimal point, click Increase Decimal or Decrease Decimal on the Formatting toolbar.

In a built-in number format:

On the Format menu, click Cells, and then click the Number tab. In the Category list, click Currency, Accounting, Percentage, or Scientific. In the Decimal places box, enter the number of decimal places you want to display. If you want to change the currency symbol, click the symbol you want to use in the Symbol list.

Applying Cell Borders and Shading

Adding Borders

To make your spreadsheet look more presentable you may add visual enhancement to it. Excel allows adding borders which enable the readers to focus on certain areas.

To add borders, follow the steps below:

1. Select the cell or range of cells where you want to add borders.

2. Do any of the following:

   • To apply the most recently selected border style, click Borders on the Formatting toolbar.

   • To apply a different border style, click the arrow next to Borders , then click a border on the palette.

To apply more border settings, click Cells on the Format menu, and then click the Border tab.

1. Click the Line Style you want.

2. Click a button to indicate the border placements.

   

Removing Borders

• On the Formatting toolbar, click the arrow next to Borders, then click No Border on the palette.

Adding Shadings or Patterns

Shading a cell means adding patterns to cell to mark important information.

The following steps will help you to add shading:

1. Select the cell or range of cells.

2. From the Format menu, choose Cells.

3. Choose the Pattern tab from the dialog box.

4. Choose a color from the color list.

5. Click OK or press Enter.

You can also use the Fill Color button on the Formatting or Drawing toolbar.

Using Format Painter

The Format Painter is located in the Standard toolbar. It copies the format from a selected object or text and applies it to the object or text you click. To copy the formatting to more than one item, double-click the Format Painter button, and then click each item you want to format. When you are finished, press ESC or the Format Painter button to turn it off.

Apply or Remove AutoFormats

1. Select the range you want to format or remove the auto format.

2. On the Format menu, click AutoFormat.

Do one of the following:

• Apply an Autoformat - Click the format you want. If you want to use only selected parts of the autoformat, click Options, and then clear the check boxes for the formats you don't want to apply.

• Remove an Autoformat - At the bottom of the list, click the format above None.

  

Whiz Words

Format Painter	AutoFormat
Shading	Fill Color

Lesson Summary

Enhancing worksheet appearance has a great part to make our worksheet more presentable. The Format Cells dialog box allows you to format font type, size, color, underline, and add effects. The Number tab allows you to format Date and Time. It also allows you to add borders and shading to your worksheet. You can also access these formatting tools in the Formatting toolbar.

Study Help

Directions: Answer the following questions briefly.

1. What tab in the Format Cell dialog box allows you to change the font type?

   ---

2. What is the importance of adding patterns to our worksheet?

   ---

   
   

   ---

   
   

   ---

3. Enumerate the steps to:

   1. Change the appearance of the border.

      ---

      
      

      ---

      
      

      ---

   2. Add patterns to your worksheet.

      ---

      
      

      ---

      
      

      ---

   3. Format date and time.

      ---

      
      

      ---

      
      

      ---

Activity 1

Directions: Perform the following instructions carefully.

1. Open the file Information.xls.

2. Add borders to your table.

3. Format the following:

   • Title - Algerian, 15, Bold

   • Column Headings - Arial 12, Bold, Italic

   • Records - Times New Roman, 12, Normal

4. Add shading to your worksheet.

5. Insert the Date and Time at the end of your table using this format (m/d/yy h:mm).

6. Save your work as Formatted Information.xls in Excel-Activities folder.

Activity 2

Directions: Perform the listed tasks below.

1. Copy the Calendar of Activities as shown below.

AUGUST CALENDAR OF Activities 2002		Days (Sun-Sat)
	Font - Arial or Tahoma Size - 26 Font Color - Yellow Fill Color - Black Row Height - 33.75		Font - Arial or Tahoma Size - 10 Fill Color - White Font Color - Sea Green
18		21
	Font - Arial Style - Bold, Underlined, Italic Fill Color - Pale Blue Text Color - Green		Font - Arial Size - 10 Style - Bold Fill Color - Plum Text Color - Yellow
Dates (1-31)		25
	Font - Arial or Tahoma Size - 10 Font Color - Black		Font - Arial Size - 10 Style - Bold Fill Color - Light Blue Text Color - White
12, 13, 14		Legend
	Font - Arial Style - Bold, Italic, Underlined Fill Color - Light Green		Font - Arial Size - 10 Style - Bold, Italic Font Color - Dark Red
15, 16, 17
	Font - Arial Style - Bold and Underlined Fill Color - Lime Text Color - Blue
			Font - Arial Size - 12 Style - Bold, Underlined Font Color - Red

2. Use Find and Replace to change the format of days (Sun, Mon …) to Times New Roman, Italic.

3. Use Find and Replace to change the word "Achievement Holiday" to "Rest Day".

4. Save your work as Calendar.xls in Excel-Activities folder.