Part I: Getting Started with Excel

Changing Text Alignment

The contents of a cell can be aligned horizontally and vertically. By default, Excel aligns numbers to the right and text to the left. All cells use bottom alignment, by default.

Overriding these defaults is a simple matter. The most commonly used alignment commands are in the Alignment group on the Home tab of the Ribbon. Use the Alignment tab of the Format Cells dialog box for even more options (see Figure 6.5).

FIGURE 6.5

The full range of alignment options are available on the Alignment tab of the Format Cells dialog box.

Choosing horizontal alignment options

Horizontal alignment options, which control how cell contents are distributed across the width of the cell (or cells), are available from the Format Cells dialog box:

•General: Aligns numbers to the right, aligns text to the left, and centers logical and error values. This option is the default alignment.

•Left: Aligns the cell contents to the left side of the cell. If the text is wider than the cell, the text spills over to the cell on the right. If the cell on the right isn’t empty, the text is truncated and not completely visible. Also available on the Ribbon.

122

Chapter 6: Worksheet Formatting

•Center: Centers the cell contents in the cell. If the text is wider than the cell, the text spills over to cells on either side if they’re empty. If the adjacent cells aren’t empty, the text is truncated and not completely visible. Also available on the Ribbon.

•Right: Aligns the cell contents to the right side of the cell. If the text is wider than the cell, the text spills over to the cell on the left. If the cell on the left isn’t empty, the text is truncated and not completely visible. Also available on the Ribbon.

•Fill: Repeats the contents of the cell until the cell’s width is filled. If cells to the right also are formatted with Fill alignment, they also are filled.

•Justify: Justifies the text to the left and right of the cell. This option is applicable only if the cell is formatted as wrapped text and uses more than one line.

•Center across Selection: Centers the text over the selected columns. This option is useful for precisely centering a heading over a number of columns.

•Distributed: Distributes the text evenly across the selected column.

Note

If you choose Left, Right, or Distributed, you can also adjust the Indent setting, which adds space between the cell border and the text. n

Figure 6.6 shows examples of text that uses three types of horizontal alignment: Left, Justify, and Distributed (with an indent).

On the CD

If you would like to experiment with text alignment settings, this workbook is available on the companion CD-ROM. The file is named text alignment.xlsx.

FIGURE 6.6

The same text, displayed with three types of horizontal alignment.

123

Part I: Getting Started with Excel

Choosing vertical alignment options

Vertical alignment options typically aren’t used as often as the horizontal alignment options. In fact, these settings are useful only if you’ve adjusted row heights so that they’re considerably taller than normal.

Vertical alignment options available in the Format Cells dialog box are

•Top: Aligns the cell contents to the top of the cell. Also available on the Ribbon.

•Center: Centers the cell contents vertically in the cell. Also available on the Ribbon.

•Bottom: Aligns the cell contents to the bottom of the cell. Also available on the Ribbon.

•Justify: Justifies the text vertically in the cell; this option is applicable only if the cell is formatted as wrapped text and uses more than one line. This setting can be used to increase the line spacing.

•Distributed: Distributes the text evenly vertically in the cell. This setting seems to have the same effect as Justify.

Wrapping or shrinking text to fit the cell

If you have text too wide to fit the column width but don’t want that text to spill over into adjacent cells, you can use either the Wrap Text option or the Shrink to Fit option to accommodate that text. The Wrap Text control is also available on the Ribbon.

The Wrap Text option displays the text on multiple lines in the cell, if necessary. Use this option to display lengthy headings without having to make the columns too wide, and without reducing the size of the text.

The Shrink to Fit option reduces the size of the text so that it fits into the cell without spilling over to the next cell. Usually, it’s easier to make this adjustment manually.

Note

If you apply Wrap Text formatting to a cell, you can’t use the Shrink to Fit formatting. n

Merging worksheet cells to create additional text space

Excel also enables you to merge two or more cells. When you merge cells, you don’t combine the contents of cells. Rather, you combine a group of cells into a single cell that occupies the same space. The worksheet shown in Figure 6.7 contains four sets of merged cells. For example, range C2:I2 has been merged into a single cell, and so has range J2:P2. In addition, ranges B4:B8 and B9:B13 have also been merged. In the latter two cases, the text direction has been changed (see “Displaying text at an angle,” later in this chapter).

124

Chapter 6: Worksheet Formatting

FIGURE 6.7

Merge worksheet cells to make them act as if they were a single cell.

You can merge any number of cells occupying any number of rows and columns. In fact, you can merge all 17,179,869,184 cells in a worksheet into a single cell — although I can’t think of any good reason to do so, except maybe to play a trick on a co-worker.

The range that you intend to merge should be empty, except for the upper-left cell. If any of the other cells that you intend to merge are not empty, Excel displays a warning. If you continue, all the data (except in the upper-left cell) will be deleted. To avoid deleting data, click Cancel in response to the warning.

You can use the Alignment tab of the Format Cells dialog box to merge cells, but using the Merge & Center control on the Ribbon (or on the Mini toolbar) is simpler. To merge cells, select the cells that you want to merge and then click the Merge & Center button. The cells will be merged, and the content in the upper-left cells will be centered horizontally. The Merge & Center button acts as a toggle. To unmerge cells, select the merged cells and click the Merge & Center button again.

After you merge cells, you can change the alignment to something other than Center.

The Home Alignment Merge & Center control contains a drop-down list with these additional options:

•Merge Across: When a multirow range is selected, this command creates multiple merged cells — one for each row.

•Merge Cells: Merges the selected cells without applying the Center attribute.

•Unmerge Cells: Unmerges the selected cells.

Displaying text at an angle

In some cases, you may want to create more visual impact by displaying text at an angle within a cell. You can display text horizontally, vertically, or at an angle between 90 degrees up and 90 degrees down.

125

Part I: Getting Started with Excel

From the Home Alignment Orientation drop-down list, you can apply the most common text angles. For more control, use the Alignment tab of the Format Cells dialog box. In the Format Cells dialog box (refer to Figure 6-5), use the Degrees spinner control — or just drag the pointer in the gauge. You can specify a text angle between –90 and +90 degrees.

Figure 6.8 shows an example of text displayed at a 45-degree angle.

Note

Rotated text may look a bit distorted onscreen, but the printed output is usually of much better quality. n

FIGURE 6.8

Rotate text for additional visual impact.

Controlling the text direction

Not all languages use the same character direction. Although most Western languages are read left to right, other languages are read right to left. You can use the Text Direction option to select the appropriate setting for the language you use. This command is available only in the Alignment tab of the Format Cells dialog box.

Don’t confuse the Text Direction setting with the Orientation setting (discussed in the previous section). Changing the text orientation is common. Changing the text direction is used only in very specific situations.

Note

Changing the Text Direction setting won’t have any effect unless you have the proper language drivers installed on your system. For example, you must install Japanese language support from the Office CD-ROM to use right-to-left text direction Japanese characters. n

New Feature

Use the Language tab of the Excel Options dialog box to determine which languages are installed. n

126