This chapter is from the book
This chapter is from the book
This chapter is from the book
Making Report Layout Changes
Excel 2016 offers three report layout styles. The Excel team continues to offer the newer Compact layout as the default report layout, even though I continually hound them about the fact that people who work in the real world would rather use the Tabular report layout, or at least would like to have a choice about which one to use as a default.
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:
- 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.
)
Figure 3.8 The Layout group on the Design tab offers different layouts and options for totals.
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 Analyze tab to see the detail again.
TIP
If you select a cell in the innermost row field and click Expand Field on the Options tab, Excel displays the Show Detail dialog, as shown in Figure 3.10, to enable 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 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.
)
Figure 3.11 The Outline layout puts each row field in a separate 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:A10, and A12:A15 in Figure 3.11.
Figure 3.11 shows the same pivot table from before, now in Outline form and with labels repeated.
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 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 new 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.
Case Study: Converting a Pivot Table to Values
Say that you want to convert the pivot table shown in Figure 3.13 to be a regular data set that you can sort, filter, chart, or export to another system. You don’t need the Sectors totals in rows 7, 14, 18, and so on. You don’t need the Grand Total at the bottom. And, depending on your future needs, you might want to move the Region field from the Columns area to the Rows area. This would allow you to add Cost and Profit as new columns in the final report.
Finally, you want to convert 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 tab, select Grand Totals, Off for Rows and Columns.
- Select Design, Subtotals, Do Not Show Subtotals.
- Drag the Region tile from the Columns area in the PivotTable Fields list. Drop this field between Sector and Customer in the Rows area.
Check Profit and Cost in the top of the PivotTable Fields list. Because both fields are numeric, they move to the Values area and appear in the pivot table as new columns. Rename both fields using the Current Field box on the Analyze tab. The report is now a contiguous solid block of data, as shown in Figure 3.14.
Figure 3.14 The pivot table now contains a solid block of data.
- Select one cell in the pivot table. Press Ctrl+* to select all the data in the pivot table.
- Press Ctrl+C to copy the data from the pivot table.
- Select a blank section of a worksheet.
Right-click and choose Paste Values to open the fly-out menu. Select Paste Values and Number Formatting, as shown in Figure 3.15. Excel pastes a static copy of the report to the worksheet.
Figure 3.15 Use Paste Values to create a static version of the data.
- If you no longer need the original pivot table, select the entire pivot table and press the Delete key to clear the cells from the pivot table and free up the area of memory that was holding the pivot table cache.
)
){kind=link}
){kind=link}
){kind=link}
The result is a solid block of summary data. These 27 rows are a summary of the 500+ rows in the original data set, but they also are suitable for exporting to other systems.
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 offers the choice Insert Blank Row 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 region makes the report easier to read. However, if you remove Sector from the report, you have only Region 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 of the Layout group on the Design tab. The wording in this drop-down is a bit confusing, so Figure 3.18 shows what each option provides. The default is to show grand totals for rows and columns, as in Figure 3.17.
)
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 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 introduced in Excel 2007 replaced useful headings with Row Labels. You can turn these off.