Customizing Fields in a Pivot Table

• By Bill Jelen and Michael Alexander
• Jul 15, 2005
• Sample Chapter is provided courtesy of Que

This chapter is from the book
Pivot Table Data Crunching

Adding and Removing Subtotals

Subtotals are undeniably an essential feature of pivot table reporting. There are times when you will want to suppress the display of subtotals and other times when you will want to show more than one subtotal per field.

Suppress Subtotals When You Have Many Row Fields

When you have many row fields in your report, subtotals can mire your view. Take the example in Figure 3.9.

Figure 3.9 Sometimes you don’t need subtotals at every level.

You can remove subtotals by activating the PivotTable Field dialog box and selecting None under Subtotals, as shown in Figure 3.10.

Figure 3.10 Choose None to remove subtotals at the Market level.

You must remove subtotals from each of the outer row fields individually. After repeating these steps for Region, Market, and Line of Business, you’ll find the report in Figure 3.11 to be much easier on the eyes.

Figure 3.11 After specifying None for three fields, you give the report a cleaner look.

Adding Multiple Subtotals for One Field

You can go in the opposite direction and add customized subtotals by choosing Custom and selecting the types of subtotals you would like to see. The dialog box in Figure 3.12 shows five subtotals being selected for the Region field.

Figure 3.12 By selecting the Custom option in the Subtotals box, you can specify multiple subtotals for one field.

With this option, you can add a host of metrics as subtotals. The report in Figure 3.13 shows all five subtotals for the Region field.

Figure 3.13 Instead of just a sum or count, this report gives a variety of statistics in the Total line for the Region field.