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 shadings (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 365.
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:
- Select a cell in the pivot table. Select the Design tab on the ribbon.
- 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.)
- Turn on banded rows.
- Open the PivotTable Styles gallery. Thanks to Live Preview, you can hover over various thumbnails and see the effect of each on the table. Fig 833 shows Pivot Style Light 10. Fig 834 shows Pivot Style Dark 19.
-
One of the light styles.
-
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.
- Change theme colors and the pivot table colors will change.