Read an Excerpt
Chapter 3: Making It All Look Pretty
...Using the Format Cells Dialog BarExcel's Format->Cells command on the menu bar (Ctrl+ 1 for short) makes it a snap to apply a whole rash of different kinds of formatting to a cell selection. The Format Cells dialog box that this command calls up contains six tabs: Number, Alignment, Font, Border, Patterns, and Protection. In this chapter, I show you how to use the Number, Alignment, Font, Border, and Patterns tabs in the Format Cells dialog box to assign new number formats and fonts to your cells as well as to change their alignment, borders, and patterns. For information on how and when to use the options on the Protection tab, see Chapter 6.
The keystroke shortcut--Ctrl+ 1--that opens the Format Cells dialog box is one worth knowing. Many of you will be doing almost as much formatting as you do data entry in a worksheet. Just keep in mind that the shortcut is Ctrl plus the number 1 and not the function key F1. Further, you must use the 1 key on the top row of the regular typewriter keyboard, not the 1 located on your numeric keypad. Pressing Ctrl plus the 1 on the numeric keypad doesn't work any better than pressing Ctrl+F1.
Getting to know the number formatsAs Chapter 2 explains, how you enter values into a worksheet determines the type of number format they get. Here are some examples:
- If you enter a financial value complete with the dollar sign and two decimal places, Excel assigns a Currency number format to the cell along with the entry.
- If you enter a value representing a percentage as a whole number followed by the percent sign without any decimal places, Excel assigns to the cell the Percentage number format that follows this pattern along with the entry.
- If you enter a date (dates are values, too) that follows one of the built-in Excel number formats like 02/19/97 or 19-Feb-97, the program assigns a Date number format that follows the pattern of the date along with a special value representing the date.
Although it's fine to format values in this manner as you go along (and even necessary in the case of dates), you don't have to do it this way. You can always assign a number format to a group of values before or after you enter them. And, in fact, formatting numbers after you enter them is often the most efficient way to go because it's just a two-step procedure:
1. Select all the cells containing the values that need dressing up.
2. Select the number format you want to use either from the Formatting toolbar or the Format Cells dialog box.
Many times, you can use one of the tools on the Formatting toolbar--if not, you can select a number format from the Number tab in the Format Cells dialog box (Ctrl+1).
Even if you're a really good typist and prefer to enter each value exactly as you want it to appear in the worksheet, you'll still have to resort to using number formats to make the values that are calculated by formulas match the others you've entered. This is because Excel applies a General number format (which the Format Cells dialog box explains as "General format cells have no specific number format.") to all the values it calculates as well as any you enter that don't exactly follow one of the other Excel number formats.
The biggest problem with the General format is that it has the nasty habit of dropping all leading and trailing zeros from the entries. This makes it very hard to line up numbers in a column on their decimal points.
Figure 3-10 shows this sad state of affairs. The figure shows a sample worksheet with the first-quarter 2000 sales figures for Mother Goose Enterprises before any of the values have been formatted. Notice how the columns of monthly sales figures zig and zag? This is the fault of Excel's General number format; the only cure is to format the values with another more uniform number format.
Carrying your cells with the Currency StyleGiven the financial nature of most worksheets, you probably use the Currency format more than any other. This is a really easy format to apply because the Formatting toolbar contains a Currency Style tool that adds a dollar sign, commas between thousands of dollars, and two decimal places to any values in a selected range. If any of the values in the cell selection are negative, this Currency format displays them in parentheses (the way accountants like them).
Figure 3-10: First-quarter-sales zigging and zagging in columns B through E.
In Figure 3-11, only the cells containing totals are selected for the Currency format (cell ranges E3:E10 and B10:D10). This cell selection was then formatted with the Currency format by simply clicking the Currency Style button on the Formatting toolbar (the one with the $ icon, naturally).
Figure 3-11: The totals in the Mother Goose Sales table after click- ing the Currency Style button on the Formatting toolbar.
Note: Although you could put all the figures in the table into the Currency format to line up the decimal points, this would result in a superabundance of dollar signs in a fairly small table. In this example, I've decided that only the monthly and quarterly totals should be formatted A la Currency.
"Look Ma no more format overflow!"When I applied the Currency number format to the selection in the cell ranges of E3:E10 and B10:D10 in the sales table shown in Figure 3-11, Excel not only added dollar signs, commas between the thousands, a decimal point, and two decimal places to the highlighted values, but also, at the same time, automatically widened columns B, C, D, and E just enough to display all this new formatting. In earlier versions of Excel, you would have had to widen these columns yourself, and instead of the perfectly aligned numbers, you would have been confronted with columns of #######s in cell ranges E3:E10 and B10:D10. Such pound signs (where nicely formatted dollar totals should be) serve as overflow indicators, declaring that whatever formatting you've added to the value in that cell has added so much to the value's display that Excel can no longer display it within the current column width.
Fortunately, Excel eliminates the format overflow indicators when you're formatting the values in your cells by automatically widening their columns. The only time you'll ever run across these dreaded #######s in your cells will be when you take it upon yourself to manually narrow a worksheet column (see "Calibrating Columns," later in this chapter) to such an extent that Excel can no longer display all the characters in its cells with formatted values.
Carrying your cells with the Comma StyleThe Comma format offers a good alternative to the Currency format. Like Currency, the Comma format inserts commas in larger numbers to separate thousands, hundred thousands, millions, and, well, you get the idea.
This format also displays two decimal places and puts negative values in parentheses. What it doesn't display is dollar signs. This makes it perfect for formatting tables where it's obvious that you're dealing with dollars and cents or for larger values that have nothing to do with money.
The Comma format also works well for the bulk of the values in the sample first-quarter sales worksheet. Figure 3-12 shows this table after the cells containing the monthly sales for each Mother Goose company were formatted with the Comma format. To do this, select the cell range B3:D9 and click the Comma Style button (the one with the, [comma] icon, of course) on the Formatting toolbar.
Figure 3-12 shows how the Comma format takes care of the earlier alignment problem in the quarterly sales figures. Moreover, notice how the Commaformatted monthly sales figures align perfectly with the Currency-formatted monthly totals in row 10. If you look really closely (you might need a magnifying glass for this one), you see that these formatted values no longer abut the right edges of their cells; they've moved slightly to the left. The gap on the right between the last digit and the cell border is there to accommodate the right parenthesis in negative values, ensuring that they too align precisely on the decimal point...