The default format for cells is the first one on the list—the General format. When cells are formatted as General, Excel makes its best guess to display numbers and text. Numbers are generally displayed as they are typed, with a few exceptions. Figure 14.2 shows some examples of General formatting, and the following rules apply to General formatted numbers:

Figure 14.2. With General formatting, Excel attempts to guess how to display numbers and text.

You can go beyond Excel's automatic number formatting by choosing the Number data type. This data type lets you choose a consistent format to use to display numbers no matter how they are entered. Figure 14.3 shows the options available when using the Number format. You can set the following options:

Figure 14.3. You can choose a variety of display options for number data using Number formatting.

Excel is particularly adept at displaying currency values. Symbols for all major currency types are available, and you can adjust decimals and display of negative numbers just as with the General number type, as shown in Figure 14.4. These are your choices:

Figure 14.4. Currency formatting gives you choices for a currency symbol along with the other number formatting options.

Accountants need to display currency values for tabulation in columns; as such, the data is easier to read if numbers and currency symbols line up. With the Accounting format option, the currency symbol is aligned to the left of the cell and the value to the right, as shown in Figure 14.5.

With the Accounting data type, negative numbers are displayed in parentheses instead of with a negative sign. The following other options are available:

Figure 14.5. Use the Accounting data type to neatly align currency values in columns.

One of the areas where Excel shines is in the options it allows for formatting date and time values. Several numeric and textual date and time variations are possible. Select the Date item to choose date formats, as shown in Figure 14.6; select Time to display only the time portion. Some options that display both date and time are available for both types.

Unfortunately, unlike its Windows cousin, Excel 2008 is not very adept at displaying date formats for locales that use the Day/Month/Year arrangement rather than the Month/Day/Year format prevalent in the United States. You can get around this by using a Custom format. See "Creating a custom format" in this chapter for more information.

It is often convenient to display fractional values as a percentage. The Percentage data type makes this extremely easy to do. Simply select the Percentage type, as shown in Figure 14.7. The only other option available is the number of decimal places to use.

The Percentage data type takes the value entered in the cell and multiplies it by 100 for display, along with a percent sign (%). For example, the value 0.50 is displayed as 50%, and 2.0 is displayed as 200%.

Understanding Date Systems

Excel dates and times are represented internally as numeric decimal values, with the whole number portion to the left of the decimal representing the date and the decimal portion representing the time. Unfortunately, a long-standing incompatibility between Mac and Windows versions of Excel has caused some problems over the years. Excel on the Mac traditionally uses January 2, 1904, as the basis for its date values. The date portion simply counts the number of days since then. For example, January 1, 2009, is represented internally by the value 38352. Excel for Windows, however, uses January 1, 1900, as the basis for its dates.

This used to cause major headaches when sharing workbooks between Windows and Macs. Fortunately, this distinction is now largely academic, because Excel allows you to set the base date system to use for a workbook. Although Excel 2007 for Windows still uses 1900 and Excel 2008 for the Mac still uses 1904, an attribute is now saved with the workbook to indicate which system is in use, and it is recognized by either version. You can change the date system in use by choosing Excel

You can choose a base date system in the Calculation preferences under Workbook options.

Figure 14.6. Date and Time formatting options allow you display dates and time values just the way you want them.

Figure 14.7. Percentages are a useful way to display fractional data.

With the Fraction data type, you can choose to display decimal data as the nearest fraction, with a whole number portion and the part after the decimal shown as a numerator/denominator fraction. Select the Fraction data type, and choose one of the fractional values shown in Figure 14.8. If the number cannot be represented entirely by the chosen value, it is rounded for display; the underlying value is never changed.

Figure 14.8. The Fraction formatting type allows yet another way to display decimal data.

Scientists and engineers often work with very large or very small numbers, and the number of digits can become unwieldy and hard to see at a glance. With such numbers, the ultimate precision of the value is not as important, so E notation was developed to represent these values in a small amount of space.

With E notation, the decimal point is moved to a more convenient location for display, and the number of places it is moved is displayed after the letter E, along with a sign indicating the direction the decimal point moves. For very large numbers, the decimal point is moved to the left, and the E value is shown with a plus sign. For very small numbers, the decimal point is moved to the right, and the E value is shown with a negative sign. Table 14.1 shows some examples of E notation. Figure 14.9 shows the Scientific formatting options.

Figure 14.9. The Scientific data type allows large numbers to be represented in E notation shorthand.

These number formatting options are nice, but sometimes you just want a cell to display data exactly the way you enter it. The Text formatting option, which is the default for any data that Excel can't determine how to apply another data type to, can be used to force all values to be displayed as entered, with no formatting changes. Figure 14.10 shows the Text formatting option.

Excel supports some formatting options that just don't fit anywhere else or merit their own category, so they've been tossed into the Special formatting category. These options, shown in Figure 14.11, include Zip code (and Zip +4), phone numbers, and social security numbers. These options are all U.S. formats.

Figure 14.10. You can choose to display numbers as text, which forces them to be displayed exactly as entered. You have no options for text formatting here.

Figure 14.11. Special formatting is available for Zip codes, phone numbers, and social security numbers.

You've looked through all the provided formats and you can't find one that formats numbers (or dates) exactly the way that you like. Fear not, because Excel gives you the Custom formatting option so you can roll your own number formats. With the Custom type, shown in Figure 14.12, you can choose one of the provided basic types as a starting point and modify it to fit exactly the way you want. The Custom options are quite flexible; in fact, they are used to define all the existing formatting types behind the scenes.

The syntax for describing a custom format is a bit arcane, so it's helpful to see a table with the options and examples. Table 14.2 gives a list of codes and what they do. Table 14.3 lists date and time codes.

Each custom format can have up to four sections, separated by a semicolon (;). These sections, from left to right, set formatting for Positive numbers, Negative numbers, Zero values, and Text values. You do not need to use all four sections; if you use one, it is used for all formatting. With two sections, the first is used for positive and zero values, and the second for negative numbers. Formats can be quite complex overall. Table 14.4 shows some examples that you might find useful.

Figure 14.12. The Custom formatting option allows you to create your own format.

Setting the horizontal and vertical text alignment can make a difference in how data is displayed in cells with expanded heights or widths, or when a single cell contains lots of data. You can choose from two alignment options: horizontal and vertical.

Horizontal alignment

The default horizontal alignment, General, lets Excel try to determine the best way to display data. General alignment displays text aligned to the left and numbers aligned to the right. You can override this setting to have data conform to the alignment of your choice. You have these options:

• General: Uses the default alignment that Excel assigns: text aligned to the left, numbers to the right.

• Left (indent): Aligns to the left, with an optional indentation value that you can choose in the Indent box to the right of the alignment drop-down list.

• Center: Centers data in the cell.

• Right: Aligns data to the right of the cell.

• Fill: Repeats data to fill up empty space in the cell.

• Justify: Spaces text in a paragraph, with the exception of the last line, to fill the cell from left to right.

• Center across selection: If multiple cells are selected, this option can be used to center the text based on the width of the selected columns. This is useful for centering titles over multiple columns, for example.

Figure 14.13. Use the Alignment tab of the Format Cells dialog box to change how text is displayed in a cell.

When text is too large to fit into a cell, Excel needs to know how to display it. By default with no text control, the data is displayed overlapping the cell next to it. This can be confusing and messy. Figure 14.14 shows some examples of text alignment formatting. These are your options:

The final alignment option for text lets you rotate it through 90 degrees vertical going up, to 90 degrees vertical going down. Simply enter an angle (positive values lead upward, negative values downward), or drag the orientation indicator to where you want.

Figure 14.14. The various text alignment options give you lots of flexibility.

The most important choice on this tab is the font to use. The right font can make the difference between readable text in a worksheet and a frustrating experience. Choose a font from the Font list. If you know the name of the font you want to use, you can type it into the Font box directly. The list scrolls as you type so you can quickly locate the correct font.

Figure 14.15. The Font tab of the Format Cells dialog box lets you customize how text is displayed.

After you've chosen a font, you need to choose a style for it. Your options are Regular (no style), Italic, Bold, or Bold Italic.

Finally, choose a size for the font. Most fonts allow from 6 to 72 points in size. If a value you want to use is not listed, you can enter it directly in the Size box.

Choosing a font is simply the first step. You can apply additional formatting attributes to your data as well. These include various text effects as well as underline styles that are unique to Excel.

Nothing changes a worksheet from drab to interesting more than adding color. Colors can be used to make certain data stand out. For example, you could make cells in which you intend to have values manually entered appear in one color, cells that are locked appear in another color, and formulas in yet another color.

Excel offers a palette of 56 colors to choose from; simply use the Color drop-down list, shown in Figure 14.16, to make your choice. Keep in mind any background color that you may want to use. If you use a dark background, you should stick with lighter font colors. Darker colors are okay on white or lighter background colors. See "Setting a background pattern" for information on setting background color.