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.

Using Compact Layout, put Region, Product, and Customer in the Rows area. Choose one of the Customers before clicking the Collapse icon. — Figure 848. Start with Product, Region, and Customer.

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.

The Central region appears in row 4. Indented in rows 5, 6, and 7 are three products. There is a Plus icon to the left of each product. Use the Plus sign to show the customers for any one product. — Figure 849. 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.

If you try to expand a field when there are no additional row fields, the Show Detail dialog offers all of the other available fields. — Figure 850. Try to expand the innermost row field, and Excel will offer to add a new field.

This article is an excerpt from Power Excel With MrExcel

Title photo by and machines on Unsplash