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.
- Start with Product, Region, and Customer.
Here's how it works:
- Select one of the customer cells. In the Options tab of the ribbon, select Collapse Entire Field. Excel will hide all the customer rows.
- Collapse the Customer field.
- 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.
- Try to expand the innermost row field, and Excel will offer to add a new field.