Making Numbers Easier to Read


Making Numbers Easier to Read

Changing the format of the cells in your worksheet can make your data much easier to read, both by setting data labels apart from the actual data and by adding borders to define the boundaries between labels and data even more clearly. Of course, using formatting options to change the font and appearance of a cell s contents doesn t help with idiosyncratic data types such as dates, phone numbers, or currency.

For example, consider U.S. phone numbers. These numbers are 10 digits long and have a three-digit area code, a three-digit exchange, and a four-digit line number written in the form (###) ###-#### . While it s certainly possible to type a phone number with the expected formatting in a cell, it s much simpler to type a sequence of 10 digits and have Excel change the data s appearance.

You can tell Excel to expect a phone number in a cell by opening the Format Cells dialog box to the Number tab and displaying the formats available under the Special category.

click to expand

Clicking Phone Number from the Type list tells Excel to format 10-digit numbers in the standard phone number format. As you can see by comparing the contents of the active cell and the contents of the formula bar in the next graphic, the underlying data isn t changed, just its appearance in the cell.

click to expand
Troubleshooting  

If you type a nine-digit number in a field that expects a phone number, you won t see an error message; instead, you ll see a two-digit area code. For example, the number 4255550122 would be displayed as (425) 555-0122 . An 11-digit number would be displayed with a four-digit area code.

Just as you can instruct Excel to expect a phone number in a cell, you can also have it expect a date or a currency amount. You can make those changes from the Format Cells dialog box by choosing either the Date category or the Currency category. The Date category lets you pick the format for the date (and determine whether the date s appearance changes due to the Locale setting of the operating system on the computer viewing the workbook). In a similar vein, selecting the Currency category displays controls to set the number of places after the decimal point, the currency symbol to use, and the way in which Excel should display negative numbers.

You can also create a custom numeric format to add a word or phrase to a number in a cell. For example, you can add the phrase per month to a cell with a formula that calculates average monthly sales for a year to ensure that you and your colleagues will recognize the figure as a monthly average. To create a custom number format, click Cells on the Format menu to open the Format Cells dialog box. Then, if necessary, click the Number tab to display the Number tab page.

In the Category list, click Custom to display the available custom number formats in the Type list. You can then click the base format you want and modify it in the Type box. For example, clicking the 0.00 format causes Excel to format any number in a cell with two digits to the right of the decimal point.

Tip  

The zeros in the format indicate that that position in the format can accept any number as a valid value.

To customize the format, click in the Type box and add to the format any symbols or text you want. For example, typing a dollar sign to the left of the existing format and then typing per month to the right of the existing format causes the number 1500 to be displayed as $1500.00 per month .

Important  

You need to enclose any text in quotes so that Excel recognizes the text as a string to be displayed in the cell.

In this exercise, you assign date, phone number, and currency formats to ranges of cells in your worksheet. After you assign the formats, you test them by entering customer data.

USE the  EasyRead.xls document in the practice file folder for this topic. This practice file is located in the  My Documents\Microsoft Press\Office 2003 SBS\ChangingDocAppearance folder, and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .

OPEN the  EasyRead.xls document.

  1. Click cell B4.

  2. On the Format menu, click Cells .

    The Format Cells dialog box appears.

  3. If necessary, click the Number tab.

  4. In the Category list, click Date .

    The Type list appears with a list of date formats.

    click to expand
  5. In the Type list, click *3/14/01 .

  6. Click OK .

    Excel assigns the chosen format to the cell.

  7. On the Standard toolbar, click the Format Painter button.

    Cell B4 is highlighted with a marquee outline.

  8. Click cell B5 and drag to cell B23.

    Excel assigns the format from cell B4 to cells B5:B23.

  9. Click cell J4.

  10. On the Format menu, click Cells .

    The Format Cells dialog box appears.

  11. In the Category list, click Special .

    The Type list appears with a list of special formats.

  12. In the Type list, click Phone Number and then click OK .

    The Format Cells dialog box disappears.

  13. On the Standard toolbar, click the Format Painter button.

    Cell J4 is highlighted with a marquee outline.

  14. Click cell J5 and drag to cell J23.

    Excel assigns the format from cell J4 to cells J5:J23.

  15. Click cell K4.

  16. On the Format menu, click Cells .

    The Format Cells dialog box appears.

  17. In the Category list, click Custom .

    The contents of the Type list are updated to reflect your choice.

    click to expand
  18. In the Type list, click the #,##0.00 item.

    #,##0.00 appears in the Type box.

  19. In the Type box, click to the left of the existing format and type $ , and then click to the right of the format and type "total" .

  20. Click OK .

    The Format Cells dialog box disappears.

  21. On the Standard toolbar, click the Format Painter button.

    Cell K4 is highlighted with a marquee outline.

  22. Click cell K5 and drag to cell K23.

    Excel assigns the format from cell K4 to cells K5:K23.

  23. In cell B4, type January 25, 2004 and press [ENTER].

    The contents of cell B4 change to 1/25/04 , matching the format you set earlier.

  24. Type the following text in the indicated cells:

    Cell

    Type this:

    C4

    C100001

    D4

    Steven

    E4

    Levy

    F4

    6789 Elm Street

    G4

    Redmond

    H4

    WA

    I4

    87063

    J4

    4255550102

    K4

    2400

    Notice that the phone number and the currency amount change to match the formats that you created earlier.

    click to expand
  25. On the Standard toolbar, click Save to save your changes.

CLOSE the  EasyRead.xls document.




Microsoft Office 2003 Step by Step
MicrosoftВ® Office ExcelВ® 2003 Step by Step (Step By Step (Microsoft))
ISBN: 0735615187
EAN: 2147483647
Year: 2005
Pages: 350
Authors: Curtis Frye

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net