This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Collapse and Expand Pivot Fields
Problem: I will be using a pivot table projected on a screen during a sales forecasting meeting. I need pivot tables that show products by region, but sometimes I need to see the customer detail for a product.
Strategy: You can solve this problem by building a pivot table with Region, Product, and Customer along the row area.
Figure 786 Start with Product, Region, and Customer.
Here’s how it works:
1. Select one of the customer cells. In the Options tab of the ribbon, select Collapse Entire Field. Excel will hide all the customer rows.
Figure 787 Collapse the Customer field.
2. Select a region cell and collapse that field as well.
Notice that each product has a plus sign button to the left of the field. When the meeting agenda moves to the DEF product, you can click the plus sign in A6 to see the region totals. You can continue collapsing sections as you are finished and then expanding the next sections.
Additional Details: If you select the innermost row field (in this case, Customer) and select Expand Entire Field, Excel assumes that you must need more detail for Customer. Because there is no additional detail in the pivot table, Excel will display the Show Detail dialog, allowing you to add a new field as the innermost row field.
Figure 788 Try to expand the innermost row field, and Excel will offer to add a new field.