Format Pivot Tables with the Gallery

December 14, 2022 - by Bill Jelen

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 None of the 46,273 Built-In Styles Do What My Manager Asks For.

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.)

The PivotTable Style Options group has four checkboxes: Row Headers, Column Headers, Banded Rows, and Banded Columns. — Figure 863. 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 864 shows Pivot Style Light 10. Figure 865 shows Pivot Style Dark 19.

The lighter pivot table format uses a pink color in the outer row fields and each subtotal row. — Figure 864. One of the light styles.

A darker format has dark purple in column A and then medium purple in the rest of the pivot table. — Figure 865. 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.

Use the Colors drop-down on the Page Layout tab to change the six theme colors. — Figure 866. Change theme colors and the pivot table colors will change.

This article is an excerpt from Power Excel With MrExcel

Title photo by Edward Howell on Unsplash