Customizing a pivot table

Customizing a pivot table’s appearance with styles and themes

You can quickly apply color and formatting to a pivot table report by using the 85 built-in styles in the PivotTable Styles gallery on the Design tab. These 85 styles are further modified by the four check boxes to the left of the gallery. Throw in the 48 themes on the Page Layout tab, and you have 65,280 easy ways to format a pivot table. If none of those provide what you need, you can define a new style.

Start with the four check boxes in the PivotTable Style Options group of the Design tab of the ribbon. You can choose to apply special formatting to the row headers, column headers, banded rows, or banded columns. My favorite choice here is banded rows because it makes it easier for the reader’s eye to follow a row across a wide report. You should choose from these settings first because the choices here will modify the thumbnails shown in the Styles gallery.

As mentioned earlier, the PivotTable Styles gallery on the Design tab offers 85 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. In Figure 3-20, you can see which styles in the gallery truly support banded rows and which just offer a bottom border between rows.

FIGURE 3-20 The styles are shown here with accents for row headers, column headers, and alternating colors in the columns.

The Live Preview feature in Excel 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, and the new styles are added to the gallery for the current workbook only. To use the custom style in another workbook, copy and temporarily paste the formatted pivot table to the other workbook. After the pivot table has been pasted, apply the custom style to an existing pivot table in your workbook and then delete the temporary pivot table.

Say that you want to create a pivot table style in which the banded colors are three 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 select Duplicate. Excel displays the Modify PivotTable Quick Style dialog box.

2. Choose a new name for the style. Excel initially appends a 2 to the existing style name, which means you have a name such as PivotStyleDark3 2. Type a better name, such as Greenbar.

3. In the Table Element list, click First Row Stripe. A new section called Stripe Size appears in the dialog box.

4. Select 3 from the Stripe Size drop-down, as shown in Figure 3-21.

   

   FIGURE 3-21 Customize the style in the Modify PivotTable Style dialog box.

5. To change the stripe color, click the Format button. The Format Cells dialog box appears. Click the Fill tab and then choose a fill color. If you want to be truly authentic, choose More Colors, Custom and use Red=200, Green=225, and Blue=204 to simulate 1980s-era greenbar paper. Click OK to accept the color and return to the Modify PivotTable Quick Style dialog box.

6. In the Table Element List, click Second Row Stripe. Select 3 from the Stripe Size drop-down. Modify the format to use a lighter color, such as white.

7. If you plan on creating more pivot tables in this workbook, choose the Set As Default PivotTable Style For This Document check box in the lower left.

8. Optionally, edit the colors for Header Row and Grand Total Row.

9. Click OK to finish building the style. Strangely, Excel doesn’t automatically apply this new style to the pivot table. After you put in a few minutes of work to tweak the style, the pivot table does not change.

10. Your new style should be the first thumbnail visible in the Styles gallery. Click that style to apply it to the pivot table.

Modifying styles with document themes

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

In case you become tired of these combinations, you can visit the Themes drop-down menu on the Page Layout tab, where many built-in themes are available. Each theme has a new combination of accent colors, fonts, and shape effects.

To change a document theme, open the Themes drop-down menu on the Page Layout tab. Choose a new theme, and the colors used in the pivot table change to match the theme.

FIGURE 3-22 Choose new colors from the Colors menu.

Case Study: Excel Pivot Table Archaeology

At Microsoft, a great person named Howie Dickerman has been in charge of pivot tables for the last few years. Howie was an architect of Power Pivot and was always happy to engage the MVPs when we had some crazy pivot table idea. Occasionally, we would find a pivot table oddity and Howie would dig in to the code to try to figure out why. He called this Excel Archaeology. Howie retired in 2021 and his willingness to go deep into the Excel code base on archaeological expeditions will be missed.

This oddity would have been right up his alley. Excel MVP Wyn Hopkins from Access Analytic recently uncovered a feature in pivot tables that allows you to quickly apply a border to several sections inside the pivot table.

Say that you have a pivot table such as the one in Figure 3-23. Leave a blank row and column on each side of the pivot table, but then completely surround the pivot table with a border. The border shown in Figure 3-23 has a different style on each side to illustrate how each side of the border will be translated to the pivot table.

FIGURE 3-23 A relatively plain pivot table, with a border just outside the pivot table.

To transfer the outer border to the pivot table, right-click any cell in the pivot table and choose Refresh. The borders from outside the pivot table are applied to various sections inside the pivot table.

To transfer the outer border to the pivot table, right-click any cell in the pivot table and choose Refresh. The borders from outside the pivot table are applied to various sections inside the pivot table.

FIGURE 3-24 The border styles from the outer border are applied to the pivot table.

In a very specific pattern, the border styles used in the outer border are applied throughout the pivot table. The dashed border used above row 2 is applied above rows 3, 4 and above cell C11. The dotted border used below row 12 is applied in the pivot table below row 11. A similar pattern happens with the left border being applied to most vertical lines in the pivot table. The right border is applied to the right edge of the pivot table.

Why is this feature in Excel? It had to pre-date the Formatting Gallery. It must have been a way to build your own Pivot Table Styles before there were styles.