Abstract
Transcript
Pivot tables have a useful feature that allows you to expand or collapse groups to see or hide details.
Let’s take a look.
Here we have a pivot table that shows product sales by category and product, grouped by year and quarter. Whenever you have a pivot table that summarizes data into various groups, you can interactively expand and collapse those groups.
Pivot tables have special expand and collapse buttons that can be enabled on the Options tab of the PivotTable Tools Ribbon. When enabled, you can use these buttons to expand and collapse various groups in the table. However, you can also just double-click these same groups to do the same thing, which is a cleaner and faster way. We’ll disable the buttons for now.
With each field you interact with, you can work with one item individually, or the entire field at once. For example, we can expand and collapse each item in the category field individually, in any combination.
If you right-click on an item, you can access the same options in the menu.
We can also expand and collapse the entire field at once. Just select a cell in the field, and use the buttons on the ribbon. These buttons are called Expand Entire Field, and Collapse Entire Field.
The same options are available in the right-click menu. Use Expand Entire Field, and Collapse Entire Field under the Expand/Collapse menu.
We can use the same approach for column label fields. If we select an item in the Year grouping, we can expand and collapse all quarters at once. We can also work with each year individually by double-clicking.
You can also use the Expand and Collapse feature to actually add fields to your pivot table. For example, if we double-click an item in the product field, Excel offers to add additional detail in the form of another field. We can, for example, add Region, and then expand and collapse region detail for each product. Notice that Excel has added the Region field as a row label under product. Of course, you can remove fields added in this way at any time.
