→ For more details about customizing styles, see “Customizing the Pivot Table Appearance with Styles and Themes,” p.It does not matter which style you choose from the gallery any of the 84 other styles are better than the default style. This draws gridlines in light styles and adds row stripes in dark styles. Select the check box for Banded Rows to the left of the PivotTable Styles gallery.Styles toward the bottom of the gallery tend to have more formatting. Choose any style other than the first style from the drop-down.The gallery contains 85 styles to choose from. Click the bottom arrow to open the complete gallery, which is shown in Figure 3.2.įigure 3.2. Three arrows appear at the right side of the PivotTable Style gallery.From the ribbon, select the Design tab.Make sure that the active cell is in the pivot table.Any table style that you choose is better than the default.įollow these steps to apply a table style: Fortunately, you can apply a table style. The default pivot table layout contains no gridlines and is rather plain. Applying a Table Style to Restore Gridlines The following sections address each issue. You can correct each of these annoyances with just a few mouse clicks. Excel renames fields in the VALUES area with the unimaginative name Sum of Revenue.Most people prefer to see zeros instead of blanks. The blank cell in B5 indicates that there were no Communications sales in the Midwest. For sparse data sets, many blanks appear in the VALUES area.There are no commas, currency symbols, and so on. Numbers in the VALUES area are in a general number format.The default table style uses no gridlines, which makes it difficult to follow the rows and columns across.This default pivot table contains several annoying items that you might want to change quickly: A typical pivot table before customization. Check the Sector and Revenue fields, and drag the Region field to the COLUMNS area.įigure 3.1. To create this pivot table, open the Chapter 3 data file. You need to make a few changes to almost every pivot table to make it easier to understand and interpret. Other options-Review more obscure options found throughout the Excel interface.Advanced calculations-Use settings to show data as a running total, percent of total, rank, percent of parent item, and more.If you have a table that defaults to Count of Revenue instead of Sum of Revenue, you need to visit the section on this topic. Summary calculations-Change from Sum to Count, Min, Max, and more.Major cosmetic changes-Use table styles to format your table quickly.Layout changes-Compare three possible layouts, show/hide subtotals and totals, and repeat row labels.The fact that you must correct these defaults in every pivot table that you create is annoying. Minor cosmetic changes-Change blanks to zeros, adjust the number format, and rename a field.
Rather than cover each set of controls sequentially, this chapter seeks to cover the following functional areas in making pivot table customization: In Excel 2013, you find controls to customize the pivot table in myriad places: the Analyze tab, Design tab, Field Settings dialog, Data Field Settings dialog, PivotTable Options dialog, and context menus. These tweaks range from making cosmetic changes to changing the underlying calculation used in the pivot table. In this case, you can use many powerful settings to tweak the information in your pivot table. Changing the Calculation in a Value Field 67Īlthough pivot tables provide an extremely fast way to summarize data, sometimes the pivot table defaults are not exactly what you need.Customizing the Pivot Table Appearance with Styles and Themes 59.