A Pivot Table in Excel

Although pivot tables provide an extremely fast way to summarize data, sometimes the pivot table defaults are not exactly what you need. In such cases, you can use many powerful settings to tweak pivot tables. These tweaks range from making cosmetic changes to changing the underlying calculation used in the pivot table.

In Excel 2016, you find controls to customize a pivot table in myriad places: the Analyze tab, Design tab, Field Settings dialog, Data Field Settings dialog, PivotTable Options dialog, and context menus.

Rather than cover each set of controls sequentially, this chapter covers the following functional areas in making pivot table customization:

  • Minor cosmetic changes—Change blanks to zeros, adjust the number format, and rename a field. The fact that you must correct these defaults in every pivot table that you create is annoying.
  • Layout changes—Compare three possible layouts, show/hide subtotals and totals, and repeat row labels.
  • Major cosmetic changes—Use pivot table styles to format a pivot table quickly.
  • Summary calculations—Change from Sum to Count, Min, Max, and more. In a pivot table that defaults to Count of Revenue, change it to default to Sum of Revenue instead.
  • Advanced calculations—Use settings to show data as a running total, percent of total, rank, percent of parent item, and more.
  • Other options—Review some of the obscure options found throughout the Excel interface.


Making Common Cosmetic Changes

You need to make a few changes to almost every pivot table to make it easier to understand and interpret. Figure 3.1 shows a typical pivot table. To create this pivot table, open the Chapter 3 data file. Select Insert, Pivot Table, OK. Check the Sector, Customer, and Revenue fields, and drag the Region field to the Columns area.