Expand, collapse, or show details in a PivotTable or PivotChart report

To show or hide additional data in a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) or PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.), you can expand or collapse fields that have detail data to any level of data detail.

You can also show or hide the details that are used to aggregate the value in a value field.

What do you want to do?


Expand or collapse to different levels of detail

In a PivotTable report or PivotChart report, you can expand or collapse to any level of data detail, and even for all levels of detail in one operation. You can also expand or collapse to a level of detail beyond the next level. For example, starting at a country/region level, you can expand to a city level which expands both the state/province and city level. This can be a time-saving operation when you work with many levels of detail. In addition, you can expand or collapse all members for each field in an Online Analytical Processing (OLAP) (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) data source.

Expand or collapse levels in a PivotTable

In a PivotTable, do one of the following:

  • Click the expand or collapse button next to the item that you want to expand or collapse.

Note    If you don't see the expand or collapse buttons, see Show or hide the expand and collapse buttons in a PivotTable.

  • Double-click the item that you want to expand or collapse.
  • Right-click the item, click Expand/Collapse, and then do one of the following:
    • To see the details for the current item, click Expand.
    • To hide the details for the current item, click Collapse.
    • To hide the details for all items in a field, click Collapse Entire Field.
    • To see the details for all items in a field, click Expand Entire Field.
    • To see a level of detail beyond the next level, click Expand To "<Field name>".
    • To hide to a level of detail beyond the next level, click Collapse To "<Field name>".

Expand or collapse levels in a PivotChart

In a PivotChart, right-click the category label for which you want to show or hide level details, click Expand/Collapse, and then do one of the following:

  • To see the details for the current item, click Expand.
  • To hide the details for the current item, click Collapse.
  • To hide the details for all items in a field, click Collapse Entire Field.
  • To see the details for all items in a field, click Expand Entire Field.
  • To see a level of detail beyond the next level, click Expand To "<Field name>".
  • To hide to a level of detail beyond the next level, click Collapse To "<Field name>".

Show or hide the expand and collapse buttons in a PivotTable

The expand and collapse buttons are displayed by default, but you may have hidden them (for example, when you don't want them to appear in a printed report). To use these buttons to expand or collapse levels of detail in the report, you must make sure that they are displayed.

  • On the Options tab, in the Show group, click +/- Buttons to show or hide the expand and collapse buttons.

Excel Ribbon Image

Note    Expand and collapse buttons are available only for fields that have detail data.

Top of Page Top of Page

Show or hide details for a value field in a PivotTable

By default, the option to display details for a value field in a PivotTable is turned on. To protect others from seeing this data, you can turn it off.

Show value field details

  • In a PivotTable, do one of the following:
    • Right-click a field in the values area of the PivotTable, and then click Show Details.
    • Double-click a field in the values area of the PivotTable.

The detail data that the value field is based on is placed on a new worksheet.

Hide value field details

  • To hide the value field detail data, right-click the sheet tab of the worksheet that contains the data, and then click Hide or Delete.

Disable or enable the option to show value field details

  1. Click anywhere in the PivotTable.

This displays the PivotTable Tools, adding the Options and Design tabs.

  1. On the Options tab, in the PivotTable group, click Options.

Excel Ribbon Image

  1. In the PivotTable Options dialog box, click the Data tab.
  2. Under PivotTable Data, clear or select the Enable show details check box to disable or enable this option.

Note    This setting is not available for an OLAP data source.

Top of Page Top of Page

 
 
Applies to:
Excel 2010