• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 3. Building Basic Formulas > Customizing Numeric Formats

Customizing Numeric Formats

Excel numeric formats give you lots of control over how your numbers are displayed, but they have their limitations. For example, no built-in format enables you to display a number such 0.5 without the leading zero, or to display temperatures using, say, the degree symbol.

To overcome these and other limitations, you need to create your own custom numeric formats. You can do this either by editing an existing format or by entering your own from scratch. The formatting syntax and symbols are explained in detail later in this section.

Every Excel numeric format, whether built-in or customized, has the following syntax:

								positive format;negative format;zero format;text format

The four parts, separated by semicolons, determine how various numbers are presented. The first part defines how a positive number is displayed, the second part defines how a negative number is displayed, the third part defines how zero is displayed, and the fourth part defines how text is displayed. If you leave out one or more of these parts, numbers are controlled as shown here:

Number of Parts UsedFormat Syntax
Threepositive format;negative format;zero format
Twopositive and zero format; negative format
Onepositive, negative, and zero format

Table 3.6 lists the special symbols you use to define each of these parts.

Table 3.6. Numeric Formatting Symbols
GeneralDisplays the number with the General format.
#Holds a place for a digit and displays the digit exactly as typed. Displays nothing if no number is entered.
0Holds a place for a digit and displays the digit exactly as typed. Displays 0 if
?Holds a place for a digit and displays the digit exactly as typed. Displays a space if no number is entered.
. (period)Sets the location of the decimal point.
, (comma)Sets the location of the thousands separator. Marks only the location of the first thousand.
%Multiplies the number by 100 (for display only) and adds the percent(%)character.
E+ e+ E– e–Displays the number in scientific format. E– and e– place a minus sign in the exponent; E+ and e+ place a plus sign in the exponent.
/ (slash)Sets the location of the fraction separator.
$ ( ) : – + <space>Displays the character.
*Repeats whatever character immediately follows the asterisk until the cell is full. Doesn't replace other symbols or numbers.
_ (underscore)Inserts a blank space the width of whatever character follows the underscore.
\ (backslash)Inserts the character that follows the backslash.
textInserts the text that appears within the quotation marks.
@Holds a place for text.
[COLOR]Displays the cell contents in the specified color.
[condition value]Uses conditional statements to specify when the format is to be used.

Before looking at some examples, let's run through the basic procedure. To customize a numeric format, select the cell or range you want to format and then follow these steps:

Choose Format, Cells (or press Ctrl+1) and select the Number tab, if it's not already displayed.

In the Category list, choose Custom.

If you're editing an existing format, choose it in the Type list box.

Edit or enter your format code.

Click OK. Excel returns you to the worksheet with the custom format applied.

Excel stores each new format definition in the Custom category. If you edited an existing format, the original format is left intact and the new format is added to the list. You can select the custom formats the same way you select the built-in formats. To use your custom format in other workbooks, you copy a cell containing the format to that workbook. Figure 3.14 shows a dozen examples of custom formats.

Figure 3.14. Sample custom numeric formats.

Here's a quick explanation for each example:

  • Example 1— These formats show how you can reduce a large number to a smaller, more readable one by using the thousands separator. A format such as 0,000.0 would display, say, 12300 as 12,300.0. If you remove the three zeros between the comma and the decimal (to get the format 0,.0), Excel displays the number as 12.3 (although it still uses the original number in calculations). In essence, you've told Excel to express the number in thousands. To express a larger number in millions, you just add a second thousands separator.

  • Example 2— Use this format when you don't want to display any leading or trailing zeros.

  • Example 3— These are examples of four-part formats. The first three parts define how Excel should display positive numbers, negative numbers, and zero. The fourth part displays the message Enter a number if the user enters text in the cell.

  • Example 4— In this example, the cents sign (¢) is used after the value. To enter the cents sign, press Alt+0162 on your keyboard's numeric keypad. (This won't work if you use the numbers along the top of the keyboard.) Table 3.7 shows some common ANSI characters you can use.

    Table 3.7. ANSI Character Key Combinations
    Key CombinationANSI Character

  • Example 5— This example adds the text string "Dollars" to the format.

  • Example 6— In this example, an M is appended to any number, which is useful if your spreadsheet units are in megabytes.

  • Example 7— This example uses the degree symbol (°) to display temperatures.

  • Example 8— The three semicolons used in this example result in no number being displayed (which is useful as a basic method for hiding sensitive values).

  • Example 9— This example shows that you can get a number sign (#) to display in your formats by preceding # with a backslash (\).

  • Example 10— In this example, you see a trick for creating dot trailers. Recall that the asterisk (*) symbol fills the cell with whatever character follows it. So, creating a dot trailer is a simple matter of adding "*." to the end of the format.

  • Example 11— This example shows a similar technique that creates a dot leader. Here the first three semicolons display nothing; then comes "*.", which runs dots from the beginning of the cell up to the text (represented by the @ sign).

  • Example 12— This example shows a format that's useful for entering stock quotations.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint