This chapter is from the book
This chapter is from the book
This chapter is from the book
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.
TIP
If you select a cell in the innermost row field and click Expand Entire Field, Excel displays the Show Detail dialog box, as shown in Figure 3.10, to allow you to add a new innermost row field.
)
Figure 3.10 When you attempt to expand the innermost field, Excel offers to add a new innermost field.
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.
Case Study: Converting a Pivot Table to Values
Say that you want to summarize your dataset to show sales by Region, Market, and Quarter. Your goal is to export this data for use by another system.
The result in Figure 3.13 is close to the desired output, with a few exceptions:
- The subtotals in rows 9, 14, 19, and 23 should be removed.
- The blank cells in A7:A8, A11:A13, A16:A18, and A21:A22 should be filled in.
- The grand total should be removed from row 24 and column G.
- The pivot table should be converted from a live pivot table to static values.
To make these changes, follow these steps:
- Select any cell in the pivot table.
- From the Design ribbon, choose Grand Totals, Off for Rows and Columns.
- Select Design, Subtotals, Do Not Show Subtotals.
- Select A5:F19, as shown in Figure 3.14.
Figure 3.14 After removing the total and subtotal information, select the data and one row of headings.
- Press Ctrl+C to copy the data from the pivot table.
- In a blank section of the workbook, choose Home, Clipboard, Paste, Paste Values to make a static copy of the data. The final step is filling in the blank cells in the first column of the table.
- Select column A of the dataset.
- In the Editing group of the Home ribbon, choose the Find & Select drop-down and then choose Go To Special.
- In the Go To Special dialog box, as shown in Figure 3.15, select Blanks and click OK.
Figure 3.15 Choosing Blanks from the Go To Special dialog box selects only the blank cells in column A.
- Type the equal sign (=), press the up-arrow key, and then press Ctrl+Enter to accept this formula for all the blank cells. This combination enters a formula in which each blank cell inherits the value from the cell above it, as shown in Figure 3.16.
Figure 3.16 The last step is changing the formulas in column A to values.
- Reselect the cells in column A.
- Click the Copy icon in the Clipboard group on the Home ribbon.
- Choose Paste Values from the Paste drop-down on the Home ribbon.
)
)
)
){kind=link}
The result is a solid block of summary data, as shown in Figure 3.17. These 90 cells are a summary of the 1.1 million cells in the original dataset, but they also are suitable for exporting to other systems.
)
Figure 3.17 The final dataset is suitable for exporting to another system.
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.