Collapse and Expand Pivot Fields
December 06, 2022 - by Bill Jelen

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.

Here’s how it works:
1. Select one of the customer cells. In the Analyze tab of the ribbon, select Collapse Entire Field. Excel will hide all the customer rows.

-
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.

This article is an excerpt from Power Excel With MrExcel
Title photo by and machines on Unsplash