Home > Sample chapters > Microsoft Office > Excel

How to Change Workbook Appearance in Microsoft Excel 2010

Defining Styles

As you work with Excel, you will probably develop preferred formats for data labels, titles, and other worksheet elements. Instead of adding a format’s characteristics one element at a time to the target cells, you can have Excel store the format and recall it as needed. You can find the predefined formats by displaying the Home tab, and then in the Styles group, clicking Cell Styles.

You can choose a style from the Cell Styles gallery, or create a custom style.

Clicking a style from the Cell Styles gallery applies the style to the selected cells, but Excel also displays a live preview of a format when you point to it. If none of the existing styles is what you want, you can create your own style by clicking New Cell Style at the bottom of the gallery to display the Style dialog box. In the Style dialog box, type the name of your new style in the Style Name field, and then click Format. The Format Cells dialog box opens.

httpatomoreillycomsourcemspimages1409840.jpg

A custom style can include number, alignment, font, and border formatting.

After you set the characteristics of your new style, click OK to make your style available in the Cell Styles gallery. If you ever want to delete a custom style, display the Cell Styles gallery, right-click the style, and then click Delete.

If all you want to do is apply formatting from one cell to the contents of another cell, use the Format Painter tool in the Clipboard group on the Home tab. Just click the cell that has the format you want to copy, click the Format Painter button, and then click the cells to which you want to apply the copied format. To apply the same formatting to multiple cells, double-click the Format Painter button and then click the target cells. When you’re done applying the formatting, press the Esc key.

In this exercise, you’ll create a style and apply the new style to a data label.

  1. httpatomoreillycomsourcemspimages1409842.jpg On the Home tab, in the Styles group, click Cell Styles, and then click New Cell Style.

    The Style dialog box opens.

    httpatomoreillycomsourcemspimages1409844.jpg

    The elements of the current style are described in the Cell Style dialog box.

  2. In the Style name field, type Crosstab Column Heading.

  3. Click the Format button. In the Format Cells dialog box, click the Alignment tab.

    httpatomoreillycomsourcemspimages1409846.jpg

    You can specify the alignment and direction of text.

  4. In the Horizontal list, click Center.

  5. Click the Font tab.

  6. In the Font style list, click Italic.

    The text in the Preview pane appears in italicized text.

    httpatomoreillycomsourcemspimages1409848.jpg

    You can make changes on multiple pages of the Format Cells dialog box before closing it.

  7. Click the Number tab.

    The Number page of the Format Cells dialog box is displayed.

  8. In the Category list, click Time.

    The available time formats appear.

  9. In the Type pane, click 1:30 PM.

  10. Click OK to save your changes.

    The Format Cells dialog box closes, and your new style’s definition appears in the Style dialog box.

  11. Click OK.

    The Style dialog box closes.

  12. Select cells C4:N4.

  13. On the Home tab, in the Styles group, click Cell Styles.

    The Cell Styles gallery opens.

    Your new style appears at the top of the gallery, in the Custom group.

  14. Click the Crosstab Column Heading style.

    Excel applies your new style to the selected cells.