Page 509 - Excel 2007 Bible

31_044039 ch24.qxp 11/21/06 11:13 AM Page 466
Part IV
Using Advanced Excel Features
Automatic number formatting
Excel is smart enough to perform some formatting for you automatically. For example, if you enter 12.3%
into a cell, Excel knows that you want to use a percentage format and applies it automatically. If you use
commas to separate thousands (such as 123,456), Excel applies comma formatting for you. And if you pre-
cede your value with a currency symbol, Excel formats the cell for currency.
You have an option when it comes to entering values into cells formatted as percentages. Access
NOTE
NOTE
the Excel Options and click the Advanced tab. If the check box labeled Enable Automatic Percent
Entry is checked (the default setting), you can simply enter a normal value into a cell formatted to display as a
percent (for example, enter 12.5 for 12.5%). If this check box isn’t checked, you must enter the value as a deci-
mal (for example, .125 for 12.5%).
Excel automatically applies a built-in number format to a cell based on the following criteria:
n If a number contains a slash (/), it may be converted to a date format or a fraction format.
n If a number contains a hyphen (-), it may be converted to a date format.
n If a number contains a colon (:) or is followed by a space and the letter A or P, it may be converted
to a time format.
n If a number contains the letter E (in either uppercase or lowercase), it may be converted to scien-
tific notation or exponential format.
TIP To avoid automatic number formatting when you enter a value, preformat the cell with the
TIP
desired number format or precede your entry with an apostrophe. (The apostrophe makes the
entry text, so number formatting is not applied to the cell.)
Formatting numbers by using the Ribbon
The Number group on the Home tab of the Ribbon contains several controls that enable you to apply com-
mon number formats quickly. The Number Format drop-down control gives you quick access to 11 com-
mon number formats. In addition, the Number group contains some buttons. When you click one of these
buttons, the selected cells take on the specified number format. Table 24.1 summarizes the formats that
these buttons perform in the U.S. English version of Excel.
NOTE
NOTE Some of these buttons actually apply predefined styles to the selected cells. Access Excel’s
styles by using the style gallery, in the Styles group on the Home tab.
TABLE 24.1
Number-Formatting Buttons on the Ribbon
Button Name Formatting Applied
Accounting Adds a dollar sign to the left, separates thousands with a comma, and displays the value with
Number Format two digits to the right of the decimal point. This is a drop-down control, so you can select other
common currency symbols.
Percent Style Displays the value as a percentage, with no decimal places.
Comma Style Separates thousands with a comma and displays the value with two digits to the right of the
decimal place.
Increase Decimal Increases the number of digits to the right of the decimal point by one.
Decrease Decimal Decreases the number of digits to the right of the decimal point by one.
466