In the Format Cells dialog box, the Number tab lets you control how Excel displays numeric data in a cell. Excel gives you a lengthy list of predefined formats (as shown in Figure 13-4). Remember, Excel uses number formats when the cell contains only numeric information. Otherwise, Excel simply ignores the number format. For example, if you enter Half past 12 in a column full of times, Excel considers it plain ol’ text—although, under the hood, the cell’s numerical formatting stays put, and Excel uses it if you change the cell content to a time.

When you create a new spreadsheet, every cell starts out with the same number format: General. This format comes with a couple of basic rules:

Figure 13-4.  You can learn about the different number formats by selecting a cell that already has a number in it, and then choosing a new number format from the Category list (Home → Cells → Format → Format Cells). When you do so, Excel uses the Format Cells dialog box to show how it’ll display the number if you apply that format. In this example, you see that the cell value, 5.18518518518519, will appear as 5.19E+00, which is scientific notation with two decimal places.

As you saw in Chapter 10, the way you type in a number can change a cell’s formatting. For example, if you enter a number with a currency symbol, the number format of the cell changes automatically to Currency. Similarly, if you enter three numbers separated by dashes (-) or backward slashes (/), Excel assumes you’re entering a date, and adjusts the number format to Date.

However, rather than rely on this automatic process, it’s far better just to enter ordinary numbers and set the formatting explicitly for the whole column. This approach prevents you from having different formatting in different cells (which can confuse even the sharpest spreadsheet reader), and it makes sure you get exactly the formatting and precision you want. You can apply formatting to the column before or after you enter the numbers. And it doesn’t matter if a cell is currently empty; Excel still keeps track of the number format you’ve applied.

Different number formats provide different options. For example, if you choose the Currency format, then you can choose from dozens of currency symbols. When you use the Number format, you can choose to add commas (to separate groups of three digits) or parentheses (to indicate negative numbers). Most number formats let you set the number of decimal places.

The following sections give a quick tour of the predefined number formats available in the Format Cells dialog box’s Number tab. Figure 13-5 gives you an overview of how different number formats affect similar numbers.

Figure 13-5.  Each column contains the same list of numbers. Although this worksheet shows an example for each number format (except dates and times), it doesn’t show all your options. Each number format has its own settings (like the number of decimal places) that affect how Excel displays data.

Excel gives you lots of options here. You can use everything from compact styles like 3/13/07 to longer formats that include the day of the week, like Sunday, March 13, 2007. Time formats give you a similar range of options, including the ability to use a 12-hour or 24-hour clock, show seconds, show fractional seconds, and include the date information.

To format dates and times, first open the Format Cells dialog box shown in Figure 13-7 (Home → Cells → Format → Format Cells). Choose Date or Time from the column on the left and then choose the format from the list on the right. Date and Time both provide a slew of options.

Figure 13-6. The all-around quickest way to apply a number format is to see some cells, and then, from the number format list, choose an option. Best of all, you see a small preview of what the value in the first selected cell will look like if you apply the format.

Figure 13-7.  Excel gives you dozens of different ways to format that modify the date’s appearance depending on the regional settings of the computer viewing the Excel file, or you can choose a fixed date format. stick to the U.S. standard. Instead, choose the appropriate region from the Locale list box. Each locale provides its own set of customized date formats.

Excel has essentially two types of date and time formats:

The first group (the formats that rely on a computer’s regional settings) is the smallest. It includes two date formats (a compact, number-only format and a long, more descriptive format) and one time format. In the Type list, these formats are at the top and have an asterisk next to them.

The second group (the formats that are independent of a computer’s regional settings) is much more extensive. In order to choose one of these formats, you first select a region from the Locale list, and then you select the appropriate date or time format. Some examples of locales include “English (United States)” and “English (United Kingdom).”

If you enter a date without specifically formatting the cell, Excel usually uses the short region-specific date format. That means that the order of the month and year vary depending on the regional settings of the current computer. If you incorporate the month name (for example, January 1, 2007), instead of the month number (for example, 1/1/2007), Excel uses a medium date format that includes a month abbreviation, like 1-Jan-2007.

You wouldn’t ever want to perform mathematical operations with some types of numeric information. For example, it’s hard to image a situation where you’d want to add or multiply phone numbers or Social Security numbers.

When entering these types of numbers, therefore, you may choose to format them as plain old text. For example, you could enter the text (555) 123-4567 to represent a phone number. Because of the parentheses and the dash (‐), Excel won’t interpret this information as a number. Alternatively, you could just precede your value with an apostrophe (') to explicitly tell Excel that it should be treated as text (you might do this if you don’t use parentheses or dashes in a phone number).

But whichever solution you choose, you’re potentially creating more work for yourself because you have to enter the parentheses and the dash for each phone number you enter (or the apostrophe). You also increase the likelihood of creating inconsistently formatted numbers, especially if you’re entering a long list of them. For example, some phone numbers may end up entered in slightly similar but somewhat different formats, like 555-123-4567 and (555)1234567.

To avoid these problems, apply Excel’s Special number format (shown in Figure 13-8), which converts numbers into common patterns. And lucky you: In the Special number format, one of the Type options is Phone Number (other formats are for Zip codes and Social Security numbers).

As you learned in the previous chapter, Excel automatically aligns cells according to the type of information you’ve entered. But what if this default alignment isn’t what you want? Fortunately, in the Format Cells dialog box, the Alignment tab lets you easily change alignment as well as control some other interesting settings, like the ability to rotate text.

Excel lets you control the position of content between a cell’s left and right borders, which is known as the horizontal alignment. Excel offers the following choices for horizontal alignment, some of which are shown in Figure 13-9:

Vertical alignment controls the position of content between a cell’s top and bottom border. Vertical alignment becomes important only if you enlarge a row’s height so that it becomes taller than the contents it contains. To change the height of a row, click the bottom edge of the row header (the numbered cell on the left side of the worksheet), and drag it up or down. As you resize the row, the content stays fixed at the bottom. The vertical alignment setting lets you adjust the cell content’s positioning.

Excel gives you the following vertical alignment choices, some of which are shown in Figure 13-9:

If you have a cell containing a large amount of text, you may want to increase the row’s height so you can display multiple lines. Unfortunately, you’ll notice that enlarging a cell doesn’t automatically cause the text to flow into multiple lines and fill the newly available space. But there’s a simple solution: just turn on the “Wrap text” checkbox (on the Alignment tab of the Format Cells dialog box). Now, long passages of text flow across multiple lines. You can use this option in conjunction with the vertical alignment setting to control whether Excel centers a block of text, or lines it up at the bottom or top of the cell. Another option is to explicitly split your text into lines. Whenever you want to insert a line break, just press Alt+Enter, and start typing the new line.

Finally, the Alignment tab allows you to rotate content in a cell up to 180 degrees, as shown in Figure 13-10. You can set the number of degrees in the Orientation box on the right of the Alignment tab. Rotating cell content automatically changes the size of the cell. Usually, you’ll see it become narrower and taller to accommodate the rotated content.

Figure 13-10.  Here’s a worksheet that demonstrates one of Excel’s most commonly used formatting tricks (merged cells), along with one of its most exotic (rotated text).

As in almost any Windows program, you can customize the text in Excel, applying a dazzling assortment of colors and fancy typefaces. You can do everything from enlarging headings to colorizing big numbers. Here are the individual font details you can change:

To change font settings, first highlight the cells you want to format, choose Home → Cells → Format → Format Cells, and then click the Font tab (Figure 13-11).

Figure 13-11. Here’s an example of how to apply an exotic font through the Format Cells dialog box. Keep in mind that when displaying data, and especially numbers, sans-serif fonts are usually clearer and look more professional than serif fonts. (Serif fonts have little embellishments, like tiny curls, on the ends of the letters; sans-sarif fonts don’t.) Arial, the default spreadsheet font, is a sans-sarif font.

Rather than heading to the Format Cells dialog box every time you want to tweak a font, you can use the ribbon’s handy shortcuts. The Home → Font section displays buttons for changing the font and font size. You also get a load of tiny buttons for applying basics like bold, italic, and underline, applying borders, and changing the text and background colors. (Truth be told, the formatting toolbar is way more convenient for setting fonts because its drop-down menu shows a long list of font names, whereas the font list in the Format Cells dialog box is limited to showing an impossibly restrictive six fonts at a time. Scrolling through that cramped space is like reading the phone book on index cards.)

Without a doubt, the most useful ribbon formatting feature is Live Preview, a frill that shows you the result of a change before you’ve even applied it. Figure 13-12 shows Live Preview in action.

Figure 13-12.  Right now, this spreadsheet’s creator is just thinking about using the stylish Garamond font for this table. However, the moment she hovers over Benguiat (higher up in the font list), Excel switches the currently selected cells on the worksheet to that font, providing a preview of the change. The best part: When she moves the mouse pointer away, the formatting disappears instantaneously. To make the changes stick, all she needs to do is click the font. This Live Preview feature works with font names, font sizes, and colors.

The best way to call attention to important information isn’t to change fonts or alignment. Instead, place borders around key cells or groups of cells and use shading to highlight important columns and rows. Excel provides dozens of different ways to outline and highlight any selection of cells.

Once again, the trusty Format Cells dialog box is your control center. Just follow these steps:

If you need to add a border around a cell or group of cells, the Format Cells dialog box’s Border tab does the trick (see Figure 13-14). However, you could have a hard time getting the result you want, particularly if you want to add a combination of different borders around different cells. In this situation, you have a major project on your hand that requires several trips back to the Format Cells dialog box.

Fortunately, there’s a little-known secret that lets you avoid the hassle: Excel’s Draw Border feature. The Draw Border feature lets you draw border lines directly on your worksheet. This process is a little like working with a painting program. You pick the border style, color, and thickness, and then you drag to draw the line between the appropriate cells. When you draw, Excel applies the formatting settings to each affected cell, just as if you’d used the Borders tab.

Here’s how it works: