Formatting Cells

Effective worksheet formatting is crucial when you present important business information. Formatting the contents of a cell doesn't change how Excel stores the data internally; rather, it changes how the information looks on your screen and how it appears in print. In this section, you'll learn the following techniques for formatting the data in cells:

• How to change the vertical and horizontal alignment of data in a cell
• How to change number formats
• How to change the font, text color, and background color
• How to add decorative borders and patterns to cells
• How to apply combinations of formatting effects using the AutoFormat command

SEE ALSO

---

For information about creating conditional formatting based on the values stored in worksheet cells, see "Creating Conditional Formatting"

Changing Alignment

The gateway to Excel's formatting commands is the Cells command on the Format menu. When you select a range of cells in the worksheet and choose the Cells command, the Format Cells dialog box appears, as shown in Figure 17-1. The Format Cells dialog box contains six tabs of formatting options that you can use to adjust the appearance of information in worksheet cells. You use the Alignment tab, shown in Figure 17-1, to change the alignment and orientation of information in worksheet cells.

FIGURE 17-1. Alignment tab of the Format Cells dialog box.

You can also use the Formatting toolbar to set the most popular alignment options.

TIP

---

You can display the Format Cells dialog box by selecting the range of cells you want to format, right-clicking the range, and then choosing Format Cells from the shortcut menu.

Adjusting Horizontal Alignment

To adjust the horizontal (side-to-side) alignment of data in a range of cells, select the range you want to align, choose Format Cells, click the Alignment tab, and choose one of the seven alignment options in the Horizontal drop-down list box. General alignment (the default) aligns text to the left edge of the cell and numbers to the right edge. This basic alignment will be suitable for most of your cells' entries. Left, Center, and Right align text to the left, center, and right edges of the cell, respectively. You can also use toolbar buttons to set these common formatting options. (See Figure 17-2.)

The Fill option repeats the data in a cell to fill all the cells selected in the row, although the data is still stored only in the first cell. The Justify option aligns text evenly between the cell borders when longer cell entries wrap within a cell.

The final option in the Horizontal drop-down list box is Center Across Selection, which centers the data in the first cell across a range of selected columns. For example, to center the contents of cell A1 across columns A, B, C, and D, select cells A1 through D1, choose Cells from the Format menu, click the Alignment tab, and select the Center Across Selection option in the Horizontal list box. Alternatively, you can select cells A1 through D1, and then click the Center Across Selection button on the toolbar. (See Table 17-1)

FIGURE 17-2. Use the Formatting toolbar to set the more common cell formats.

By the way, Center Across Selection won't display text over occupied cells, so for the command to function properly, cells B1 through D1 must be empty. When you choose the command, Excel aligns the data to the center of the selection, but the information is still stored internally in the leftmost cell. By using these seven horizontal alignment options, you can create virtually any tabular formatting effect. Figure 17-3 shows the result of several types of horizontal alignment formatting.

Adjusting Vertical Alignment

The Alignment tab also allows you to adjust the vertical (top to bottom) alignment in cells. The default vertical alignment is Bottom, meaning the cell contents are aligned to the bottom of the cell. However, if you change the row height to add additional white space to cells (you'll learn how to do this later in the chapter), you might enhance the appearance of your worksheet by selecting the Top or Center option in the Vertical drop-down list box (see Figure 17-1). If you have multiple lines of text in a cell, you can also use the Justify alignment option, which spreads complete lines of text evenly between the left and right edges of the cell.

Adjusting Text Orientation

A powerful formatting option on the Alignment tab is the Orientation setting, which changes the text orientation in the selected cells from the default horizontal orientation to an exact angle (measured in degrees) on a 180-degree semicircle. This slick feature lets you create a ledger that includes any of the attractive column labels shown in Figure 17-4. (See step-by-step instructions coming up.)

FIGURE 17-3. The Horizontal alignment options can create a variety of useful formatting effects.

One nonintuitive option in this Orientation setting is the button you use to "stack" letters vertically in worksheet cells, the effect that's demonstrated in cell B3 in Figure 17-4. To create this neat effect, click the vertical bar on the Alignment tab containing the word Text. (See Figure 17-1) When this bar is highlighted, Excel will create the stacked-letter effect shown in cell B3, and the text orientation will be set to zero (0) degrees. This is a useful and visually interesting effect for column labels, especially if the text is five or less characters long.

FIGURE 17-4. The Orientation option on the Alignment tab lets you specify an exact angle for your text just like using a protractor in art class.

ON THE WEB

---

The Format.xls example is on the Running Office 2000 Reader's Corner page. For information about connecting to this Web site, read the Introduction.

To change the text angle for the labels in a group of cells, follow these steps:

1. Select the cells you want to reorient.
2. Choose Cells from the Format menu, and then click the Alignment tab.
3. In the Orientation box, click the angle you want to use for the text on the alignment compass. Excel will show you a preview of the orientation you select.

TIP

---

Using the Degrees scroll box, you can specify an exact text angle from -90 degrees to +90 degrees.

4. Click OK on the Alignment tab to reorient the selected cells.

Changing Number Formats

Excel allows you to change the appearance of your numeric entries by using several formatting options on the Number tab of the Format Cells dialog box, shown in Figure 17-5. To change the number format for a range of cells, select the cells, choose Cells from the Format menu, click the Number tab, choose a category in the Category list box, and then, if necessary, pick the format you want to use. To help you make your selection, Excel displays formatting examples when more than one option is available to choose from. You can also specify the number of decimal places in most of the formats, and in certain cases, the presence of the currency symbol ($) for dollar amounts and of the comma separator (,) for numbers over 1,000.

The Date category, shown in Figure 17-5, supports two new four-digit year patterns to resolve any confusion over the specified century in the date. The Excel 2000 team added these formats to address the year 2000 problem, which arises when the century is ambiguous in dates used for billing, collection, and other financial transactions.

FIGURE 17-5. The Number tab lets you change the format of your numeric entries.

(In other words, you now have the option of printing 3/14/2001 rather than 3/14/01.)

Table 17-1 describes the purpose of each numeric format category on the Number tab, and shows an example of each.

TABLE 17-1. The Numeric Formats on the Number Tab

Category	Purpose	Examples
General	The default number format, right-aligned, with no special formatting codes.	15.75 5425
Number	A flexible numeric format that can be enhanced with commas, variable decimal places, and (for negative numbers) colors and parentheses.	3.14159 (1,575.32)
Currency	A general monetary format that can be enhanced with dollar signs, variable decimal places, and (for negative numbers) colors and parentheses. Excel 2000 now supports the Euro format.	$75.35 ($1,234.10)
Accounting	A special currency format designed to align columns of monetary values along the decimal point. (The dollar sign appears along the left side of the cell.)	$ 75.00 $ 500.75
Date	A general-purpose date format that displays calendar dates in several standard styles.	1/15/2000 Jan-15-00
Time	A general-purpose time format that displays chronological values in several standard styles.	3:30 PM 15:30:58
Percentage	A format that multiplies the value in the selected cell by 100 and displays the result using a percentage symbol (%).	175% 15.125%
Fraction	A format that expresses numbers as fractional values. (You specify the number of digits and denominator.)	1/8 2/16
Scientific	An exponential notation for numbers that contain a lot of digits.	1.25E-08 4.58E+12
Text	A format that treats numbers like text. (It aligns them on the left edge of the cell and displays them exactly as they are entered.)	500.35 12345.0
Special	A collection of useful formats that follow an alphanumeric pattern, including Zip Code, Phone Number, and Social Security Number.	98109-1234 535-65-2342
Custom	A list of all standard formats (such as formats for foreign currency) and any custom numeric formats you create. (See next section.)	INV-0075 £150.50

In addition to the options on the Number tab, you can use the Formatting toolbar buttons shown in Figure 17-6 to format the numeric entries in selected cells.

FIGURE 17-6. The buttons controlling numeric formatting on Excel's Formatting toolbar.

Creating a Custom Number Format

If you routinely enter numeric values in a format that Excel doesn't recognize, you should consider creating a custom number format that you can use to organize nonstandard numeric entries in a consistent and visually appealing manner. For example, you might want to create a custom number format for part numbers or invoice numbers that includes both letters and numbers, or a monetary format that features international currency symbols. To create this type of custom format, choose Cells from the Format menu, click the Number tab, select Custom from the Category list box, and then either:

• modify an existing format by selecting it in the list box and then editing it in the Type box, or
• enter a new format in the Type box using characters and one or more of Excel's special formatting symbols.

Figure 17-7 shows the Custom option on the Number tab after we've created a currency format using the British pound (£) symbol. (You enter the £ symbol by holding down the Alt key and typing 0163 on the numeric keypad. Make sure Num Lock is on.)

To help you organize your custom number format, Excel lets you enter placeholders for digits, special symbols, and other useful characters using the formatting symbols shown in Table 17-2. You can also enter characters (such as currency symbols, or useful abbreviations such as Part or INV) to be included in the format. For example, to create a custom part number format that translates the cell value 25 to the formatted part number Part AA-025, enter the code Part AA-000 in the Type text box. To use the custom format later, click the Number tab, click the Custom Category, and then double-click the custom format in the list box. You can also delete custom formats by highlighting the format and clicking the Delete button. (Excel will not let you delete the default formats.)

Changing Excel's Default Currency Symbol

To change the currency symbol used in Excel and other Windows-based applications, open the Control Panel, double-click the Regional Settings icon, and then select the country you want to use in the drop-down list box on the Regional Settings tab. For example, if you want Excel to display the British pound sign (£) as its default currency symbol, choose English (British) in the drop-down list box, and click OK. Windows will reconfigure your Windows-based applications so that the pound sign (£) is used rather than the dollar sign ($) in all your documents.

If you want to change only particular currency symbols on a worksheet, enter them individually in cells or customize a number format to display them as needed. If standard alphabetical letters or other symbols normally represented on your keyboard are used for the symbol, type them in the appropriate format. For example, the currency symbol for German Marks is DM, and the symbol follows the monetary value, such as 550.57 DM. If a special ANSI code is required for the symbol, make sure Num Lock is turned on, hold down the Alt key, and type the appropriate four-digit code. The following table lists some of the most popular currency symbols and their ANSI codes:

Country	Denomination	Symbol	ANSI Code
United Kingdom	Pound	£	Alt+0163
Japan	Yen	¥	Alt+0165
United States	Cent	¢	Alt+0162

FIGURE 17-7. To create a custom number format, select the Custom option and modify an existing format or create a new one.

TABLE 17-2. Useful Formatting Characters for Building Custom Number Formats

Character	Purpose	Example	Number Entered	Result
#	Creates a placeholder for significant digits, rounding to fit if necessary.	##.###	50.0048 2.30	50.005 2.3
0	Rounds numbers to fit like the # character, but fills any empty positions with zeros to align numbers and to fill all specified positions	00.00	50.1 5	50.10 05.00
?	Also rounds numbers to fit, but fills any empty positions with spaces rather than extra zeros (if necessary) to align numbers and fill positions.	??.??	5.6 .70 73.27	5.6 .70 73.27
"text"	Adds the characters specified to the value in the cell.	"ID " ##	75 2	ID 75 ID 2
comma (,)	Separates thousands in numbers.	#,###	5600	5,600
$, -, +, :, /, (, ), space	Standard formatting characters. Each appears as specified in the custom numeric format.	$#.000	500.5	$500.500
%	Multiplies value by 100 and adds percentage symbol.	##%	.25	25%

Changing Text Font and Text Color

To emphasize headings and distinguish different kinds of information in your worksheet, you can use the Font tab of the Format Cells dialog box, shown in Figure 17-8. The Font tab lets you change the font, style, size, and color of the data in selected cells. It also controls whether data is underlined and allows you to create special formatting effects such as strikethrough, superscript, and subscript. The fonts displayed on the Font tab depend on the type of printer you are connected to and the fonts installed on your system. Fonts preceded by a TrueType symbol are TrueType fonts, designed to appear in print exactly as they do on the screen. You might also see fonts on the Font tab that have tiny printer icons in front of them; these are scalable fonts, which will look sharp when printed but might not display accurately on the screen. (The size will probably be right but the character shapes might not exactly match.) You can see both typeface symbols in Figure 17-8.

FIGURE 17-8. You can change the font and text color for selected cells on the Font tab of the Format Cells dialog box.

In addition to selecting formatting options on the Font tab, you can also use Excel's Formatting toolbar to change several font and text color options. Figure 17-9 shows the buttons you can use to increase your formatting speed.

FIGURE 17-9. Selected text cells can be formatted using these Formatting toolbar buttons.

To change the font and text color formatting in one or more cells, follow these steps:

1. Select the cells you want to format. (To format individual characters in a cell, see the sidebar on formatting which follows)
2. Choose Cells from the Format menu, and then click the Font tab.
3. Use the list boxes and check boxes on the Font tab to adjust the font characteristics you want to change. Use the Preview window to verify that the appearance is what you want.

NOTE

---

If you want to return to the default font setting, select the Normal Font check box on the Font tab.

4. When you're finished, click OK.

Adding Borders to Cells

Another useful technique for highlighting specific information in a worksheet is adding borders to important cells using the Border tab of the Format Cells dialog box, shown in Figure 17-10. The Border tab lets you place a solid or dashed line along one or more cell edges, allowing you to create a summary line or boxed effect, or even an interesting combination of squares and rectangles. You can also create diagonal lines in worksheet cells, so that your spreadsheets can contain triangles, "X" patterns, or other intriguing shapes.

FIGURE 17-10. The Border tab lets you underline or box selected cells.

Formatting Individual Characters in a Cell

You can also format individual characters in a cell if the entry contains text. This useful feature lets you emphasize important words in a long entry or create dramatic effects in headings. For example, you can italicize one word in a cell containing many words or change the first letter of a heading to a larger point size. To format individual characters in a cell, double-click the cell and select the characters you want to change. Then choose the Cells command on the Format menu and change the attributes you want, or click the appropriate buttons on the Formatting toolbar. When you press Enter, the formatting will take effect.

To specify borders for the cells you have selected, first click one of the 14 line styles in the Style box (the None style removes existing borders). Then click the lines you want on the preview diagram in the Border box, or click the buttons along the left and bottom of the Border box for the same result. As a shortcut, you can also use one of the three border styles in the Presets box: None (to remove an existing border), Outline (to place a border around the outside edge of the selected cells), or Inside (to draw lines along the inside edges of selected cells). You can also change the color of the border by opening the Color list box.

Figure 17-11 shows an example of the Outline button at work. In the example, we selected cells A3:E9 in the worksheet, chose the Cells command on the Format menu and clicked the Border tab, clicked a heavy border style and the color black, and then clicked the Outline button. The resulting border appears only around the perimeter of the range we selected. If we wanted to outline each of the cells as well as the outer perimeter, we could have selected both the Outline button and the Inside button. Or, to provide a line that runs continuously under the table headings in Figure 17-11, we would select the headings, and then select just the bottom border line in the weight we want.

FIGURE 17-11. Clicking the Outline button adds a box around the selected text.

ON THE WEB

---

The OrderFrm.xls example is on the Running Office 2000 Reader's Corner page.

Making Borders Faster: Using the Borders Toolbar Button

A handy alternative to using the Border tab is clicking the Borders button on Excel's Formatting toolbar. In this case, using the Formatting toolbar is really much faster than using the Format Cells dialog box, and the toolbar also gives you single-step access to some formatting designs that require multiple steps using the Format Cells dialog box. To use the Borders button, select the range of cells you want to highlight, click the Borders button, and then pick from the 12 border options that appear on the Borders toolbar, as shown in the illustration below. The 12 options are actually border formatting shortcuts, and a few of them feature a combination of aesthetically pleasing styles. For example, the bottom left style—useful for creating worksheet tables—places a light border on the top of the cell and a heavier border on the bottom of the cell. Learning to use these border styles will save you considerable time as you format your worksheets.

Adding Shading to Cells

To create effects that complement the borders produced by the Border tab, the Patterns tab of the Format Cells dialog box (see Figure 17-12) lets you add a background color and optional colored pattern to one or more cells in your worksheet. By default, the color you select has no pattern added to it, so you see a solid color in your cells. However, you can also add a background pattern and change its color from the default black to any of the colors displayed in the Pattern drop-down list. To add color and shading to cells, select the cells you want to format, choose Cells from the Format menu, click the Patterns tab, pick a Cell shading color, and then pick a shading pattern in a second color from the Pattern drop-down list box.

Color can make a striking addition to your worksheet, and is ideal—if used in moderation—for documents created to be viewed electronically, such as status reports, departmental ledgers, sales projections, and the like. If you don't have a color printer, your color shading effects will be converted to gray tones when you print. (Not to worry, Excel usually does a good job at this.) To see what this conversion is like, you can view your worksheet using the Print Preview command on the File menu. Figure 17-13 shows a sample worksheet with color and pattern shading.

FIGURE 17-12. The Patterns tab lets you add color background shading and patterns to your cells.

FIGURE 17-13. Color and pattern shading look nice, even if you don't have a color printer.

You can also use the Fill Color button on the Formatting toolbar to change the background color (but not the pattern) used in worksheet cells. To remove the existing color in worksheet cells, click the No Fill option.

Copying Formatting Using the Format Painter Button

Occasionally, you'll need to copy the formats from one cell to another cell without copying the data in the cell. For example, you might want to copy the cell formats you used to create a 14-point, bold, Times New Roman heading that has a thick border to a second heading you're creating later in the worksheet. Excel allows you to accomplish this task by using the Format Painter button on the Standard toolbar. To copy formatting using the Format Painter button, follow these steps:

1. Select the cell from which you want to copy formats.
2. Click the Format Painter button on the toolbar. A marquee will appear around the selected cell and a paintbrush will be added to the mouse pointer.
3. Select the range of cells you want to change to the new format.

If you decide you don't like the format you copied, remember that you can choose Undo from the Edit menu to remove it. To remove the selection marquee, press Escape.

TIP

---

If you want to copy formats to cells or ranges that are not contiguous, double-click the Format Painter button, and then select the cells you want to format one by one. When you've finished, click the Format Painter button again.