Making Layout Changes
Excel 2007 offers three layout styles instead of the two styles available in previous versions of Excel. The new style—Compact Layout—is promoted to be the default layout for your pivot tables.
Layout changes are controlled in the Layout group of the Design ribbon, as shown in Figure 3.8. This group offers four icons:
- 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.
- Blank Rows—Inserts or removes blank lines after each group.
Figure 3.8 The Layout group on the Design ribbon offers different layouts and options for totals.
Using the New Compact Layout
By default, all new pivot tables use the compact layout shown in Figure 3.6. In this layout, multiple fields in the row area are stacked up in column A. Note in the figure that the Denver market and Midwest region are both in column A.
The compact form is suited for using the Expand and Collapse icons. Select one of the market cells—A7 as an example—and click the Collapse Entire Field icon on the Options ribbon. Excel hides all the detail below this field and shows only the regions, as shown in Figure 3.9.
Figure 3.9 Click the Collapse Entire Field icon to hide levels of detail.
After a field is collapsed, you can show detail for individual regions by using the plus icons in column A, or you can click Expand Entire Field on the Options ribbon to see the detail again.
Using the Outline Form Layout
When you select Design, Layout, Report Layout, Show in Outline Form, Excel fills column A with the outermost row field. Additional row fields occupy columns B, C, and so on.
Figure 3.11 shows the pivot table in Outline form.
Figure 3.11 The Outline layout puts each row field in a separate column.
This layout is better suited if you plan to copy the values from the pivot table to a new location for further analysis. Although the Compact layout offers a clever approach by squeezing multiple fields in one column, it is not ideal for reusing the data later.
By default, both the Compact and Outline layouts put the subtotals at the top of each group. You can use the Subtotals drop-down on the Design ribbon to move the totals to the bottom of each group, as shown in Figure 3.12.
Figure 3.12 With subtotals at the bottom of each group, the pivot table occupies several more rows.
Using the Traditional Tabular Layout
Pivot table veterans will recognize the tabular layout shown in Figure 3.13. This layout is similar to the one that has been used in pivot tables since their invention. In this layout, the subtotals can never appear at the top of the group.
Figure 3.13 The tabular layout is similar to pivot tables in prior versions of Excel.
The tabular layout is probably the best layout if you hope to later use the resulting summary data in a subsequent analysis.
Controlling Blank Lines, Grand Totals, Subtotals, and Other Settings
Additional settings in the pivot table allow you to toggle various elements.
Subtotals can be moved to the top or bottom of the group or turned off entirely. As noted previously, moving the subtotals to the top of the group saves a few rows in the pivot table. However, top subtotals are available only when the layout is set to Compact or Outline. Use the Subtotals icon on the Design ribbon to choose the subtotals option. Figure 3.18 shows the subtotals at the top of each group.
Figure 3.18 Subtotals at the top, grand totals for the rows, and blank lines between groups are controlled through icons in the Layout group on the Design ribbon.
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 of the Layout group on the Design ribbon. The wording in this drop-down seems just a bit confusing.
If you would like a grand total column on the right side of the table, you need to select On for Rows only. Even though it is a grand total column, each total is totaling a single row.
Similarly, to add a grand total row, you need to select On for Rows Columns only. Each individual grand total in the total row is totaling the cells in a column.
In Figure 3.18, the grand total column appears because the Grand Totals drop-down is set to On For Rows Only.
The Blank Rows drop-down allows you to insert blank lines between groups. In Figure 3.18, the blank lines in rows 13, 19, and 25 appear because Insert Blank Line After Each Item was selected in the Blank Rows drop-down.
As you examine the pivot table in Figure 3.18, you might think the area around B8:B9 appears strange. Whereas the sales figures for columns C, D, and E are closely aligned with the headings, the Qtr1 heading in B8 appears far away from the sales figures in B9:B29. This happens because all the headings in B8:E8 are left-aligned. Something is causing column B to be too wide. That something is the text Column Labels in B7. This text, plus Row Labels in A8, is a new feature in Excel 2007. Although this feature might have been designed to improve readability, it is annoying that the text makes column B too wide.
To remove these text entries, click the Field Headers icon in the Show/Hide group on the Options ribbon. This group also has icons to turn off the plus and minus buttons or to hide the PivotTable Field List. Figure 3.19 shows this section of the Ribbon, as well as the pivot table with all three items turned off.
Figure 3.19 In Excel 2007, field headers serve little purpose.