Home > Articles > Home & Office Computing > Microsoft Applications

  • Print
  • + Share This
This chapter is from the book

Customizing the Pivot Table Appearance with Styles and Themes

The PivotTable Styles gallery on the Design ribbon offers 84 built-in styles. Grouped into 28 styles each of Light, Medium, and Dark, the gallery offers variations on the accent colors used in the current theme.

Note that you can modify the thumbnails for the 84 styles shown in the gallery by using the four check boxes in the PivotTable Style Options group. In Figure 3.20, the 84 styles are shown with all four of the option buttons unchecked.

Figure 3.20

Figure 3.20 The 84 thumbnails appear one way when no style options are checked.

In Figure 3.21, the 84 styles are shown with accents for row headers, column headers, and alternating colors in the columns.

Figure 3.21

Figure 3.21 The 84 thumbnails appear differently with three style options checked. You can see that many of the styles do not support banded columns, even though this option is chosen.

The PivotTable Style Options group appears to the left of the PivotTable Styles gallery. If you want banded rows or columns, it is best to choose this option before opening the gallery. Some of the 84 themes do not support banded columns or banded rows.

Excel 2007's Live Preview feature works in the styles gallery. As you hover your mouse cursor over style thumbnails, the worksheet shows a preview of the style.

Customizing a Style

You can create your own pivot table styles. The new styles are added to the gallery and will be available on every new pivot table created on your computer.

Say that you want to create a pivot table style in which the banded colors are two rows high. Follow these steps to create the new style:

  1. Find an existing style in the PivotTable Styles gallery that supports banded rows. Right-click the style in the gallery and choose Duplicate. Excel displays the Modify Table Quick Style dialog box.
  2. Choose a new name for the style. Excel initially appends a 2 to the existing style name, so you have a name like PivotStyleMedium16 2.
  3. In the Table Element list, click on First Row Stripe. A new section called Stripe Size appears in the dialog box.
  4. Choose 2 from the Stripe Size drop-down, as shown in Figure 3.22.
    Figure 3.22

    Figure 3.22 Customize the style in the Modify Table Quick Style dialog box.

  5. If you want to change the stripe color, click the Format button. The Format Cells dialog box appears. Here, click the Fill tab and then choose a fill color. Click OK to accept the color and return to the Modify Table Quick Style dialog box.
  6. In the Table Element List, click on Second Row Stripe. Change the Stripe Size drop-down to 2.
  7. Click OK. Prepare to be disappointed that the change didn't work. It's okay, though. When you modified table style Medium 16, you actually created a brand new style. The pivot table is still formatted in the original style.
  8. Open the PivotTable Styles gallery. Your new style is added to the top of the gallery in the Custom section. Choose the style to apply the formatting, as shown in Figure 3.23.
    Figure 3.23

    Figure 3.23 Your new style is available at the top of the gallery.

Choosing a Default Style for Future Pivot Tables

You can control which style is the default style to use for all future pivot tables on the computer. The default can either be one of the built-in styles or a new custom style that you modified.

In the PivotTable Styles gallery on the Design ribbon, right-click the style and choose Set as Default.

Modifying Styles with Document Themes

The formatting options for pivot tables in Excel 2007 are impressive. The 84 styles, combined with 8 combinations of the Style options, make for hundreds of possible format combinations.

In case you ever become tired of these combinations, you can visit the Themes drop-down on the Page Layout ribbon. Twenty built-in themes are available here. Each theme has a new combination of accent colors, fonts, and shape effects. Choosing a new theme affects the fonts and colors in your pivot table styles.

To change a document theme, open the Themes drop-down on the Page Layout ribbon. As you hover the mouse cursor over the themes in the drop-down, Live Preview shows you the colors and fonts in your table, as shown in Figure 3.24. To select a theme, click on it.

Figure 3.24

Figure 3.24 Choose a document theme to modify the colors and fonts in the built-in styles.

  • + Share This
  • 🔖 Save To Your Account