Lesson 8

Preview

Change Grand Totals, Insert Blank Lines, Repeat All Items Labels; Practice Exercises 10, 11, & 12

Practice Exercise 10: Change the Grand Total capability so that it is off for rows and columns, on for columns only, and on for rows only.

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

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

Figure 1

STEP 2: In the Ribbon Layout section, select the Grand Total icon on the left end of the Ribbon (FIGURE 2). On the drop-down menu, select the choice, “ Off For Rows and Columns

Figure 2

The resulting pivot table with “Grand Totals Off For Rows and Columns” is shown below in FIGURE 3:

Figure 3

STEP 4: Select the Grand Total icon again. On the drop-down menu, select the choice, “ On For Rows Only” (FIGURE 4).

Figure 4

The resulting pivot table with “Grand Totals On For Rows Only” is shown below in FIGURE 5:

Figure 5

STEP 5: Select the Grand Total icon again. On the drop-down menu, select the choice, “ On For Columns Only” (FIGURE 6).

Figure 6

The resulting pivot table with “Grand Totals On For Columns Only” is shown below in FIGURE 7:

Figure 7

Practice Exercise 11: Insert blank lines after each item in a pivot table for improved readability

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

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

Figure 8

​Select the Report Layout icon, select drop down menu choice, Show in Outline Form (FIGURE 9).

Figure 9

STEP 2: For Grand Totals, select On for Rows and Columns (FIGURE 10).

Figure 10

​STEP 3: In the Ribbon Layout section, select the Blank Rows icon on the left end of the Ribbon. On the drop-down menu, select the choice, Insert Blank Line after Each Item (FIGURE 11).

Figure 11

​STEP 4: To remove the blank lines, select Blank Rows icon and “Remove Blank Lines after Each Item” (FIGURE 12).

Figure 12

The resulting pivot table with “Insert Blank Line After Each Item” is shown below in FIGURE 13:

Figure 13

The resulting pivot table with “Remove Blank Line After Each Item” is shown below in FIGURE 14:

Figure 14

Practice Exercise 12: Repeat All Item Labels

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

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

Figure 15

STEP 2: Select the Report Layout icon, select drop down menu choice, Show in Outline Form (FIGURE 16).

Figure 16

STEP 3: In the Ribbon Layout section, select the Report Layout icon on the left end of the Ribbon (FIGURE 17). On the drop-down menu, select the choice, Repeat All Item Labels.

Figure 17

​STEP 5: To remove the repeating of item labels, select Report Layout icon again and select the choice, Do Not Repeat Items Labels (FIGURE 18).

Figure 18

The resulting pivot table with “Repeat All Item Labels” is shown below in FIGURE 19:

Figure 19

The resulting pivot table with “Do Not Repeat Item Labels” is shown below in FIGURE 20:

Figure 20

END OF LESSON


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

Leave a Reply