- Making Common Cosmetic Changes
- Making Report Layout Changes
- Customizing a Pivot Table's Appearance with Styles and Themes
- Changing Summary Calculations
- Adding and Removing Subtotals
- Changing the Calculation in a Value Field
- Next Steps
This chapter is from the book
This chapter is from the book
This chapter is from the book
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 2016 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:
- 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.
- 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.
- In the Table Element list, click First Row Stripe. A new section called Stripe Size appears in the dialog.
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.
- To change the stripe color, click the Format button. The Format Cells dialog 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, Blue=204 to simulate 1980s-era greenbar paper. Click OK to accept the color and return to the Modify PivotTable Quick Style dialog.
- 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.
- 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.
- Optionally edit the colors for Header Row and Grand Total Row.
- 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.
- 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 2016 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 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 on the Page Layout tab. Choose a new theme, and the colors used in the pivot table change to match the theme.
TIP
Some of the themes use unusual fonts. You can apply the colors from a theme without changing the fonts in your document by using the Colors drop-down next to the Themes menu, as shown in Figure 3.22.
){kind=link}
Figure 3.22 Choose new colors from the Colors menu.