Appendix B: Using Custom Number Formats | Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Although Excel provides a good variety of built-in number formats, you may find that none of these suits your needs. This appendix describes how to create custom number formats and provides many examples.

About Number Formatting

By default, all cells use the General number format. This is basically a "what you type is what you get" format. If the cell is not wide enough to show the entire number, the General format rounds numbers with decimals and uses scientific notation for large numbers. In many cases, you may want to format a cell with something other than the General number format.

The key thing to remember about number formatting is that it affects only how a value is displayed. The actual number remains intact, and any formulas that use a formatted number use the actual number.

Note

An exception to this rule occurs if you specify the Precision as Displayed option on the Calculation tab of the Options dialog box. If that option is in effect, formulas will use the values that are actually displayed in the cells. In general, using this option is not a good idea because it changes the underlying values in your worksheet.

One more thing to keep in mind: If you use Excel's Find and Replace dialog box (displayed by choosing Home Editing Find & Select Find, characters that are displayed a result of number formatting (for example, a currency symbol) are not searchable.

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 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 a percentage. Access 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 decimal (for example, .125 for 12.5%).

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 (in either uppercase or lowercase), it may be converted to scientific notation or exponential format.

Tip

To avoid automatic number formatting when you enter a value, pre-format 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.)

Formatting Numbers by Using the Ribbon

The Number group on the Home tab of the Ribbon contains several controls that enable you to apply 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 button. When you click one of these buttons, the selected cells take on the specified number format. Table B-1 summarizes the formats that these buttons perform in the U.S. English version of Excel.

Table B-1: NUMBER-FORMATTING BUTTONS ON THE RIBBON
Open table as spreadsheet
Button Name	Formatting Applied
Accounting Number Format	Adds a dollar sign to the left, separates thousands with a comma, and displays the value with 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.

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 of the Home tab.

Using Shortcut Keys to Format Numbers

Another way to apply number formatting is to use shortcut keys. Table B-2 summarizes the shortcut key combinations that you can use to apply common number formatting to the selected cells or range.

Table B-2: NUMBER-FORMATTING KEYBOARD SHORTCUTS
Open table as spreadsheet
Key Combination	Formatting Applied
Ctrl+Shift+~	General number format (that is, unformatted values).
Ctrl+Shift+$	Currency format with two decimal places. (Negative numbers appear in parentheses.)
Ctrl+Shift+%	Percentage format with no decimal places.
Ctrl+Shift+∘	Scientific notation number format with two decimal places.
Ctrl+Shift+#	Date format with the day, month, and year.
Ctrl+Shift+@	Time format with the hour, minute, and AM or PM.
Ctrl+Shift+!	Two decimal places, thousands separator, and a hyphen for negative values.

Using the Format Cells Dialog Box to Format Numbers

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

Click the dialog box selector in the Home Number group.
Choose Home Number Number Format More Number Formats.
Press Ctrl+1.

The Number tab of 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.

Following is a list of 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: Enables you to 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: Enables you to specify the number of decimal places, to choose a currency symbol, and to 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: Enables you to choose from a variety of date formats and select the locale for your date formats.
Time: Enables you to choose from a number of time formats and select the locale for your time formats.
Percentage: Enables you to choose the number of decimal places; always displays a percent sign.
Fraction: Enables you to choose from among nine fraction formats.
Scientific: Displays numbers in exponential notation (with an E): 2.00E+05 = 200,000. 2.05E+05 = 205,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: Enables you to define custom number formats not included in any of the other categories.

Note

If the cell displays a series of hash marks after you apply a number format (such as #########), it usually means that the column isn't wide enough to display the value by using the number format that you selected. Either make the column wider (by dragging the right border of the column header) or change the number format. A series of hash marks also can mean that the cell contains an invalid date or time.