Use formatting to change the way Numbers, Dates, Times, and Text appear in Microsoft Excel

Applies to
Microsoft Excel 97

Custom Number Formatting in Microsoft Excel

Overview

This document contains information about using custom number formats. A custom number format allows you to change the way numbers, dates, times, and text are displayed in Microsoft Excel. This document contains information about the following topics:

 Note   This document explains how to use the number format codes and how to create your own custom number formats in Microsoft Excel.

A sample workbook, that contains the custom number formats described in this document, is also available.

The illustrations and examples in this document pertain to Microsoft Excel 97. However, unless noted otherwise, you can use the number formatting codes and custom number formats in this document in Microsoft Excel 5.0 and 7.0.

Introduction to Number Formatting

You can use number formats to change the way Microsoft Excel displays numbers, text, dates, and times in cells on a worksheet. When you apply a number format to a cell, you are not changing the underlying value that the cell contains; you simply change the way the value appears on the worksheet. The underlying value in the cell, not the formatted value that is displayed, is the value that Microsoft Excel uses in calculations.

A number format is a set of symbols, or codes, that tell Microsoft Excel how to display a value on the worksheet. Microsoft Excel provides a variety of built-in number formats for percentages, fractions, currency, dates, times, and other numbers.

The default number format for all cells on a new worksheet is the General format. A number that uses the General format appears as an integer (for example, 789) or as a decimal fraction (for example, 7.89). A number that uses the General format and is longer than the width of the cell, appear as a number in scientific notation (for example, 7.89E+08). When you type data in a cell that is formatted with the General format, Microsoft Excel determines whether one of the other built-in number formats is more appropriate for the data. For example, if you type a number that begins with a dollar sign or ends with a percent sign, Microsoft Excel changes the cell number format from General to a currency format or a percentage format.

You can change the number format of a cell to one of the other built-in formats, or you can create your own custom number format. To change the number format of a cell, click the cell to select it, click Cells on the Format menu, and then click the Number tab.

Fig. 1  Figure 1: Format Cells Dialog Box

To create a custom number format, click the Custom category in the dialog box, and then type the codes (see the next section for more information) for the custom number format and click OK to apply the format to the selected range of cells.

Number Format Codes

To create a custom number format, specify format codes that describe how you want to display a number, date, time, or text. You can specify up to four sections that have format codes. The sections, separated by semicolons, define the formats for positive numbers, negative numbers, zero values, and text, in that order. For each section of the number format, type symbols that represent how you want the number to look. Figure 2 contains a custom number format that uses four sections.

Fig. 2  Figure 2: Sections of a Number Format

Figure 3 shows how data with the sample custom number format appears.

Fig. 3  Figure 3. Data Before and After Applying the Format

When you create a number format you do not have to create all four sections. For example, if you include only two number sections, the first section is used for both positive numbers and zeros; the second section is used for negative numbers. If you create only one number section, all numbers use that format. Text in cells is not affected by the format if the format contains less than four sections.

There are formatting codes that you can use to create number, text, date, time, and other formats.

Basic Number Formatting

The number of digits that a number format displays depends on the number of placeholders that you use. To create placeholders for numbers, include the format codes #, 0 (zero), or ? in a section. If the number has more digits to the right of the decimal point than there are placeholders in the format, Microsoft Excel rounds the number to as many decimal places as there are placeholders. If the number has more digits to the left of the decimal point than there are placeholders in the format, Microsoft Excel displays the extra digits.

Placeholders have the following characteristics:

  • The placeholder # does not display insignificant zeros.
  • The placeholder 0 (zero) displays extra zeros when a number has fewer digits than there are zeros in the format.
  • The placeholder ? displays a space for insignificant zeros on either side of the decimal point so that the decimal points are aligned. You can also use this symbol for fractions that have varying numbers of digits.

Common format codes are listed in the following table.

To display Use this format code
1234.59 as 1234.6 ####.#
8.9 as 8.900 #.000
12 as 12.0 and 1234.568 as 1234.57 #.0#
5.25 as 5 1/4 and 5.3 as 5 3/10 with aligned division symbols # ???/???
.5 as 0.5 0.##
  • To display a comma as a thousands separator or to scale a number by a multiple of one thousand, include a comma in the number format.

Common format codes that use the comma as a separator are listed in the following table.

To display Use this format code
12000 as 12,000 #,###
12000 as 12 #,
12200000 as 12.2 0.0,,

Example 1: Displaying Leading Zeros

You can use the placeholder 0 (zero) to display leading zeros when a number has fewer digits than you specify in the format. You can use the 0 placeholder to create a number format that displays leading zeros in cells. One example of data for which you may want to display leading zeros is zip code data. You can format a cell with the number format code 00000 to display the value with at least five digits. A cell that contains less than five digits is automatically "padded" with leading zeros so that it contains five digits.

Figure 4 contains data that is formatted with the code 00000.

Fig. 4  Figure 4. Displaying Leading Zeros

Example 2: Aligning Values with Decimal Points

The placeholder ? leaves a space for insignificant zeros to the right of a number. Insignificant zeros are not displayed. This allows you to align values that have varying lengths with the decimal point.

Figure 5 displays data that uses the number format #.??? to align values with the decimal points. Notice that the values line up with the decimal points.

Fig. 5  Figure 5. Aligning Values with Decimal Points

Example 3: Displaying Numbers in Thousands

In Microsoft Excel, you can scale a number by a multiple of one thousand. For example, using a custom number format, you can format the number 12,500 to appear as 12.5. Remember that although the cell displays 12.5, Microsoft Excel still uses the underlying value 12,500 for calculations.

To display a number that you scale by a multiple of one thousand, use a number format like #, or #.00, so that no placeholders follow the comma. Figure 6 illustrates how data appears when you use #, or #.00,.

Figure 6. Displaying Numbers in Thousands

Do not type a placeholder after the comma when you create the formats (for example, #, and #.00,). This is the key to creating a number format that scales a number as a multiple of one thousand. You can also display numbers in millions (for example, #,,) and billions (for example, #,,,) by typing more commas at the end of the number format.

Stored Values vs. Displayed Values

Microsoft Excel displays a number according to the format of the cell that containing the number; thus, the number you see in a cell may differ from the underlying value that is stored in the cell. No matter how you format a cell to display a number, Microsoft Excel always uses the stored value for calculations. You can determine the stored value of a formatted cell by examining the value of the cell in the formula bar.

If you display a number with less precision than its stored value, Microsoft Excel displays a number that is rounded up or down. This may cause a formula to display results that appear incorrect. For example, if two cells each contain the value 1.4, and you add them in a third cell, the result is 2.8. If you format all three cells to display an integer, Microsoft Excel rounds the values. The result that appears is 1 + 1 = 3, which appears to be incorrect.

In Microsoft Excel, you can use a setting to force Microsoft Excel to calculate formulas based on displayed values. Selecting this setting remedies the situation in which the results of formatted calculations appear incorrect due to rounding. The setting is commonly used for calculating figures that are based on monetary amounts. To use this setting, use the following steps:

  1. On the Tools menu, click Options.
  2. Click the Calculations tab and click Precision As Displayed.
  3. Click OK.

 Important   When you click Precision as Displayed, Microsoft Excel permanently changes stored constant values in your workbook to the displayed values. Microsoft Excel cannot restore the original full precision values at a later time.

Using Color in a Format

In addition to controlling the appearance of numeric values, you can change the color of selected cell entries. This gives you the ability to emphasize cells on your worksheet based on input. Note that the colors you specify with a number format take precedence over the colors you specify for the font.

Each of the four sections in a number format can have its own color. To use a color in a section of your number format, include the name of the color you want in brackets [] at the front of the section. You can use the built-in colors: black, blue, cyan, green, magenta, red, white, and yellow. You can also use a corresponding color in the color palette (for example, COLOR n, where n is a number from 1 to 56). When you use the code COLOR n, Microsoft Excel selects the corresponding color from the current palette. If the color you specify is a dithered color (a color that is a mixture of dots of two or more solid colors), Microsoft Excel uses the closest solid color match for the format.

Example 4: Using Color in a Number Format to Validate Data

Color in a number format is used primarily to emphasize a certain cell or range of cells. You can use color to validate whether the data entered in a cell is correct. For example, if you are using a range of cells for data entry and you want to limit data entry to only numbers that are greater than or equal to zero, you can use a custom number format similar to the following:

0;[Red]"Error!";0;[Red]"Error!"

If you apply this format to a cell and type a negative number or text in the cell, the cell displays the red text "Error!" to alert you that the value you entered is not valid. Figure 7 illustrates how invalid values appear in cells with this number format.

Fig. 7  Figure 7. Using Color in a Number Format for Validating Data

Suppressing the Certain Values with Number Formats

To prevent any type of value (positive, negative, zero, or text) from appearing, omit the code for the corresponding section.

Example 5: Suppressing Zeros

To prevent zeros from appearing in cells on your worksheet, apply a number format in which you omit the third section (the section for zeros). For example, when you apply the number format 0;-0;;@, Microsoft Excel does not display zeros.

Fig. 8  Figure 8. Suppressing Zeros

Example 6: Suppressing Error Values

In Microsoft Excel, you can prevent error values (such as #DIV/0!, #N/A, #VALUE!, #REF!, and #NUM!) from being displayed by using a combination of a custom number format and a font color. There are only four sections in a number format (positive, negative, zero and text). Note that there is no section for error values. However, you can apply a font color that matches the cell background (usually white), and then apply a custom number format that displays numbers and text as black. Because the number format color overrides the font color, numbers and text appear in black text, and errors appear in white text, which gives the appearance that the error values are hidden. Use the following steps to hide error values in a range of cells:

  1. Select the cells you want to format.
  2. On the Format menu, click Cells.
  3. Click the Font tab.
  4. Click the same color that is used for the cell background (usually white) in the Color list.
  5. Click the Numbers tab.
  6. Click the Custom category and type the code [BLACK]General in the Type box.
  7. Click OK to return to your worksheet.
Fig. 9  Figure 9. Suppressing Errors

Number Format Codes for Text and Spacing

Setting Conditional Values for a Number Format Section

Conditional Formatting and Microsoft Excel 97

Number Formatting Limitations

Related Knowledge Base Articles

 
 
Applies to:
Excel 2003