Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2021 Excel 2019 Excel 2016 Excel 2013 More...Less
In a PivotTable or PivotChart, you can expand or collapse to any level of data detail, and even for all levels of detail in one operation. On Windows and the Mac, 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) data source.You can also see the details that are used to aggregate the value in a value field.
WindowsMacWeb
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 the Show or hide the expand and collapse buttons in a PivotTable section in this topic.
-
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>".
-
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>".
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.
In Excel 2016 and Excel 2013: On the Analyze tab, in the Show group, click +/- Buttons to show or hide the expand and collapse buttons.
Note:Expand and collapse buttons are available only for fields that have detail data.
You can show or hide details and disable or enable the corresponding option.
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 onis placed on a new worksheet.
-
Hide value field details
-
Right-click the sheet tab of the worksheet that contains the value field data, and then click Hide or Delete.
Disable or enable the option to show value field details
-
Click anywhere in the PivotTable.
-
On the Options or Analyze tab (depending on the Excel version you are using) on the ribbon, in the PivotTable group, click Options.
-
In the PivotTable Options dialog box, click the Data tab.
-
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.
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 the Show or hide the expand and collapse buttons in a PivotTable section in this topic.
-
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>".
-
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>".
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.
In Excel 2016 and Excel 2013: On the Analyze tab, in the Show group, click +/- Buttons to show or hide the expand and collapse buttons.
Note:Expand and collapse buttons are available only for fields that have detail data.
You can show or hide details and disable or enable the corresponding option.
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 onis placed on a new worksheet.
-
Hide value field details
-
Right-click the sheet tab of the worksheet that contains the value field data, and then click Hide or Delete.
Disable or enable the option to show value field details
-
Click anywhere in the PivotTable.
-
On the Options or Analyze tab (depending on the Excel version you are using) on the ribbon, in the PivotTable group, click Options.
-
In the PivotTable Options dialog box, click the Data tab.
-
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.
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 the Show or hide the expand and collapse buttons in a PivotTable section in this topic.
-
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.
-
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 thePivotTabletab, in theShowgroup, click +/- Buttons to show or hide the expand and collapse buttons.
Note:Expand and collapse buttons are available only for fields that have detail data.
Showing or hiding details is supported for PivotTables created from a table or range.
Show value field details
-
In a PivotTable, do one of the following:
-
Click anywhere in the PivotTable. On the PivotTable tab, click Show Details.
-
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 onis placed on a new worksheet.
-
Hide value field details
-
Right-click the sheet tab of the worksheet that contains the value field data, and then click Hide or Delete.
Need more help?
You can always ask an expert in the Excel Tech Communityor get support inCommunities.
See Also
Create a PivotTable
Use the Field List to arrange fields in a PivotTable
Create a PivotChart
Use slicers to filter data
Create a PivotTable timeline to filter dates
Need more help?
Want more options?
Discover Community
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Microsoft 365 subscription benefits
Microsoft 365 training
Microsoft security
Accessibility center
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
