Home > Sample chapters > Microsoft Office > Excel

How to Change Workbook Appearance in Microsoft Excel 2010

Applying Workbook Themes and Excel Table Styles

Microsoft Office 2010 includes powerful design tools that enable you to create attractive, professional documents quickly. The Excel product team implemented the new design capabilities by defining workbook themes and Excel table styles. A theme is a way to specify the fonts, colors, and graphic effects that appear in a workbook. Excel comes with many themes installed.

To apply an existing workbook theme, display the Page Layout tab. Then, in the Themes group, click Themes, and click the theme you want to apply to your workbook. By default, Excel applies the Office theme to your workbooks.

You can choose from among dozens of preformatted visual themes.

When you want to format a workbook element, Excel displays colors that are available within the active theme. For example, selecting a worksheet cell and then clicking the Font Color arrow displays a palette of colors. The theme colors appear at the top of the color palette—the standard colors and the More Colors link, which displays the Colors dialog box, appear at the bottom of the palette.

httpatomoreillycomsourcemspimages1409854.jpg

If you format workbook elements by using colors from the Theme Colors area, applying a different theme changes that object’s colors.

You can change a theme’s colors, fonts, and graphic effects by displaying the Page Layout tab and then, in the Themes group, selecting new values from the Colors, Fonts, and Effects lists. To save your changes as a new theme, display the Page Layout tab, and in the Themes group, click Themes, and then click Save Current Theme. Use the controls in the Save Current Theme dialog box that opens to record your theme for later use. Later, when you click the Themes button, your custom theme will appear at the top of the gallery.

Just as you can define and apply themes to entire workbooks, you can apply and define Excel table styles. You select an Excel table’s initial style when you create it; to create a new style, display the Home tab, and in the Styles group, click Format As Table. In the Format As Table gallery, click New Table Style to display the New Table Quick Style dialog box.

httpatomoreillycomsourcemspimages1409856.jpg

You can apply a standard Excel table style or create a custom table style.

Type a name for the new style, select the first table element you want to format, and then click Format to display the Format Cells dialog box. Define the element’s formatting, and then click OK. When the New Table Quick Style dialog box reopens, its Preview pane displays the overall table style and the Element Formatting area describes the selected element’s appearance. Also, in the Table Element list, Excel displays the element’s name in bold to indicate it has been changed. To make the new style the default for new Excel tables created in the current workbook, select the Set As Default Table Quick Style For This Document check box. When you click OK, Excel saves the new table style.

In this exercise, you’ll create a new workbook theme, change a workbook’s theme, create a new table style, and apply the new style to an Excel table.

  1. If necessary, click any cell in the Excel table.

  2. httpatomoreillycomsourcemspimages1409744.png On the Home tab, in the Styles group, click Format as Table, and then click the style at the upper-left corner of the Table Styles gallery.

    Excel applies the style to the table.

  3. On the Home tab, in the Styles group, click Format as Table, and then click New Table Style.

    The New Table Quick Style dialog box opens.

  4. In the Name field, type Exception Default.

  5. In the Table Element list, click Header Row.

  6. Click Format.

    The Format Cells dialog box opens.

  7. Click the Fill tab.

    The Fill page is displayed.

    httpatomoreillycomsourcemspimages1409858.jpg

    The fill colors shown in the palette are specific to the currently applied theme.

  8. In the first row of color squares, just below the No Color button, click the third square from the left.

    The new background color appears in the Sample pane of the dialog box.

  9. Click OK.

    The Format Cells dialog box closes. When the New Table Quick Style dialog box reopens, the Header Row table element appears in bold, and the Preview pane’s header row is shaded.

  10. In the Table Element list, click Second Row Stripe, and then click Format.

    The Format Cells dialog box opens.

  11. Just below the No Color button, click the third square from the left again.

    The new background color appears in the Sample pane of the dialog box.

  12. Click OK.

    The Format Cells dialog box closes. When the New Table Quick Style dialog box reopens, the Second Row Stripe table element appears in bold, and every second row is shaded in the Preview pane.

    httpatomoreillycomsourcemspimages1409860.jpg

    The effect of your changes is shown in the Preview area.

  13. Click OK.

    The New Table Quick Style dialog box closes.

  14. On the Home tab, in the Styles group, click Format as Table. In the gallery, in the Custom area, click the new format.

    Excel applies the new format.

  15. httpatomoreillycomsourcemspimages1409862.png On the Page Layout tab, in the Themes group, click the Fonts arrow, and then in the list, click Verdana.

    Excel changes the theme’s font to Verdana (which is part of the Aspect font set).

    After changing a theme’s colors, fonts, or effects, you can save the theme for reuse as a custom theme.

  16. httpatomoreillycomsourcemspimages1409866.png In the Themes group, click the Themes button, and then click Save Current Theme.

    The Save Current Theme dialog box opens.

    Custom themes are stored with other Office templates.

  17. In the File name field, type Verdana Office, and then click Save.

    Excel saves your theme.

  18. In the Themes group, click the Themes button, and then click Origin.

    Excel applies the new theme to your workbook.