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

Chapter 3. Building Basic Formulas > Changing Numeric Formats

Changing Numeric Formats

The quickest way to format numbers is to specify the format as you enter your data. For example, if you begin a dollar amount with a dollar sign ($), Excel automatically formats the number as currency. Similarly, if you type a percent sign (%) after a number, Excel automatically formats the number as a percentage. Here are a few more examples of this technique. Note that you can enter a negative value using either the negative sign (–) or parentheses.

Number EnteredNumber DisplayedFormat Used
$1234.567$1,234.57Currency
($1234.5)($1,234.50)Currency
10%10%Percentage
123E+021.23E+04Scientific
5 3/45 3/4Fraction
0 3/43/4Fraction
3/44–MarDate


NOTE

Excel interprets a simple fraction such as 3/4 as a date (March 4, in this case). Always include a leading zero, followed by a space, if you want to enter a simple fraction from the formula bar.


Specifying the numeric format as you enter a number is fast and efficient because Excel guesses the format you want to use. Unfortunately, Excel sometimes guesses wrong (for example, interpreting a simple fraction as a date). In any case, you don't have access to all the available formats (for example, displaying negative dollar amounts in red). To overcome these limitations, you can select your numeric formats from a list. Here are the steps to follow:

1.
Select the cell or range of cells to which you want to apply the new format.

2.
Choose Format, Cells (or press Ctrl+1). The Format Cells dialog box appears.

3.
Choose the Number tab, if it's not already displayed.

4.
Select the format you want to use in the Category list box. Excel displays the various options available for the category you choose. For example, Figure 3.13 shows the options that appear when you choose the Number format.

Figure 3.13. When you choose a format in the Category list, Excel displays the format's options.


5.
Choose the formatting options you want to use. The Sample information box shows a sample of the format applied to the current cell's contents.

6.
Click OK. Excel returns you to the worksheet with the new formatting applied.

As an alternative to the Format Cells dialog box, Excel offers several keyboard shortcuts for setting the numeric format. Select the cell or range you want to format, and use one of the key combinations listed in Table 3.5.

Table 3.5. Shortcut Keys for Selecting Numeric Formats
Shortcut KeyFormat
Ctrl+~General
Ctrl+!Number (two decimal places; using thousands separator)
Ctrl+$Currency (two decimal places; using dollar sign; negative numbers surrounded by parentheses)
Ctrl+%Percentage (zero decimal places)
Ctrl+^Scientific (two decimal places)


If your mouse is nearby, you can use the tools in the Formatting toolbar as another method of selecting numeric formats. Here are the four available tools (see Figure 3.13):

ButtonFormat
Currency StyleAccounting (two decimal places; using dollar sign)
Percent StylePercentage (zero decimal places)
Comma StyleNumber (two decimal places; using thousands separator)
Increase DecimalIncreases the number of decimal places in the current format
Decrease DecimalDecreases the number of decimal places in the current format


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