CHAPTER

Using Custom

Number Formats

551

Part IV: Using Advanced Excel Features

One more thing to keep in mind: If you use the Find and Replace dialog box (Home Editing Find & Select Find), characters that are displayed as a result of number formatting (for example, a currency symbol) are not searchable by default. To be able to locate information based on formatting, use the Search In Value option in the Find and Replace dialog box.

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 assumes 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 precede your value with a currency symbol, Excel formats the cell for currency.

Note

You have an option when it comes to entering values into cells formatted as percentages. Access the Excel Options and click the Advanced tab. If the Enable Automatic Percent Entry check box is selected (the default setting), you can simply enter a normal value into a cell that has been formatted to display as a percent (for example, enter 12.5 for 12.5%). If this check box isn’t selected, you must enter the value as a decimal (for example, .125 for 12.5%). n

Excel automatically applies a built-in number format to a cell based on the following criteria:

•If a number contains a slash (/), it may be converted to a date format or a fraction format.

•If a number contains a hyphen (-), it may be converted to a date format.

•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.

•If a number contains the letter E (uppercase or lowercase), it may be converted to scientific notation or exponential format.

Tip

To avoid automatic number formatting when you enter a value, preformat the cell with the 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.) n

Formatting numbers by using the Ribbon

The Number group on the Home tab of the Ribbon contains several controls for applying common number formats quickly. The Number Format drop-down control gives you quick access to 11 common 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.

552

Chapter 24: Using Custom Number Formats

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. n

TABLE 24.1

Number-Formatting Buttons on the Ribbon

Using shortcut keys to format numbers

Another way to apply number formatting is to use shortcut keys. Table 24.2 summarizes the shortcut key combinations that you can use to apply common number formatting to the selected cells or range. Notice that these are the shifted versions of the number keys along the top of a typical keyboard.

TABLE 24.2

553

Part IV: Using Advanced Excel Features

Using the Format Cells dialog box to format numbers

For maximum control of number formatting, use the Number tab in the Format Cells dialog box. You can access this dialog box in any of several ways:

•Click the dialog box launcher at the bottom right of the Home Number group.

•Choose Home Number Number Format More Number Formats.

•Press Ctrl+1.

The Number tab in the Format Cells dialog box contains 12 categories of number formats from which to choose. When you select a category from the list box, the right side of the dialog box changes to display appropriate options.

Here are the number-format categories, along with some general comments:

•General: The default format; it displays numbers as integers, decimals, or in scientific notation if the value is too wide to fit into the cell.

•Number: Specify the number of decimal places, whether to use your system thousands separator (for example, a comma) to separate thousands, and how to display negative numbers.

•Currency: Specify the number of decimal places, choose a currency symbol, and display negative numbers. This format always uses the system thousands separator symbol (for example, a comma) to separate thousands.

•Accounting: Differs from the Currency format in that the currency symbols always line up vertically, regardless of the number of digits displayed in the value.

•Date: Choose from a variety of date formats and select the locale for your date formats.

•Time: Choose from a number of time formats and select the locale for your time formats.

•Percentage: Choose the number of decimal places; always displays a percent sign.

•Fraction: Choose from among nine fraction formats.

•Scientific: Displays numbers in exponential notation (with an E): 2.00E+05 = 200,000. You can choose the number of decimal places to display to the left of E.

•Text: When applied to a value, causes Excel to treat the value as text (even if it looks like a value). This feature is useful for such items as numerical part numbers and credit card numbers.

•Special: Contains additional number formats. The list varies, depending on the Locale you choose. For the English (United States) locale, the formatting options are Zip Code, Zip Code +4, Phone Number, and Social Security Number.

•Custom: Define custom number formats not included in any of the other categories.

554