Lesson 5

Preview

Customizing a Pivot Table; Practice Exercise 4

Practice Exercise 4 – Change the layout of a Pivot Table so it displays data in the Compact Form

Continue to use data file: RANGE TO TABLE TO PIVOT TABLE2

Compact View
The compact view is used when space is of a prime consideration although the readability factor is lower than using Outline or Tabular views. It is the default form view for Excel pivot tables.

STEP 1: Move the fields to the 4 quadrants as shown by the graphic to the right (FIGURE 1).

Filters: None
Columns: Values, Category
Rows: Year, Month, Market
​Values: Sales Qty, Sales

Figure 1

STEP 2: Select any cell in the pivot table. Select the PivotTable Tools Design tab on the Ribbon as shown in FIGURE 2.​

Figure 2

STEP 3: ​In the Layout section of the Ribbon, select the Report Layout icon. Select the choice from the drop-down menu, “Show in Compact Form” (FIGURE 3).

Figure 3

STEP 4: In Compact View (FIGURE 4), the Month, Region, and Market fields are all sharing the same column, Column A.
In Compact View (and Outline View)  the Subtotals appear up at the top (default) of their section instead of at the bottom. The placement of Subtotals  can be changed so that they appear at the bottom of the section if desired.​ 
NOTE: The Compact View is the default view seen whenever you first create a pivot table.

Figure 4
  • In Compact View, the Month, Region, and Market fields are all sharing the same column, Column A.
  • In Compact View (and Outline View)  the Subtotals appear up at the top (default) of their section instead of at the bottom.
  • The placement of Subtotals  can be changed so that they appear at the bottom of the section if desired. 

Practice Exercise 5 – Change the layout of a Pivot Table so that it displays data in the Outline Form

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

Outline View
The Outline View is used when readability is desired. Rather than have all of the row fields displayed in the first column, the Outline View displays each Row field in a separate column, thus improving the readability.

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

Figure 5

STEP 2: In the Layout section of the Ribbon Design tab, select the Report Layout icon. Select the choice from the drop-down menu, “Show in Outline Form” as shown in (FIGURE 6).

Figure 6

STEP 3: Notice that in Outline View (FIGURE 7), the Row fields are not all contained in the first column, as is the case with the Compact View. In Outline View, separate columns are used to display the Year field, Month field, and Market Field. The Year field is in Column A, the Month field is in Column B, and the Market field is in Column C. 

As with the Compact View, the default location for the Subtotals is up at the top of a section. You can change the location of the Subtotals to the bottom of a section if desired.

Figure 7

Practice Exercise 6 – Change the layout of a Pivot Table so that it displays data in the Tabular Form

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

Tabular View
Subtotals are placed at the bottom of the section they are in as the default; you cannot move subtotals up to the top of a section as you can with the Compact and Outline views.
The Tabular View is the best layout of the three if you anticipate using the data in another future analysis. The data is in a presentation that is most conducive for reuse since the readability factor is higher in the tabular view. 

STEP 1: Select any cell in the pivot table to show the contextual tabs. Select the PivotTable Design tab from the Ribbon (FIGURE 8).

Figure 8

STEP 2: In the Layout section of the Ribbon Design tab, select the Report Layout icon.
Select the choice from the drop-down menu, “Show in Tabular Form” (FIGURE 9).

Figure 9

STEP 3: ​Here is the result of selecting “Show in Tabular Form” (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