Using Excel 2007’s Special Number Formats

Excel 2007 includes a nifty number format category called Special that contains four formats: Zip Code, Zip Code + 4, Phone Number, and Social Security Number. These Special number formats, described below, really come in handy when creating large tables with this type of data in Excel:

  • Zip Code: Retains any leading zeros in the value (important for zip codes and of absolutely no importance in arithmetic computations). Example: 00123.

  • Zip Code + 4: Automatically separates the last four digits from the first five digits and retains any leading zeros. Example: 00123-5555.

  • Phone Number: Automatically encloses the first three digits of the number in parentheses and separates the last four digits from the previous three with a dash. Example: (999) 555-1111.

  • Social Security Number: Automatically puts dashes in the value to separate its digits into groups of three, two, and four. Example: 666-00-9999.

Follow these steps to apply any of the Special formats:

  1. Select the cells containing the numbers you want to format.

  2. From the Home tab, click the Number dialog box launcher in the bottom-right corner of the Number group.

    The Format Cells dialog box appears, with the Number tab on top.

  3. In the Category list, select Special.

    The available special formats appear in the Type list.

    Use the Format Cells dialog box to apply Special formats such as Zip Code and Phone Number.
    Use the Format Cells dialog box to apply Special formats such as Zip Code and Phone Number.
  4. Select the desired format from the Type list.

    When you click a format in the Type list, Excel shows what effect this would have on the first of the values in the current cell selection in the Sample area above the Type list.

  5. Click OK.

    Excel applies the format to the cell selection.

blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.