The quickest way to apply currency formatting is by clicking Currency in the Number drop-down list in the Number group on the Home tab, as shown in Figure 9-35. The Currency format is similar to the Number format that precedes it in the drop-down list, except it also includes the default currency symbol for your locale. Notice that most of the commands listed here display little previews showing you what the contents of the active cell will look like if you click that command.

Figure 9-35. The contents of the selected cell are previewed below each command in the Number drop-down list.

For additional currency formatting options, select the Currency category in the Format Cells dialog box, which offers a similar set of options as the Number category (refer to Figure 9-34) but adds a drop-down list of worldwide currency symbols. Besides clicking the dialog box launcher in the Number group on the Home tab to display the Format Cells dialog box, you can also select the More Number Formats command at the bottom of the Number drop-down list shown in Figure 9-35.

Using the Decimal Buttons

You can change the number of displayed decimal places in any selected cell or range at any time using two buttons—Increase Decimal and Decrease Decimal—in the Number group on the Home tab:

The Increase Decimal button displays an arrow pointing to the left, and the Decrease Decimal button displays an arrow pointing to the right, which might seem backward to those of us in the left-to-right/smaller-to-larger world of Western culture, but of course these buttons address what happens only on the right side of the decimal point. Each click adds or subtracts one decimal place from the displayed value. Interestingly, although you can specify up to 30 decimal places using the Format Cells dialog box, you can increase the number of decimal places to a maximum of 127, one click at a time, using the Increase Decimal button.

The most-often-used Accounting format is directly available on the Home tab on the ribbon, using the Accounting Number Format button in the Number group. Clicking this button applies a standard two-decimal-place format with comma separators and currency symbols to the selected cells. Clicking the arrow button adjacent to the Accounting Number Format button displays a menu providing access to a few additional currency symbols, as shown in Figure 9-36.

Figure 9-36. The $ button applies a standard Accounting format and offers a few optional currency symbols.

The Accounting formats address the needs of accounting professionals, but they benefit the rest of us as well. When you use one of these formats with the Single Accounting or Double Accounting font formats (to add underlines to your numbers), you can easily create profit and loss (P&L) statements, balance sheets, and other schedules that conform to generally accepted accounting principles (GAAP). The Accounting formats correspond roughly to the Currency format in appearance—you can display numbers with or without your choice of currency symbols and specify the number of decimal places. However, the two formats have some distinct differences. The rules governing the Accounting formats are as follows:

Typically, when creating a GAAP-friendly worksheet of currency values, you would use currency symbols only in the top row and in the totals row at the bottom of each column of numbers. This makes good sense because using dollar signs with every number would make for a much busier table. The middle of the table is then formatted using a compatible format without currency symbols, as shown in Figure 9-37.

Figure 9-37. It is standard practice to use currency symbols only in the top and bottom rows of a table.

Luckily, Excel makes it easy for you to format this way by using buttons in the Number group on the Home tab. Despite seemingly incompatible button names, both the Accounting Number Format button and the Comma Style button apply Accounting formats adhering to the rules described earlier. So, to format the numeric entries in the table shown in Figure 9-37, select the first and last rows, click the Accounting Number Format button, then select all the cells in between, and click the Comma Style button. (We then selected all the numeric cells in the table and clicked the Decrease Decimal button twice to hide all the decimal values.)

Not surprisingly, using the Percentage format displays numbers as percentages. The decimal point of the formatted number, in effect, moves two places to the right, and a percent sign appears at the end of the number. For example, if you choose a percentage format without decimal places, the entry 0.1234 is displayed as 12%; if you select two decimal places, the entry 0.1234 is displayed as 12.34%. Remember that you can always adjust the number of displayed decimal places using the Increase Decimal and Decrease Decimal buttons.

An interesting (and helpful) quirk about percentage formats is that they behave differently depending on whether you type a number and then apply the format or type a number in a previously formatted cell. For example, Figure 9-38 shows two cells formatted as percentages. We typed the same number—22.33—in each cell, but only cell A1 was previously formatted with the Percentage format; we clicked the Percent Style button after typing the value in cell A2.

Figure 9-38. When using percentages, it makes a difference whether you format before or after typing values.

As you can see, it makes a world of difference which way you do this. So, why is this behavior helpful? For example, if a worksheet contains a displayed value of 12% and you need to change it to 13%, typing 13 in the cell would seem to make sense, even though this is technically wrong. It is not particularly intuitive to type .13 (including the leading decimal point). Usability studies show that most people would type 13 in this situation, which would logically result in a displayed value of 1300% (if not for the quirky behavior), so Excel assumes that you want to display 13%. If you apply the Percentage format to a range of cells that already contain values (or formulas that result in values), check all the cells afterward to make sure you get the intended results.

The formats in the Fraction category in the Format Cells dialog box, shown in Figure 9-39, display fractional numbers as actual fractions rather than as decimal values. As with all number formats, the underlying value does not change despite the displayed value of the fraction.

Figure 9-39. Excel provides many fraction-formatting options.

You can generate some wild, nonstandard fractions unless you apply constraints using options in the Format Cells dialog box. Here is how Excel applies different fraction formats:

The Scientific format displays numbers in exponential notation. For example, a two-decimal Scientific format (the default) displays the number 98765432198 as 9.88E+10 in a standard-width cell. The number 9.88E+10 is 9.88 times 10 to the 10th power. The symbol E stands for exponent, a synonym here for 10 to the nth power. The expression “10 to the 10th power” means 10 times itself 10 times, or 10,000,000,000. Multiplying this value by 9.88 gives you 98,800,000,000, an approximation of 98,765,432,198. Increasing the number of decimal places (the only option available for this format) increases the precision and will likely require a wider cell to accommodate the displayed value.

You can also use the Scientific format to display very small numbers. For example, in a standard-width cell this format displays 0.000000009 as 9.00E–09, which equates to 9 times 10 to the negative 9th power. The expression “10 to the negative 9th power” means 1 divided by 10 to the 9th power, 1 divided by 10 nine times, or 0.000000001. Multiplying this number by nine results in our original number, 0.000000009.

Applying the Text format to a cell indicates that the entry in the cell is to be treated as text, even if it’s a number. For example, a numeric value is ordinarily right-aligned in its cell. If you apply the Text format to the cell, however, the value is left-aligned as if it were a text entry. For all practical purposes, a numeric constant formatted as text is still considered a number because Excel is capable of recognizing its numeric value anyway.

The four Special formats shown in Figure 9-40 are a result of many requests from users. These generally noncalculated numbers include two ZIP code formats, a phone number format (complete with the area code in parentheses), and a Social Security number format. Using each of these Special formats, you can quickly type numbers without having to type the punctuation characters.

The following are guidelines for using the Special formats:

Figure 9-40. Excel provides several frequently requested formats in the Special category.

Most number formats you need are available through commands and buttons on the ribbon, but you can use the Format Cells dialog box to accomplish minor feats of formatting that might surprise you. We’ll use the Custom category on the Number tab in the Format Cells dialog box, shown in Figure 9-41, to create custom number formats using special formatting codes. (To quickly display the Format Cells dialog box, press Ctrl+1.) Excel adds new formats to the bottom of the list of formatting codes in the Type list, which also includes built-in formats. To delete a custom format, select the format in the Format Cells dialog box, and click Delete. You cannot delete built-in formats.

Creating New Number Formats The quickest way to start creating a custom format is to use one of the built-in formats as a starting point. Here’s an easy way to build on an existing format, as well as to see what the codes in the Type list mean:

Figure 9-41. Use the Custom category to create new formats using special codes.

For example, to create a format that displays the date and time with the longest available format for day, month, and year, start by typing a date in a cell, and then select it. In the Custom category in the Format Cells dialog box, edit the format in the Type text box to read dddd, mmmm dd, yyyy – hh:mm AM/PM (including spaces and commas), and then click OK. Figure 9-42 shows the result.

Figure 9-42. We created a custom format by typing codes in the Type text box.

You can create any number format using the codes in Table 9-1.

Table 9-2 lists the built-in formats and indicates how these codes relate to the other categories on the Number tab. (This table does not list date and time codes, which are covered in Chapter 15.)

Creating Four-Part Formats Within each custom format definition, you can specify completely different formats for positive, negative, zero, and text values. You can create custom formats with as many as four parts, separating the portions by semicolons—positive number; negative number; zero; text. Figure 9-43 shows how three different formats are constructed using codes.

Figure 9-43. You can create your own four-part formats.

Among the built-in formats, only the Accounting formats use all four parts, as shown in Figure 9-43, which breaks down each part of the third Accounting format in Table 9-2. The following are some guidelines for creating multipart formats:

Adding Color to Formats You can also use the Number formats to change the color of selected cell entries. For example, you might use color to distinguish categories of information or to make totals stand out. You can even create formats that assign different colors to specific numeric ranges so that, for example, all values greater than or less than a specified value appear in a different color.

"Amount due: "$#,##0.00_);"Credit: "($#,##0.00);"Let's call it even. ";"Please
note: "@

"Part # "###-####

To change the color of an entry, type the name of the new color in brackets in front of each segment of code. For example, if you want to apply a blue Currency format with two decimal places, edit the $#,##0.00_);($#,##0.00) format as follows:

[Blue]$#,##0.00_);($#,##0.00)

When you apply this format to a worksheet, positive and zero values appear in blue, and text and negative values appear as usual, in black. The following simple four-part format code displays positive values in blue, negative values in red, zero values in yellow, and text in green (with no additional number formatting specified).

[Blue];[Red];[Yellow];[Green]

You can specify the following color names in your formats: Black, Blue, Cyan, Green, Magenta, Red, White, and Yellow. You can also specify a color as COLORn, where n is a number in the range 1 through 16. Excel selects the corresponding color from your worksheet’s current 16-color palette.

#,##0.00 _$;[Red]#,##0.00 _$

Using Custom Format Conditional Operators You can create custom formats that are variable. To do so, you can add a conditional operator to the first two parts of the standard four-part custom format. This, in effect, replaces the positive/negative formats with either/or formats. The third format becomes the default format for values that don’t match the other two conditions (the “else” format). You can use the conditional operators <, >, =, <=, >=, and <> with any number to define a format.

For example, suppose you are tracking accounts receivable balances. To display accounts with balances of more than $50,000 in blue, negative values in parentheses and in red, and all other values in the default color, create this format:

[Blue][>50000]$#,## 0.00_);[Red][<0]($#,##0.00);$#,##0.00_)

Using these conditional operators can also be a powerful aid if you need to scale numbers. For example, if your company produces a product that requires a few milliliters of a compound for each unit and you make thousands of units every day, you need to convert from milliliters to liters and kiloliters when you budget the use of this compound. Excel can make this conversion with the following numeric format:

[>999999]#,##0,,"kl";[>999]##,"L";#"ml"

The following table shows the effects of this format on various worksheet entries:

As you can see, using a combination of conditional formats, the thousands separator, and text with spaces within quotation marks can improve both the readability and the effectiveness of your worksheet—and without increasing the number of formulas.

$#,##0.00;;

;;;

The Align Left, Center, and Align Right buttons on the ribbon correspond to three of the options in the Horizontal drop-down list on the Alignment tab in the Format Cells dialog box: Left (Indent), Center, and Right (Indent). These options align the contents of the selected cells, overriding the default cell alignment. Figure 9-46 shows the Horizontal alignment options in action, all of which we’ll discuss in detail in the following sections.

Figure 9-46. Use the Horizontal alignment options to control the placement of text from left to right.

Indenting Cell Contents The Increase Indent button simultaneously applies left alignment to the selected cells and indents the contents by the width of one character. (One character width is approximately the width of the capital X in the Normal cell style.) Each click increments the amount of indentation by one. The adjacent Decrease Indent button does just the opposite, decreasing the indentation by one character width with each click.

In the Format Cells dialog box, the corresponding options are Left (Indent) and Right (Indent), shown in Figure 9-46. These are linked to the adjacent Indent control, whose displayed value is normally zero—the standard left-alignment setting. Each time you increase this value by one, the entry in the cell begins one character width to the right. For example, in Figure 9-46, row 2 is formatted with no left indent, row 3 with a left indent of 1, and row 4 with a left indent of 2. The maximum indent value you can use is 250.

Distributing Cell Contents Using the Distributed (Indent) option in the Horizontal drop-down list, you can position text fragments contained in a cell with equal spacing within the cell. For example, in Figure 9-46, we first merged cells A8:B8 into one cell, then typed the word Distributed three times in the merged cell, and then applied the Distributed (Indent) horizontal alignment. The result shows that Excel expanded the spaces between words in equal amounts to justify the contents within the cell.

Centering Text Across Columns The Center Across Selection option in the Horizontal text alignment drop-down list centers text from one cell across all selected blank cells to the right or to the next cell in the selection that contains text. For example, in Figure 9-46, we applied the Center Across Selection format to cells A7:B7. The centered text is actually in cell A7.

Center Across Selection vs. Merge & Center

Although the result might look similar to that of the Merge & Center button on the Home tab, the Center Across Selection alignment option does not merge cells. When you use Center Across Selection, the text from the leftmost cell remains in its cell but is displayed centered across the entire selected range. Notice in the following figure that the selection shading shows that cells A1 and B1 are still two separate cells. The Merge & Center button creates a single cell in place of all the selected cells. Although Merge & Center merges rows and columns of cells, Center Across Selection works only on rows.

In the above figure, the text “Center Across Selection” actually spans two active cells, while the text “Merge & Center” is in a single cell that was created by selecting cells A2:B2 and clicking the Merge & Center button. Either method allows you to change column widths; the centering readjusts automatically. If you type anything in cell B1, the centered text “retreats” to cell A1; if you subsequently clear cell B1, the text is re-centered across the two cells until you clear the format from both cells. You cannot type anything in cell B2 because it essentially no longer exists after being merged with cell A2. For more information, see Merging and Unmerging Cells on page 365.

Filling Cells with Characters The Fill option in the Horizontal alignment drop-down list repeats your cell entry to fill the width of the column. For example, in Figure 9-46, cells A9:B9 contain the single word Fill and a space character, with the Fill alignment format applied. Only the first cell in the selected range needs to contain text. Excel repeats the text to fill the range. Like the other Format commands, the Fill option affects only the appearance, not the underlying contents, of the cell.

Wrapping Text in Cells If you type a label that’s too wide for the active cell, Excel extends the label past the cell border and into adjacent cells—provided those cells are empty. If you click the Wrap Text button on the Home tab (or the Wrap Text option on the Alignment tab in the Format Cells dialog box), Excel displays your label entirely within the active cell. To accommodate it, Excel increases the height of the row in which the cell is located and then wraps the text onto additional lines within the same cell. As shown in Figure 9-46, cell A10 contains a multiline label formatted with the Wrap Text option.

Justifying Text in Cells The Alignment tab in the Format Cells dialog box provides two justify options—one in the Horizontal drop-down list and one in the Vertical drop-down list. The Horizontal Justify option not only forces text in the active cell to align flush with the right margin, as shown in cell B10 in Figure 9-46, but also wraps text within the cell and adjusts the row height accordingly.

The Vertical Justify option performs essentially the same task as its Horizontal counterpart, except it adjusts cell entries relative to the top and bottom of the cell rather than the sides, as shown in cell E3 of Figure 9-47.

The Justify Distributed option becomes available only when you select one of the Distributed options in either the Horizontal drop-down list or the Vertical drop-down list. It combines the effect of the Justify option with that of the Distributed option not only by wrapping text in the cell and forcing it to align flush right but also by spacing the contents of the cell as evenly as possible within each wrapped line of text.

Figure 9-47. Use the Vertical options to control placement of text from top to bottom.

Clicking the Orientation button in the Alignment group on the Home tab displays the menu shown in Figure 9-48, offering common orientation options.

Figure 9-48. Use the Orientation menu to rotate or stack text in a cell.

The Orientation area on the Alignment tab in the Format Cells dialog box contains additional controls, letting you change the angle of cell contents to read at any angle from 90 degrees counterclockwise to 90 degrees clockwise.

A Cool Application of Angled Text

Many times the label at the top of a column is much wider than the data stored in it. You can use the Wrap Text option to make a multiple-word label narrower, but sometimes that’s not enough. Vertical text is an option, but it can be difficult to read and takes a lot of vertical space. Try using rotated text and cell borders:

Here’s how to do it:

1. Select the cells you want to format, and click the dialog box launcher in the Font group on the Home tab to display the Format Cells dialog box.

2. Click the Border tab, and apply vertical borders to the left, right, and middle of the range.

3. Click the Alignment tab, and use the Orientation controls to select the angle you want. (It’s usually best to select a positive angle from 30 to 60 degrees.)

4. In the Horizontal Text Alignment drop-down list, select Center, and then click OK. Excel rotates the left and right borders along with the text.

5. Drag down the bottom border of the row 1 header (the line between 1 and 2) to make it deep enough to accommodate the labels without wrapping.

6. Select all the active columns, and double-click any one of the lines between the selected column headers (for example, the line between the column letters C and D) to shrink all the columns to their smallest possible width.

Using the Angle Counterclockwise command on the Orientation button’s menu (in the Alignment group on the Home tab) rotates the text to +45 degrees for you, but because we wanted to apply borders and alignment options as well, using the Format Cells dialog box was a more efficient method.

Excel automatically adjusts the height of the row to accommodate vertical orientation unless you manually set the row height either before or after changing text orientation. Cell G3 in Figure 9-47 shows what happens when you click the tall, skinny Text button on the left side of the Orientation area. Although the button is labeled Text, you can also apply this “stacked letters” effect to numbers and formulas.

The angle controls let you rotate text to any point in a 180-degree arc. You can use either the Degrees box at the bottom or the large dial above it to adjust text rotation. To use the dial, click and drag the Text pointer to the angle you want; the number of degrees appears in the spinner below. You also can click the small up and down arrows in the Degrees box to increment the angle one degree at a time from horizontal (zero), or you can highlight the number displayed in the Degrees box and type a number from –90 through 90. Cells H3:K3 in Figure 9-47 show some examples of rotated text.

The Shrink To Fit check box on the Alignment tab in the Format Cells dialog box reduces the size of the font in the selected cell until the contents can be completely displayed in the cell. This is useful when you have a worksheet in which adjusting the column width to allow a particular cell entry to be visible has undesirable effects on the rest of the worksheet or where angled text, vertical text, and wrapped text aren’t feasible solutions. In Figure 9-49, we typed the same text in cells A1 and A2 (and increased the font size for readability) and applied the Shrink To Fit option to cell A2.

Figure 9-49. The Shrink To Fit alignment option reduces the font size until the cell contents fit within the cell.

The Shrink To Fit format is dynamic and readjusts if you change the column width, either increasing or decreasing the font size as needed. The assigned size of the font does not change; therefore, no matter how wide you make the column, the font will not expand beyond the assigned size.

The Shrink To Fit option can be a good way to solve a problem, but keep in mind that this option reduces the font to as small a size as necessary. If the cell is narrow enough and the cell contents long enough, the result might be too small to read.

Adding background images to worksheets is easy. Click the Page Layout tab on the ribbon, and click the Background button. A standard Windows file-management dialog box appears, from which you can open most types of image files, located anywhere on your computer or network. Excel then applies the graphic image to the background of the active worksheet, as shown in Figure 9-59.

Here are some tips for working with background images:

Figure 9-59. Add a background graphic to any worksheet.

If the standard column width isn’t enough to display the complete contents of a cell, one of the following will occur:

To change column widths using the mouse, drag the lines between column headings. As you drag, the width of the column and the number of pixels appear in a ScreenTip, as shown in Figure 9-61. This figure also illustrates how to change the width of multiple columns at the same time: Drag to select column headings; alternatively, hold down Ctrl, and click headings to select nonadjacent columns. Then, when you drag the line to the right of any selected column, all the selected column widths change simultaneously.

Figure 9-61. The cursor looks like a double-headed arrow when you adjust column width or row height with the mouse.

The height of a row always changes dynamically to accommodate the largest font used in that row. Thus, you don’t usually need to worry about characters being too tall to fit in a row. Adjusting row height is the same as adjusting column width—just drag one of the lines between row headings.

To restore the default height of one or more rows, select any cell in those rows, and click AutoFit Row Height on the Format menu on the Home tab. Unlike column width, you cannot define a standard row height. The AutoFit command serves the same function, returning empty rows to the standard height needed to accommodate the default font and fitting row heights to accommodate the tallest entry. When you create or edit a multiline text entry using the Wrap Text button or the Justify option on the Alignment tab in the Format Cells dialog box, Excel automatically adjusts the row height to accommodate it.

The spreadsheet grid is arguably the most versatile type of document, and the ability to merge cells makes it all the more versatile. Select the cells you want to merge, and click the arrow to the right of the Merge & Center button in the Alignment group on the Home tab to display the menu shown in Figure 9-62.

Figure 9-62. The Merge & Center button offers a variety of merge commands.

When you merge cells, you end up with a single cell that comprises the original cells. If in the worksheet shown in Figure 9-63, we were to select cells A4:B5 and click the Merge Across command, the result would be two merged cells, A4 and A5, each spanning two columns. Here are the differences between the Merge & Center commands:

Figure 9-63. Most of the cells in the top five rows of this worksheet, and a couple in the sixth row, are merged in various combinations.

Figure 9-63 shows the same worksheet shown in Figure 9-62 after merging cells A1:B3, C1:F3, G1:J5, A4:B4, A5:B5, D4:F4, D5:F5, and D6:E6. We had to shuffle some of the text, before merging so that we wouldn’t lose it to the merging process. For example, the text in the original range G1:J5 was unevenly spaced because of the different row heights needed to accommodate the text in cells A1 and C2. To eliminate this problem, we used the Merge Cells command on the range A1:B3, we used the Merge & Center command on the ranges C1:F3 and G1:J5, and then we reentered the text.

When you merge cells, the new big cell uses the address of the cell in the upper-left corner, as shown in Figure 9-63. Cell A1 is selected, as you can see in the Name box. (In the figure, we also expanded the formula bar to show the three rows of text in the merged cell.) The headings for rows 1, 2, and 3 and columns A and B are highlighted, which would ordinarily indicate that the range A1:B3 is selected. For all practical purposes, however, cells A2:A3 and B1:B3 no longer exist. The other merged cells, or the subsidiary cells, act like blank cells when referred to in formulas and return zero (or an error value, depending on the type of formula).

Merging cells obviously has interesting implications, considering that it seems to violate the grid—one of the defining attributes of spreadsheet design. That’s not as bad as it sounds, but keep in mind these tips: