Applying Number Formatting
Number formatting refers to the process of changing the appearance of values contained in cells. Excel provides a wide variety of number formatting options. In the following sections, you see how to use many of Excel’s formatting options to quickly improve the appearance of your worksheets.
Tip
Remember that the formatting you apply works with the selected cell or cells. Therefore, you need to select the cell (or range of cells) before applying the formatting. Also remember that changing the number format does not affect the underlying value. Number formatting affects only the appearance.
Values that you enter into cells normally are unformatted. In other words, they simply consist of a string of numerals. Typically, you want to format the numbers so that they’re easier to read or are more consistent in terms of the number of decimal places shown.
Figure 13-8 shows a worksheet that has two columns of values. The first column consists of unformatted values. The cells in the second column are formatted to make the values easier to read. The third column describes the type of formatting applied.
Figure 13-8. Use numeric formatting to make it easier to understand what the values in the worksheet represent.
Tip
If you move the cell pointer to a cell that has a formatted value, the Formula bar displays the value in its unformatted state because the formatting affects only how the value appears in the cell—not the actual value contained in the cell.
Using automatic number formatting
Excel is smart enough to perform some formatting for you automatically. For example, if you enter 12.2% into a cell, Excel knows that you want to use a percentage format and applies it for you 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 dollar sign, the cell is formatted for currency (assuming that the dollar sign is your system currency symbol).
Tip
A handy default feature in Excel makes entering percentage values into cells easier. If a cell is formatted to display as a percent, you can simply enter a normal value (for example 12.5 for 12.5%). If this feature isn’t working (or if you prefer to enter the actual value for percents), open the Excel Options dialog box and click the Advanced choice in the list at the left. In the Editing Options section, locate the checkbox labeled Enable Automatic Percent Entry and remove the check mark.
Formatting numbers by using the Ribbon
The Home 
Number group in the Ribbon contains controls that let you quickly apply common number formats (see Figure 13-9).
Figure 13-9. You can find number formatting commands in the Number group of the Home tab.
The Number Format drop-down list contains 11 common number formats. Additional options include an Accounting Number Format drop-down list (to select a currency format), plus a Percent Style and a Comma Style button. In addition, the group contains a button to increase the number of decimal places, and another to decrease the number of decimal places.
When you select one of these controls, the active cell takes on the specified number format. You also can select a range of cells (or even an entire row or column) before clicking these buttons. If you select more than one cell, Excel applies the number format to all the selected cells.
Using shortcut keys to format numbers
Another way to apply number formatting is to use shortcut keys. Table 13-1 summarizes the shortcut key combinations that you can use to apply common number formatting to the selected cells or range. Notice that these Ctrl+Shift characters are all located together, in the upper-left part of your keyboard.
| 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 |
Formatting numbers using the Format Cells dialog box
In most cases, the number formats that are accessible from the Number group on the Home tab are just fine. Sometimes, however, you want more control over how your values appear. Excel offers a great deal of control over number formats through the use of the Format Cells dialog box, shown in Figure 13-10. For formatting numbers, you need to use the Number tab.
Figure 13-10. When you need more control over number formats, use the Number tab of the Format Cells dialog box.
You can bring up the Format Cells dialog box in several ways. Start by selecting the cell or cells that you want to format and then do the following:
Choose Home
Number and click the small Dialog Box Launcher icon.
Choose Home
Number, click the Number Format drop-down list, and select More Number Formats from the drop-down list.
Right-click and choose Format Cells from the shortcut menu.
Press the Ctrl+1 shortcut key.
The Number tab of the Format Cells dialog box displays 12 categories of number formats from which to choose. When you select a category from the list box, the right side of the tab changes to display the appropriate options.
The Number category has three options that you can control: the number of decimal places displayed, whether to use a thousand separator, and how you want negative numbers displayed. Notice that the Negative Numbers list box has four choices (two of which display negative values in red), and the choices change depending on the number of decimal places and whether you choose to separate thousands.
The top of the tab displays a sample of how the active cell will appear with the selected number format (visible only if a cell with a value is selected). After you make your choices, click OK to apply the number format to all the selected cells.
Caution
Selecting the Precision As Displayed option changes the numbers in your worksheets to permanently match their appearance onscreen. This setting applies to all sheets in the active workbook. Most of the time, this option is not what you want. Make sure that you understand the consequences of using the Set Precision As Displayed option.
Cross-Ref
Chapter 15 discusses ROUND and other built-in functions.
The following are the number-format categories, along with some general comments:
General: The default format; it displays numbers as integers, as decimals, or in scientific notation if the value is too wide to fit in the cell.
Number: Enables you to specify the number of decimal places, whether to use a comma to separate thousands, and how to display negative numbers (with a minus sign, in red, in parentheses, or in red and in parentheses).
Currency: Enables you to specify the number of decimal places, whether to use a currency symbol, and how to display negative numbers (with a minus sign, in red, in parentheses, or in red and in parentheses). This format always uses a comma to separate thousands.
When Numbers Appear to Add Up Incorrectly
Applying a number format to a cell doesn’t change the value—only how the value appears in the worksheet. For example, if a cell contains 0.874543, you may format it to appear as 87%. If that cell is used in a formula, the formula uses the full value (0.874543), not the displayed value (87%).
In some situations, formatting may cause Excel to display calculation results that appear incorrect, such as when totaling numbers with decimal places. For example, if values are formatted to display two decimal places, you may not see the actual numbers used in the calculations. But because Excel uses the full precision of the values in its formula, the sum of the two values may appear to be incorrect.
Several solutions to this problem are available. You can format the cells to display more decimal places. You can use the ROUND function on individual numbers and specify the number of decimal places Excel should round to. Or you can instruct Excel to change the worksheet values to match their displayed format. To do so, open the Excel Options dialog box and click the Advanced choice. Check the Set Precision As Displayed checkbox (which is located in the section named When Calculating This Workbook).
Accounting: Differs from the Currency format in that the currency symbols always line up vertically.
Date: Enables you to choose from several different date formats.
Time: Enables you to choose from several different time formats.
Percentage: Enables you to choose the number of decimal places and 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 number). This feature is useful for such items as part numbers.
Special: Contains four additional number formats (Zip Code, Zip Code +4, Phone Number, and Social Security Number).
Custom: Enables you to define custom number formats that aren’t included in any other category.
Tip
If a cell displays a series of pound signs (such as #########), it usually means that the column isn’t wide enough to display the value in the number format that you selected. Either make the column wider or change the number format.