EXCEL 2000 – FORMATTING - DOC by hcj


									                                                                iCIT Helpdesk and Training Services

Formatting Numbers
Cell Text Alignment
Fonts, Underlining, Color and Special Effects
Borders and Cell Shading
Cell, Sheet and Workbook Protection

Adding formatting to a spreadsheet can help your work get the attention it deserves.
Enlarging font size, bolding or italicizing text, and changing the orientation of the text are
all ways to call attention to certain data.

                                                             1. Open Excel.
                                                             2. Open the file named Excel
                                                             – Budget Summary Report.

                                                             Number and Font
                                                             1. Select the range of cells
                                                             2. From the Format menu,
                                                                choose Cells.

                                                             The number tab includes
                                                             options to change the way
                                                             numbers, dates, and times
                                                             are displayed.

                                                             1. Click on the Number tab.
                                                             2. Select Accounting from
                                                                the category list.
                                                             3. The defaults should be 2
                                                                decimal places and a
   dollar sign as the symbol.

3/1/2010                                                                              Page 1 of 7
                                                                  iCIT Helpdesk and Training Services

                                                             1. Click on the Font tab.
                                                             2. Choose Times New
                                                                Roman from the list of
                                                             3. Change the font size to 12.
                                                             4. Click OK.

Many of the formatting features found on the Format Cells dialog box can also be found
on the Formatting Toolbar. Use the Toolbar buttons to quickly add formatting to cells.

     Font Type, Font Size          Bold, Italic,       Left,        Merge &
                                   Underline        Center or       Center
                                                    Right Align

 Currency, Percent,         Increase or          Increase or           Border, Fill Color,
   Comma Style               Decrease          Decrease Indent           & Font Color

Use the Formatting Toolbar buttons to format the Total amounts.
1. Select the range B28:G28.
2. Click on the currency style button.
3. Change the font type to Times New Roman and the Font Size to 12 using the drop-
   down lists located on the formatting toolbar. Add the Currency Style if you’d like.

Format the Title
1. Click on Cell A1.
2. Click on the Bold button and the Italic button.

3/1/2010                                                                                Page 2 of 7
                                                              iCIT Helpdesk and Training Services

3. Change the font size to 14.

Merge & Center
The Year-to-Date heading applies to the first four columns of numerical data. Using
Merge & Center, the cell can span above all four columns.

1. Select the range of cells B6:E6.
2. Click on the Merge & Center button.
   (This option is also available on the Format Cells dialog box, the alignment tab.)
3. Use the same button to merge and center the Current Month heading over cells F6-

                                                             1. Select the two merged
                                                                cells (Year-to-Date and
                                                                Current Month).
                                                             2. From the Format menu,
                                                                choose Cells.
                                                             3. Click on the Border tab.
                                                             4. Choose the double
                                                                underline style in the
                                                                style box on the right-
                                                                had side of the window.
                                                             5. From the color box,
                                                                choose Blue. (2nd row,
                                                                6th column from left).
                                                             6. Click on the Outline
                                                                button to outline the
                                                             7. Click on the Inside
                                                                button to place a border
                                                                between the cells.
                                                             8. Click OK.

Note: When applying a line style or color, you must select the style or color option
before clicking any of the border buttons to apply it.

3/1/2010                                                                            Page 3 of 7
                                                                            iCIT Helpdesk and Training Services

Changing the alignment of cell entries can help emphasize data or draw attention to
titles or headers.
Horizontal alignment selections:

                                                                          General - Applies the default
                                                                           alignment. Text data is aligned at
                                                                           the left edge of the cell, and
                                                                           numbers, dates, and times are
                                                                           aligned at the right of the cell.

                                                                          Left - Aligns contents at the left
                                                                           edge of the cell.

                                                                          Center - Centers the contents of
                                                                           the cell.

                                                                          Right - Aligns cell contents at the
                                                                           right edge of the cell.

                                                                          Fill - Repeats the characters in
                                                                           the left-most cell in the selection
                                                                           across the selected range. All
                                                                           cells to be filled in the selected
                                                                           range must be empty. For
                                                                           example, you can create a
                                                                           border-like effect by typing a dash
                                                                           (-) or an asterisk (*) in a cell and
                                                                           then using this option to repeat
                                                                           the string of characters across a

    Justify - Breaks the cell contents into multiple lines within the cell and adjusts the spacing between
     words so that all lines are as wide as the cell.

Vertical Alignment:
Select an option in the Vertical box to change the vertical alignment of cell contents. By default, Excel
aligns text vertically on the bottom of a cell.

To change the orientation of a cell’s contents, select a cell or range and click and drag the red diamond
next to the word Text to select the angle --OR-- Enter the number of degrees for realignment using the
spin box.

1. Select the range of cells B8:G8.
2. From the Format menu, choose Cells.
3. Click on the Alignment tab.
4. Choose Center from the Horizontal list box.
5. Change the orientation to 45 degrees by dragging the red diamond to the next
   diamond above it, or type 45 in the degree box.
6. Click OK. The text is now displayed at a 45 degree angle and centered within the

3/1/2010                                                                                           Page 4 of 7
                                                             iCIT Helpdesk and Training Services

You could also use the center alignment button on the formatting toolbar, but there is no
button available to adjust text orientation.

                                                          Applying Cell Shading
                                                          1. Select the range of cells
                                                          2. From the Format menu,
                                                             choose Cells.
                                                          3. Click on the Patterns tab.
                                                          4. Choose the light lavender
                                                             found furthest to the right
                                                             in the second row from the
                                                          5. Click on the pattern box
                                                             and choose 6.25% gray
                                                             (the uppermost right-hand
                                                          6. Click OK to apply the
                                                          7. With the range of cells still
                                                             selected, click the Bold
                                                             button to make the text
                                                             more readable.

Additional Changes
1. Select the Social Security numbers (C32:C36).
2. From the Format menu, choose Cells.
3. Click on the Number tab.
4. Choose Special from the category listing and Social Security Number as the type.
5. Click OK.

Use the same steps to format the phone numbers in cells D32:D36.
Change the salary amounts to currency style.
Underline the headings in row 31.

A completed Budget Summary Report is located on the last page of this handout.

3/1/2010                                                                           Page 5 of 7
                                                        iCIT Helpdesk and Training Services

Information about Workbook Protection:

                                                     The Protection panel of the
                                                     Format Cells window will
                                                     allow you to add various
                                                     levels of protection to cells,
                                                     sheets, or workbooks.

                                                     By default, Excel will lock
                                                     each cell so that other users
                                                     cannot make changes to your
                                                     data or formulas once you
                                                     protect your Sheet or
                                                     Workbook. If you wish to
                                                     unlock a cell or range, click in
                                                     the Locked box to turn off the

                                                     You can also choose to Hide
                                                     formulas so that other users
                                                     can only view the results of a
                                                     formula, not the formula itself.
                                                     Select the cell or range that
                                                      contain the formulas you
                                                      wish to hide, then click in the
                                                      Hidden box.

                                    To protect a sheet or workbook, use the
                                    Tools menu and choose Protection. Then
                                    make your selection from the secondary
                                        Contents locks the cells on the sheet; no changes or
                                         deletions are allowed.
                                        Objects prevents changes to charts or graphical
                                        Scenarios locks any scenarios you’ve created.

                                    The Password window assigns password protection to
                                    your document. Pick a word you won’t forget, then write it
                                    down and put it in a safe place. Once protected with a
                                    password, the sheet can’t be unprotected without it!

3/1/2010                                                                      Page 6 of 7
                                                                             T&IR Training Services

3/1/2010   D:\Docstoc\Working\pdf\03ec5479-5378-43a2-ad94-7935f6fb5f60.doc                      Page 7 of 7

To top