Numeric Formatting Options

The numeric values that you place in your Excel cells are more than just numbers; they often represent dollar amounts, a date, or a percentage. If the various numeric style buttons on the Formatting toolbar (discussed in the previous section) do not offer the exact format you want for your numbers , don't worry. Excel's Format Cells dialog box offers a wide range of number formats and even allows you to create custom formats.

To use the Format Cells dialog box to assign numeric formatting to cells in a worksheet, follow these steps:

1. Select the cell or range that contains the values you want to format.

2. Select the Format menu and select Cells . The Format Cells dialog box appears.

3. Click the Number tab. The different categories of numeric formats are displayed in a Category list (see Figure 9.1).

   Figure 9.1. Apply a numeric format in the Number tab of the Format Cells dialog box.

   

4. In the Category list, select the numeric format category you want to use. The sample box displays the default format for that category.

5. Click OK to assign the numeric format to the selected cells.

As you can see from the Number tab on the Format Cells dialog box, Excel offers several numeric formatting styles. Table 9.1 provides a list of these different number formats.

Table 9.1. Excel's Number Formats

Number Format	Examples	Description
General	10.6 $456,908.00	Excel displays your value as you enter it. In other words, this format displays currency or percent signs only if you enter them yourself.
Number	3400.50 120.39	The default Number format has two decimal places. Negative numbers are preceded by a minus sign, but they can also appear in red and/or parentheses.
Currency	$3,400.50 $3,400.50	The default Currency format has two decimal places and a dollar sign. Negative numbers appear with a minus sign, but they can also appear in red and/or parentheses.
Accounting	$3,400.00 $978.21	Use this format to align dollar signs and decimal points in a column. The default Accounting format has two decimal places and a dollar sign.
Date	11/7	The default Date format is the month and day separated by a slash; however, you can select from numerous other formats.
Time	10:00	The default Time format is the hour and minutes separated by a colon ; however, you can opt to display seconds, a.m., or p.m.
Percentage	99.50%	The default Percentage format has two decimal places. Excel multiplies the value in a cell by 100 and displays the result with a percent sign.
Fraction	1/2	The default Fraction format is up to one digit on each side of the slash. Use this format to display the number of digits you want on each side of the slash and the fraction type (such as halves , quarters , eighths, and so on).
Scientific	3.40E+03	The default Scientific format has two decimal places. Use this format to display numbers in scientific notation.
Text	135RV90	Use Text format to display both text and numbers in a cell as text. Excel displays the entry exactly as you type it.
Special	02110	This format is specifically designed to display ZIP codes, phone numbers, and Social Security numbers correctly so that you don't have to enter any special characters , such as hyphens.
Custom	00.0%	Use Custom format to create your own number format. You can use any of the format codes in the Type list and then make changes to those codes. The # symbol represents a number placeholder, and 0 represents a zero placeholder.

You can also open the Format Cell dialog box using a shortcut menu. Select the cell or cells that you want to assign a numeric format to, and then right-click those cells. On the shortcut menu that appears, select Format Cells . Then, select the Number tab to select your numeric format.

That's Not the Date I Entered! If you enter a date into a cell that is already formatted with the Number format, the date appears as a value that represents the number of days between January 1, 1900, and that date. Change the cell's formatting from a Number format to a Date format and select a date type. The entry in the cell then appears as an actual date.

How Do I Get Rid of a Numeric Format? To remove a number format from a cell (and return it to General format), select the cell whose formatting you want to remove, open the Edit menu, select Clear , and select Formats .