• Excel Book Excerpt

Excel Format Pivot Tables with the Gallery

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Format Pivot Tables with the Gallery

Problem: Due to the dynamic nature of pivot tables, it is fairly hard to format them. If I start applying formats to individual cells, the formats are lost after I rearrange the pivot table. Help!

Strategy: You can solve this problem by using the gallery on the Design tab of the ribbon. This is an amazing improvement over Excel 2003’s AutoFormat.

The gallery offers seven color styles (grayscale and six theme colors). There are four styles each in three gradings (light, medium, and dark). There is one style with no formatting. You have (6 x 4 x 3) 72 color styles, 12 grayscale styles, and 1 plain style for a total of 85 styles.

You can modify the color and grayscale styles by using the four check boxes Row Headers, Column Headers, Banded Rows, and Banded Columns. Since each checkbox offers 2 choices, 2 x 2 x 2 x 2 = 16 variations on each of the 84 styles. 84 x 16 + 1 yields 1345 styles, (1152 color, 192 grey, 1 plain)

By choosing a new theme, you can change the 6 accent colors to any of 40 built-in sets of colors. This leads to 46,080 color styles (1152 x 40). Adding the grayscale and plain style gives you 46,273 styles.

In case one of the built-in 46.273 different styles doesn’t work for you, then you can create your own custom formatting. See the “None of the 46,273 Built-In Styles
Do What My Manager Asks For" on page 372

In comparison, Excel 2003 offered 22 AutoFormats, and all of them were horrible. Many of them changed the layout of your table. Microsoft did an incredible job with the formatting options in Excel 2007. Here’s how you use them:

1. Select a cell in the pivot table. Select the Design tab on the ribbon.

2. Make selections in the PivotTable Style Options group, changing Row Headers, Column Headers, Banded Rows, and/or Banded columns. (You should do this before opening the Styles gallery, as the thumbnails in the gallery will reflect these settings.)


Figure 803 Turn on banded rows.

3. Open the PivotTable Styles gallery. Thanks to Live Preview, you can hover over various thumbnails and see the effect of each on the table. Figure 804 shows Pivot Style Light 10. Figure 805 shows Pivot Style Dark 19.


Figure 804 One of the light styles.


Figure 805 One of the dark styles.

Additional Details: On the Page Layout tab of the ribbon, you can change to any of the different built-in color schemes. This will affect the colors used in the gallery.


Figure 806 Change theme colors and the pivot table colors will change.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy