TOC BACK FORWARD HOME

Microsoft® Excel 97 Quick Reference

- 7 -
Formatting

To enhance the appearance and improve readability of your worksheets, you can format the information in worksheet cells either before or after you enter the data. You can change the fonts or apply attributes such as boldface, italic, underline, borders, patterns, and colors. You also can apply numeric and date and time formats. Many of the formats you will use most often are accessible on the Formatting toolbar. Additional formatting options are available on the Format menu.

Alignment: Aligning Text Horizontally

Excel automatically aligns entries within a cell, according to the data you enter. When you enter text in a cell, Excel aligns the data to the left of the cell. When you enter numbers, Excel aligns them to the right. You can override this automatic alignment, however, and specify how you want data aligned: to the left, to the right, centered or justified within a cell, or centered across a range of cells.

In most cases, you will probably want to change the alignment of column headings so that the text is centered in each cell. If your worksheet data includes a title, you may also want to center the title over the worksheet data. (See also "Alignment: Centering Across Multiple Cells.")

Steps

  1. Select the cell or range containing data you want to align. Choose Format, Cells; then click the Alignment tab.

  2. Select one of the alignment options in the Horizontal drop-down list; then click OK.

You can quickly align data by selecting a cell or range, then clicking one of the following buttons on the Formatting toolbar: Align Left, Center, Align Right, or Merge and Center.

Alignment: Aligning Text Vertically

By default, Excel aligns text and numbers at the bottom edge of a cell. You can change the vertical alignment of cell data so that the data is displayed at the top edge or centered in a cell, or justified between the top and bottom edges. You may want to center text vertically in a cell, for example, if you want to place a border surrounding the cell.

Steps

  1. Select the cell or range containing data you want to align. Choose Format, Cells; then click the Alignment tab.

  2. Select one of the alignment options in the Vertical drop-down list; then click OK.

Alignment: Centering Across Multiple Cells

Excel enables you to center worksheet titles easily using two different methods. The Center Across Selection option in the Alignment tab of the Format Cells dialog box centers the title within a range of selected cells. You also can merge a range of selected cells into a single cell, and then center a title within the larger, merged cell. You can use the Merge and Center button on the Formatting toolbar to merge a selected range of cells and also center text within the new merged cell with a single mouse click.

When you merge a range of cells, the original cells become one large cell and you can no longer work in the individual cells. If your title is in cell A1, for example, you would select the range A1:A5 and click the Merge and Center button if you want the title to appear centered over the range A1:A5. After you merge the cells, the new merged cell is cell A1, and cells A2, A3, A4, and A5 no longer exist. An advantage to using the Merge Cells option is that you can align the contents in the merged cell any way you want. If you use the Center Across Selection option, however, you can only center text in the selected range. (See also "Alignment: Aligning Text Horizontally.")

Steps

  1. Type and format the title in the left cell of the range in which you want the title centered. Select the cells across which you want the text centered.

  2. Choose Format, Cells; then click the Alignment tab.

  3. In the Horizontal drop-down list, select the Center Across Selection option; then click OK.

To center a title using the Merge and Center button from the Formatting toolbar, type and format the title in the left cell of the range in which you want the title centered. Then, select the range and click the Merge and Center button.

If you later want to split a merged cell back into its individual cells, select the merged cell(s). Choose Format, Cells; then click the Alignment tab. Clear the Merge Cells check box; then click OK.

Alignment: Justifying Lines of Text

Excel's Justify option takes long strings of text, divides them into lengths that you specify, and reenters each length in its own cell (similar to full justification in a word processor). Lines are broken at spaces between words so that words stay together. The result appears as a paragraph with each new line starting in the next lower cell. You can use justification to join and wrap strings of text that are not in the same cell. (See also "Alignment: Aligning Text Horizontally.")

Steps

  1. Select the range that contains the text you want to justify, and extend the range selection to the right and down to define how much space you want the text to occupy after justification.

  2. Choose Format, Cells; then click the Alignment tab.

  3. In the Horizontal drop-down list, select the Justify option; then click OK.

CAUTION: It is possible to overwrite existing text when you justify a range of data. If this happens, immediately choose Edit, Undo Justify. When you need more space to justify text, you can shorten the text, insert cells (or rows or columns) and select a larger area in which to justify the text, or move the obstructing information.

Alignment: Rotating Text

In Excel, the default orientation for text is horizontal, reading left to right. You can also align text so that the letters are stacked, reading top to bottom, or rotated anywhere from 90 degrees counterclockwise (reading sideways, bottom to top) to 90 degrees clockwise (reading sideways, top to bottom).

You can use rotated text effectively when you need vertical titles for reports or to label the sides of charts, tables, or drawings.

Steps

  1. Select the cell or range containing data you want to rotate. Choose Format, Cells; then click the Align- ment tab.

  2. In the Orientation area, drag the pointer in the second box up or down to change the orientation of the text (as displayed in the Orientation preview box); or, specify a value in the Degrees box between 90 and -90 degrees. Then click OK.

TIP: To quickly select a stacked orientation, reading top to bottom, click the first box in the Orientation gauge; then click OK.

Alignment: Shrinking Text to Fit in a Cell

If you need to fit text in a cell without widening the column containing the text, you can shrink the size of the text by using the Shrink to Fit alignment option. If you apply the Shrink to Fit option to a cell and later add more text to the cell, Excel automatically shrinks the text more so that the text fits in the cell. If you delete some of the text from the cell, Excel increases the size of the text. (See also "Alignment: Wrapping Text in a Cell.")

Steps

  1. Select the cell or range containing data you want to shrink. Choose Format, Cells; then click the Alignment tab.

  2. Select the Shrink to Fit option; then click OK.

Alignment: Wrapping Text in a Cell

If you enter a long text entry in a cell, you can have Excel wrap the text so that it forms a paragraph that fits inside that cell. The cell's height increases to accommodate multiple lines of text. (See also "Alignment: Shrinking Text to Fit in a Cell.")

Steps

  1. Select the cell or range containing data you want to wrap. Choose Format, Cells; then click the Alignment tab.

  2. Select the Wrap Text option; then click OK.

TIP: If you change the width of a column after you've wrapped text in that column, you may need to adjust the row height. Double-click the line just under the row number for the row containing the wrapped text to automatically adjust the row height.

Borders and Lines

You can place borders around cells, or use borders as lines and double lines under cells to add emphasis, to define data-entry areas, or to mark totals and subtotals. When combined with shading, borders make your worksheets easier to read and add interest. You can use the Borders button on the Formatting toolbar to quickly add borders to selected cells.

Steps

  1. Select the cell or range to which you want to add a line or border.
  2. Click the down arrow next to the Borders button on the Formatting toolbar. A palette of border selections appears. Click the desired border.

TIP: You can access additional border options and line styles by using the Format Cells dialog box. First, select the cell or range where you want to add a border. Choose Format, Cells; then click the Border tab and select the options you want. If you want to add a color to your line or border, click the Color drop-down list and select a color from the palette.

Colors and Patterns

Just as fonts and attributes can enhance your worksheets, patterns and colors can help clarify meaning and make important data stand out by differentiating parts of the screen. If you have a color printer, you can print these colors.

Steps

  1. Select the cell or range to which you want to add color or a pattern (or both). Choose Format, Cells.
  2. Click the Patterns tab, and select the main color for your pattern from the Color grid. Select a pattern from the Pattern drop-down list.
  3. If you want the pattern lines to appear in color (rather than black), select a color from the lower portion of the Pattern drop-down list. Check the Sample area at the bottom-right corner of the dialog box to see the color and pattern; then click OK.

TIP: Be careful not to use too many colors or patterns in a single worksheet, or you may distract the reader. Apply color or patterns sparingly, only to worksheet data you most want to emphasize.

You also can use buttons in the Formatting toolbar to change the color used for the cell background or the text in the cell. Select the cell or range you want to format. To change the background color, click the down arrow next to the Fill Color button; then click the color you want. To change the font color, click the down arrow next to the Font Color button; then click the color you want.

(See also "Formatting: Conditional Formats.")

Copying Formats with the Format Painter

You can use the Format Painter button in the Standard toolbar to copy formats such as font, style, alignment, borders, fills, and so on, from selected cells. This formatting can then be applied to any or all the other cells.

For example, if you normally use the same formats to indicate totals in your worksheets (such as boldface, underline, a larger font, and currency format), you can format just one total and then use the Format Painter to quickly copy all the formats from that cell to other cells within your worksheet.

Steps

  1. Select the cell or range whose format(s) you want to copy.

  2. Click the Format Painter button on the Standard toolbar. The pointer changes to a plus sign with a paintbrush attached.

  3. Select the cell or range to which you want to copy the formats. The formatting is applied to the selected cell or range.

TIP: If you want to "paint" the format to several cells or ranges, double-click the Format Painter button, and then select the cells or ranges. The Format Painter will remain active until you click the Format Painter button again or press Esc.

Fonts: Applying Boldface, Italic, and Underline

You can emphasize text in your worksheets by applying boldface, italic, or underline to cell data. For example, you can use boldface for titles and column headings, italic for key words or phrases, and underlines for totals. (See also "Borders and Lines.")

Steps

  1. Select the cell or range containing the text you want to format.

  2. Click the Bold, Italic, or Underline button in the Formatting toolbar. The new formatting appears in the cell.

(See also "Formatting: Selected Characters in a Cell.")

Fonts: Changing Font Colors

With Excel, you can change the font colors that appear on-screen. If you have a color printer, you can print these colors.

Choose font colors carefully. From a readability standpoint, light font colors such as yellow are probably not a good choice--especially if you plan to present your worksheet data in an on-screen presentation. However, you may want to use lighter font colors if you also format the background of cells in a dark color (such as yellow text on a dark blue background). You should probably not use the color red to format numbers, unless you want to draw attention to negative numbers or perhaps sales figures that are below expectations. (See also "Colors and Patterns" and "Formatting: Conditional Formats.")

Steps

  1. Select the cell or range containing the text you want to format.

  2. Click the down arrow next to the Font Color button on the Formatting toolbar. A palette of colors appears.

  3. Click the color you want to use. The selected text changes to the color you select.

TIP: To see additional font colors, select the cell or range you want to format. Choose Format, Cells; then click the Font tab. Click the arrow beside the Color drop-down list and choose the desired color; then click OK.

Fonts: Changing Fonts and Font Sizes

Fonts represent the various typefaces used in printed materials. These fonts may be changed to emphasize certain aspects of the data being presented. The height of fonts is measured in points; there are 72 points per inch. Therefore, an 18-point font will print 1/4 inch tall. You can also change the size of fonts. Some fonts are available in more sizes than other fonts. In general, it is probably best to avoid using more than three different fonts on a page of information, or the page may become too busy. (See also "Fonts: Applying Boldface, Italic, and Underline.")

Steps

  1. Select the cell or range containing the text you want to format.

  2. Click the down arrow next to the Font button on the Formatting toolbar. A list of available fonts appears. Click the font you want to use.

  3. Click the down arrow next to the Font Size button on the Formatting toolbar. A list of available font sizes for the selected font appears. Click the font size you want to use.

TIP: To apply other effects to fonts, such as strikethrough, superscript, or subscript formatting, choose Format, Cells; then click the Font tab. Select the desired options, and then click OK.

Formatting: Conditional Formats

A new feature in Excel 97 enables you to apply a specified format to cells, depending upon whether or not specific conditions in the cell are met. The value or contents in a cell is evaluated to determine whether the specified formatting should be applied. If the condition is met, you can specify that formatting such as font style, font color, cell color and pattern, and cell borders be applied to the cell.

Why Would I Use This?

Conditional formats are especially valuable when you need to check for data entry errors, evaluate analysis data, and verify data used in executive information systems.

In a sales worksheet, for example, you can format a cell so that data appears in red and boldface if the value in the cell falls below a specified amount, or blue if the cell's value exceeds a certain amount.

Steps

  1. Select the cell to which you want to apply the conditional format; then, choose Format, Conditional Formatting.

  2. Select Cell Value Is from the Condition drop-down list. Select one of the conditional operators, such as "greater than," from the next drop-down list.

  3. Enter the values (or cell references) you want to compare in the two text boxes. Then, click the Format button and select the formatting you want to apply if the condition is met.

  4. Click the Add button if you want to add another condition, and then repeat steps 2 and 3. You can specify up to three conditions. Click OK.

To modify a conditional format, select the cells to which the conditional formatting has been applied, choose Format, Conditional Formatting, and modify the conditions and formatting as desired.

To delete a condition in a conditional format, click Delete in the Conditional Formatting dialog box to display the Delete Conditional Format dialog box. Select the conditions you want to delete and click OK.

NOTE: If you want to format a cell based on a formula that you specify, select Formula Is in the Condition drop-down list. Then, enter a formula in the text box that appears. The formula must evaluate to TRUE or FALSE. If the formula evaluates to TRUE, the conditional format you specify is applied to the cell.
The formula can include references to cells in the worksheet to which you are applying the conditional formatting, but not references to other worksheets or workbooks.

TIP: You can copy conditional formatting to other cells in the worksheet. Select the cell with the conditional format you want to copy, and then click the Format Painter button. Select the cells to which you want to copy the conditional formatting.

Formatting: Custom Numeric Formats

You can create your own custom numeric formats for financial or scientific tasks and create formats for catalog numbers, international currency, and so on. Any time you need to display a number in a special way, you should consider using a custom numeric format.

A custom format can have up to four parts, as shown in the following syntax line:

positive;negative;zero;text

You use a semicolon to separate the parts of a custom format. The first position specifies the format for positive numbers in the cell, the second for negative numbers, and so on.

Steps

  1. Select the cells for which you want to apply the custom format. Choose Format, Cells; then click the Number tab.

  2. In the Category list, select Custom.

  3. If an existing format closely resembles the custom format you want to create, select that format in the list.

  4. In the Type text box, edit the custom formats pattern. Click OK when you are done.

TIP: If you need help understanding the codes used in numeric formatting, press F1 while in the Format Cells dialog box; then choose the Help option related to custom format codes.

NOTE: Excel includes some Special formats for social security numbers, zip codes, and phone numbers. If this is the type of format you want to create, you may not need to create a custom format. Instead, select Special in the Category list, and then select the format you want.

To remove a custom format, choose Format, Cells; then click the Number tab. In the Category list, select Custom; then select the format you want to delete from the list and click Delete.

Formatting: Dates and Times

Regardless of how you enter or calculate the date and time, you can display the date and time using any of Excel's predefined formats. (See also "Entering: Dates and Times" in the section "Getting Started" and "Date and Time Functions" in the "Functions Mini-Reference" section.)

Excel supplies several predefined date and time formats for you. If you find that you want to use a format that isn't available, however, you can create a custom numeric format to display your date or time as you want it to appear. To do so, see "Formatting: Custom Numeric Formats" for more information.

Steps

  1. Select the cell or range containing the date or time you want to format. Choose Format, Cells; then click the Number tab.

  2. In the Category list, select either Date or Time.

  3. Select the format you want from the Type list; then click OK.

Formatting: Multiple Worksheets

Excel enables you to perform operations to groups of worksheets at the same time. By grouping worksheets, you can save much formatting, data entry, and editing time by applying the same operations to the entire group at once. This is especially useful for applications such as consolidations, or worksheets that should contain common headings or formulas. If you name a range in the active sheet while sheets are grouped, the same name is applied to all the sheets in the group. You can only group worksheets that are contained in the same workbook. (See "Selecting: Worksheets" in the section "Getting Started" before you complete this task.)

Steps

  1. Select the worksheets to which you want to apply identical formatting. Notice that the title bar now contains [Group].

  2. Begin formatting one of the selected worksheets. All formatting you apply to the current worksheet also applies to other sheets in the group.

  3. To separate the group into individual sheets, hold down the Shift key and click the sheet tab for the current worksheet.

Formatting: Numbers

By default, numbers are right-aligned and appear in the General number format. You can change the existing format to any Excel format you choose, however.(See also "Entering: Numbers" in the section "Getting Started." )

Excel supplies several predefined number formats for you. If you find that you want to use a format that isn't available, however, you can create a custom numeric format to display your number as you want it to appear in your worksheet. See "Formatting: Custom Numeric Formats" for more information.

Steps

  1. Select the cell that contains the number. Choose Format, Cells; then click the Number tab.

  2. Select the desired format from the Category list.

  3. Choose from any additional options that appear (such as Decimal Places, if applicable); then click OK.

Formatting: Selected Characters in a Cell

You can apply one or more attributes, such as bold, italic, or underline, to individual characters within a cell rather than to the entire cell. If a cell entry contains two words, for example, you can apply formatting to only one of these words.

Steps

  1. Select the cell containing the text you want to format. Select the text in the formula bar that you want to change by dragging across it with the mouse.

  2. Choose Format, Cells. Select the options you want to change on the Font tab; then click OK.

Formatting: Tables with AutoFormats

Excel's AutoFormat feature lets you create professional- looking tables with the click of a few buttons. No matter what your level of expertise with Excel, you can use AutoFormat to apply a set of predefined formatting choices to reports, tables, and lists without resorting to complex formatting operations.

Steps

  1. Select the range you want to format. Choose Format, AutoFormat.

  2. In the Table Format list, select the desired format (a preview of your selection appears in the Sample box). Click OK. The range you selected now displays the AutoFormat you chose.

NOTE: When you need to apply only parts of an AutoFormat, click the Options button in the AutoFormat dialog box. Clear formats in the Formats to Apply group that you do not want applied. For example, if you don't want AutoFormat to change your row heights or columns widths, clear the Width/Height check box.

TIP: If the format does not appear as you expected, immediately choose Edit, Undo AutoFormat to restore the table to its previous format.

Hiding Zeros

Hiding zeros often makes detailed financial worksheets easier to read. You can choose to hide all zeros throughout the worksheet, or use a conditional format to specify a range of cells in which you want to hide zeros.

Steps

  1. To hide zeros throughout the entire worksheet, choose Tools, Options; then click the View tab.

  2. Clear the Zero Values check box; then click OK.

When you want to view the zeros again, select the Zero Values check box on the View tab.

TIP: You can use a conditional format to hide zeros by specifying white (or whatever the background color is, if it is not white) as the font color when the value of the cell equals zero. You can apply this format to the entire worksheet or to just a range of cells in which you want to hide zeros. (See "Formatting: Conditional Formats" for more information.)

Styles: Creating a Style

Styles are a set of predefined formats you can create and then apply to a cell or range. If you often format text as bold, italic, and right-aligned, for example, create a style that will assign each of these formats to a selected cell or range. The default (standard) format is stored in Excel's Normal style format. If you type data in an unformatted cell, Excel uses the Normal style for that cell.

Steps

  1. Select a cell that contains the formatting to which you want to assign a style. Choose Format, Style. The Style dialog box appears.

  2. In the Style Name list box, type a name for the new style. In the Style Includes area, select or clear the options, as desired.

  3. Click the Modify button to display the Format Cells dialog box; select the desired formatting options, and then click OK to return to the Style dialog box.

  4. Click the Add button to add the defined style to the list of styles; then click OK.

  5. To use the style you created, select the cell or range you want to format. Choose Format, Style and select the style name from the Style Name list box; then click OK.

To delete a style you created, choose Format, Style. Then select the style name you want to delete in the Style Name list box and click the Delete button. Click OK. Any cells previously formatted with the style you deleted revert to the Normal style.

Styles: Merging Styles

If you've created styles in one workbook that you'd like to use in another workbook, you can copy the styles from one workbook to the other by merging the styles. All styles from the source workbook are merged into the target sheet--they will replace styles in the target sheet having the same name. (See "Styles: Creating a Style" before you complete this task.)

Steps

  1. Open the source and target workbooks and activate the workbook that will receive the copied styles.

  2. Choose Format, Style; then click the Merge button.

  3. In the Merge Styles From list, select the source workbook that contains the styles you want to copy; then click OK.

  4. You may see an alert box if the source and target workbooks have styles with the same names. Select Yes if you want the source styles to replace styles with the same name in the target workbook; or select No if you want to merge all styles except those with the same name; or select Cancel if you don't want to merge any styles.

  5. Excel returns you to the Style dialog box. Click the Close button.

Styles: Redefining Styles

Styles can save you time when you need to reformat a workbook. If your workbook uses styles, you need only to redefine the style. All cells in the workbook that use the style will immediately reformat to match the new style definition. Redefining the default Normal style affects all of the text in the current workbook that is not already formatted using other styles. (See "Styles: Creating a Style" before you complete this task.)

Steps

  1. Open the workbook containing the style you want to redefine.

  2. Choose Format, Style; then select the style you want to redefine in the Style Name list.

  3. Click the Modify button; then select the tab for the type of formatting you want to redefine.

  4. Change the formatting options for the style, as desired; then click OK.

  5. Click OK to redefine the style and apply it to the current cell; or click Close to close the dialog box without applying the style to the selected cell.

TOCBACKFORWARDHOME


©Copyright, Macmillan Computer Publishing. All rights reserved.