Midlands Microsoft Office Provider

2.4 Change Grand Totals, Insert Blank Lines, Repeat All Items Lables; Practice Exercises 11, 12, & 13 – Intro to Excel 2016 Pivot Tables

Practice Exercise 11: Change the Grand Total capability so that it is off for rows and columns, on for columns only, and on for rows only.
Still 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 12: Insert blank lines after each item in a pivot table for improved readability
​Still 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).

​Select the Report Layout icon, select drop down menu choice, Show in Outline Form (FIGURE 9).
​
FIGURE 8
                                                                     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).

​

​
​STEP 4: To remove the blank lines, select Blank Rows icon and “Remove Blank Lines after Each Item” (FIGURE 12).
                                                                  FIGURE 11
                                                                  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 13: Repeat All Item Labels
Still 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).
 

​


STEP 2: Select the Report Layout icon, select drop down menu choice, Show in Outline Form (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.

​

​

​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 15
                                                                    FIGURE 16
                                                                   FIGURE 17
                                                                    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
This is the end of this section. To continue, go to Module 2 Section 2.5 Improving Pivot Table Readability; Practice Exercise 14