Lesson 6

Preview

Relocating and Removing Subtotals; Practice Exercise 7 & 8

Practice Exercise 7: Relocating and Removing Subtotals Position in a Compact View

NOTE: If you are using the tabular View, you cannot relocate subtotals from the bottom to the top of a section. Only the compact and outline views will allow subtotals at the top of a section. Since only the Compact and Outline format views show subtotaling at the top by default, we will focus on those 2 views.

STEP 1:Select any cell on the pivot table. Select the PivotTable Tools Design tab on the Ribbon (FIGURE 1).

Figure 1

STEP 2: From the Report Layout icon in the toolbar Layout group, select “Show in Compact Form” (FIGURE 2).

Figure 2

STEP 3: In the Ribbon Layout section, select the Subtotals icon on the left end of the Ribbon.  On the drop-down menu, select the choice, “ Show all Subtotals at Bottom of Group” (FIGURE 3).

You have other choices with disposition of the Subtotals as shown on the graphic to the right. Other choices include:

  • Do Not Show Subtotals
  • ​Show all Subtotals at Top of Group (default for Compact & Outline Views)
Figure 3

Pivot table below in FIGURE 4 shows data in Compact View with Subtotals at Bottom.

Figure 4

Note: We will not show how to select Subtotaling at the top since that is the default when you select the Compact view.


Practice Exercise 8: Relocating and Removing Subtotals Position in an Outline View

Continue to use the data file, RANGE TO TABLE TO PIVOT TABLE2.

NOTE: If you are using the tabular View, you cannot relocate subtotals from the bottom to the top of a section. Only the compact and outline views will allow subtotals at the top of a section. Since only the Compact and Outline format views show subtotaling at the top by default, we will focus on those 2 views.

​STEP 1: Select any cell on the pivot table. Select the PivotTable Tools Design tab on the Ribbon (FIGURE 5).

Figure 5

​STEP 2: In the Ribbon Layout section, select the Report Layout icon. On the drop-down menu, select the choice,   “ Show in Outline Form” (FIGURE 6).

Figure 6

STEP 3: In the Ribbon Layout section, select the Subtotals icon. On the drop-down menu, select the choice, “Show all Subtotals at Bottom of Group” (FIGURE 7).

Figure 7

This is our pivot table in Outline Form with subtotaling shown at the bottom (FIGURE 8).

Figure 8

Note: We will not show how to select Subtotaling at the top since that is the default when you select the Outline view.

Now we will remove subtotals

STEP 4: In the Ribbon Layout section, select the Subtotals icon. On the drop-down menu, select the choice, “Do Not Show Subtotals” (FIGURE 9).

Figure 9

STEP 2: Here is our pivot table below in Outline View with Subtotaling removed (FIGURE 10).

Figure 10

END OF LESSON


Back to: Intro to Excel 2016 Pivot Tables > Module 2 – Intro to Excel 2016 Pivot Tables

Leave a Reply