Chapter 6: Worksheet Formatting

Note

The graphic background on a worksheet is for onscreen display only — it isn’t printed when you print the worksheet. n

Copying Formats by Painting

Perhaps the quickest way to copy the formats from one cell to another cell or range is to use the Format Painter button (the button with the paintbrush image) of the Home Clipboard group.

1.Select the cell or range that has the formatting attributes you want to copy.

2.Click the Format Painter button. The mouse pointer changes to include a paintbrush.

3.Select the cells to which you want to apply the formats.

4.Release the mouse button, and Excel applies the same set of formatting options that were in the original range.

If you double-click the Format Painter button, you can paint multiple areas of the worksheet with the same formats. Excel applies the formats that you copy to each cell or range that you select. To get out of Paint mode, click the Format Painter button again (or press Esc).

Using Named Styles for Easier Formatting

One of the most underutilized features in Excel is named styles. Named styles make it very easy to apply a set of predefined formatting options to a cell or range. In addition to saving time, using named styles also helps to ensure a consistent look.

A style can consist of settings for up to six different attributes:

•Number format

•Font (type, size, and color)

•Alignment (vertical and horizontal)

•Borders

•Pattern

•Protection (locked and hidden)

The real power of styles is apparent when you change a component of a style. All cells which use that named style automatically incorporate the change. Suppose that you apply a particular style to a dozen cells scattered throughout your worksheet. Later, you realize that these cells should have a font size of 14 pt rather than 12 pt. Rather than change each cell, simply edit the style. All cells with that particular style change automatically.

131

Part I: Getting Started with Excel

Applying styles

Beginning with Excel 2007, the style feature has been revamped significantly, and Excel now includes a good selection of predefined named styles that work in conjunction with document themes. Figure 6.12 shows the effect of choosing Home Styles Cell Styles. Note that this display is a live preview: that is, as you move your mouse over the style choices, the selected cell or range temporarily displays the style. When you see a style you like, click it to apply the style to the selection.

Note

If Excel’s window is wide enough, you won’t see the Cell Styles command in the Ribbon. Rather, you will see four or more formatted style boxes. Click the drop-down arrow to the right of these boxes to display all the defined styles. n

FIGURE 6.12

Excel displays samples of available cell styles.

Note

By default, all cells use the Normal style. If you modify the Normal style, all cells that haven’t been assigned a different style will reflect the new formatting. n

After you apply a style to a cell, you can apply additional formatting to it by using any formatting method discussed in this chapter. Formatting modifications that you make to the cell don’t affect other cells that use the same style.

You have quite a bit of control over styles. In fact, you can do any of the following:

•Modify an existing style.

•Create a new style.

•Merge styles from another workbook into the active workbook.

132

Chapter 6: Worksheet Formatting

The following sections describe these procedures.

Modifying an existing style

To change an existing style, choose Home Styles Cell Styles. Right-click the style you want to modify and choose Modify from the shortcut menu. Excel displays the Style dialog box, shown in Figure 6.13. In this example, the Style dialog box shows the settings for the Office theme Normal style — which is the default style for all cells. The style definitions vary, depending on which document theme is active.

FIGURE 6.13

Use the Style dialog box to modify named styles.

Here’s a quick example of how you can use styles to change the default font used throughout your workbook.

1.Choose Home Styles Cell Styles. Excels displays the list of styles for the active workbook.

2.Right-click Normal and choose Modify. Excel displays the Style dialog box (Figure 6-13), with the current settings for the Normal style.

3.Click the Format button. Excel displays the Format Cells dialog box.

4.Click the Font tab and choose the font and size that you want as the default.

5.Click OK to return to the Style dialog box.

6.Click OK again to close the Style dialog box.

The font for all cells that use the Normal style changes to the font that you specified. You can change any formatting attributes for any style.

133

Part I: Getting Started with Excel

Creating new styles

In addition to using Excel’s built-in styles, you can create your own styles. This flexibility can be quite handy because it enables you to apply your favorite formatting options very quickly and consistently.

To create a new style, follow these steps:

1.Select a cell and apply all the formatting that you want to include in the new style.

You can use any of the formatting that is available in the Format Cells dialog box (refer to Figures 6-3 and 6-5).

2.After you format the cell to your liking, choose Home Styles Cell Styles, and choose New Cell Style. Excel displays its Style dialog box (refer to Figure 6-13), along with a proposed generic name for the style. Note that Excel displays the words By Example to indicate that it’s basing the style on the current cell.

3.Enter a new style name in the Style Name field. The check boxes display the current formats for the cell. By default, all check boxes are selected.

4.(Optional) If you don’t want the style to include one or more format categories, remove the check(s) from the appropriate check box(es).

5.Click OK to create the style and to close the dialog box.

After you perform these steps, the new custom style is available when you choose

Home Styles Cell Styles. Custom styles are available only in the workbook in which they were created. To copy your custom styles to another workbook, see the section that follows.

Note

The Protection option in the Style dialog box controls whether users will be able to modify cells for the selected style. This option is effective only if you’ve also turned on worksheet protection, by choosing Review Changes Protect Sheet. n

Merging styles from other workbooks

Custom styles are stored with the workbook in which they were created. If you’ve created some custom styles, you probably don’t want to go through all the work to create copies of those styles in each new Excel workbook. A better approach is to merge the styles from a workbook in which you previously created them.

To merge styles from another workbook, open both the workbook that contains the styles that you want to merge and the workbook into which you want to merge styles. From the workbook into which you want to merge styles, choose Home Styles Cell Styles and choose Merge Styles. Excel displays the Merge Styles dialog box that shows a list of all open workbooks. Select the workbook that contains the styles you want to merge and click OK. Excel copies styles from the workbook that you selected into the active workbook.

134