Customizing a pivot table

Making report layout changes

Excel offers three report layout styles. The Excel team continues to offer the Compact layout as the default report layout. If you prefer a different layout, change it using the default settings for pivot tables.

If you consider three report layouts, and the ability to show subtotals at the top or bottom, plus choices for blank rows and Repeat All Item Labels, you have 16 different layout possibilities available.

Layout changes are controlled in the Layout group of the Design tab, as shown in Figure 3-8. This group offers four icons:

FIGURE 3-8 The Layout group on the Design tab offers different layouts and options for totals.

• Subtotals—Moves subtotals to the top or bottom of each group or turns them off.

• Grand Totals—Turns the grand totals on or off for rows and columns.

• Report Layout—Uses the Compact, Outline, or Tabular forms. Offers an option to repeat item labels.

• Blank Rows—Inserts or removes blank lines after each group.

Using the Compact layout

By default, all new pivot tables use the Compact layout that you saw in Figure 3-6. In this layout, multiple fields in the row area are stacked in column A. Note in the figure that the Consultants sector and the Andrew Spain Consulting customer are both in column A.

The Compact form is suited for using the Expand and Collapse icons. If you select one of the Sector value cells such as Associations in A5 and then click the Collapse Field icon on the Analyze tab, Excel hides all the customer details and shows only the sectors, as shown in Figure 3-9.

FIGURE 3-9 Click the Collapse Field icon to hide levels of detail.

After a field is collapsed, you can show detail for individual items by using the plus icons in column A, or you can click Expand Field on the PivotTable Analyze tab to see the detail again.

FIGURE 3-10 When you attempt to expand the innermost field, Excel offers to add a new innermost field.

Using the Outline layout

When you select Design, Layout, Report Layout, Show In Outline Form, Excel puts each row field in a separate column. The pivot table shown in Figure 3-11 is one column wider, with revenue values starting in C instead of B. This is a small price to pay for allowing each field to occupy its own column. Soon, you will find out how to convert a pivot table to values so you can further sort or filter. When you do this, you will want each field in its own column.

The Excel team added the Repeat All Item Labels option to the Report Layout tab starting in Excel 2010. This alleviated a lot of busy work because it takes just two clicks to fill in all the blank cells along the outer row fields. Choosing to repeat the item labels causes values to appear in cells A6:A7, A9:A14, as shown in Figure 3-11.

Figure 3-11 shows the same pivot table from before, now in Outline form and with labels repeated.

FIGURE 3-11 The Outline layout puts each row field in a separate column.

By default, both the Compact and Outline layouts put the subtotals at the top of each group. You can use the Subtotals drop-down menu on the Design tab to move the totals to the bottom of each group, as shown in Figure 3-12. In Outline view, this causes a not-really-useful heading row to appear at the top of each group. Cell A5 contains “Associations” without any additional data in the columns to the right. Consequently, the pivot table occupies 44 rows instead of 37 rows because each of the 7 sector categories has an extra header.

FIGURE 3-12 With subtotals at the bottom of each group, the pivot table occupies several more rows.

Using the traditional Tabular layout

Figure 3-13 shows the Tabular layout. This layout is similar to the one that has been used in pivot tables since their invention through Excel 2003. In this layout, the subtotals can never appear at the top of the group. The Repeat All Item Labels works with this layout, as shown in Figure 3-13.

FIGURE 3-13 The Tabular layout is similar to pivot tables in legacy versions of Excel.

The Tabular layout is the best layout if you expect to use the resulting summary data in a subsequent analysis. If you wanted to reuse the table in Figure 3-13, you would do additional “flattening” of the pivot table by choosing Subtotals, Do Not Show Subtotals and Grand Totals, Off For Rows And Columns.

Controlling blank lines, grand totals, and other settings

Additional settings on the Design tab enable you to toggle various elements.

The Blank Rows drop-down menu offers the choice Insert Blank Line After Each Item. This setting applies only to pivot tables with two or more row fields. Blank rows are not added after each item in the inner row field. You see a blank row after each group of items in the outer row fields. As shown in Figure 3-16, the blank row after each sector makes the report easier to read. However, if you remove Sector from the report, you have only Customer in the row fields, and no blank rows appear (see Figure 3-17).

FIGURE 3-16 The Blank Rows setting makes the report easier to read.

FIGURE 3-17 Blank rows will not appear when there is only one item in the row field.

Grand totals can appear at the bottom of each column and/or at the end of each row, or they can be turned off altogether. Settings for grand totals appear in the Grand Totals drop-down menu of the Layout group on the Design tab. The wording in this drop-down menu is a bit confusing, so Figure 3-18 shows what each option provides. The default is to show grand totals for rows and columns.

FIGURE 3-18 The wording is confusing, but you can toggle off the grand total column, row, or both.

If you want a grand total column but no grand total at the bottom, choose On For Rows Only, as shown at the top of Figure 3-18. To me, this seems backward. To keep the grand total column, you have to choose to turn on grand totals for rows only. I guess the rationale is that each cell in F5:F8 is a grand total of the row to the left of the cell. Hence, you are showing the grand totals for all the rows but not for the columns. Perhaps someday Microsoft will ship a version of Excel in English-Midwest where this setting would be called “Keep the Grand Total Column.” But for now, it remains confusing.

In a similar fashion, to show a grand total row but no grand total column, you open the Grand Totals menu and choose On For Columns Only. Again, in some twisted version of the English language, cell B18 is totaling the cells in the column above it.

The final choice, Off For Rows And Columns, is simple enough. Excel shows neither a grand total column nor a grand total row.

Back in Excel 2003, pivot tables were shown in Tabular layout and logical headings such as Region and Product would appear in the pivot table, as shown in the top pivot table in Figure 3-19. When the Excel team switched to Compact form, they replaced those headings with Row Labels and Column Labels. These add nothing to the report. To toggle off those headings, look on the far-right side of the PivotTable Analyze tab for an icon called Field Headers and click it to remove Row Labels and Column Labels from your pivot tables in Compact form.

FIGURE 3-19 The Compact form replaces useful headings with Row Labels. You can turn these off.