Show or hide elements of a PivotTable or PivotChart view

When the drop areas are hidden and the PivotTable view does not have a field in the row, column, data, or filter area, you can't drag fields to these areas.

1. On the PivotTable toolbar, click Properties , and then click the Behavior tab.

2. Select or clear the Drop areas check box.

1. On the PivotTable toolbar, click Properties , and then click the Report tab.

2. Select or clear the ScreenTips check box.

1. Click the row or column field for which you want to show or hide subtotals and grand totals.

2. To show totals, on the PivotTable toolbar, click the Subtotal button so that it is selected. To hide totals, click the button again.

Notes

• Subtotals and grand totals are not displayed for fields in the detail area (the area where all of the individual records can be displayed).

• When you choose to display subtotal and grand total values, they are displayed for every total field in the PivotTable view. You can't exclude individual total fields.

• Subtotal and grand total calculations can include or exclude items of data that are hidden by filtering.

Do one of the following:

• To show or hide detail data for a cell or for an item at the lowest level, click Show Details or Hide Details on the PivotTable toolbar.

• To show or hide detail data for all members of a row or column field, select the innermost row or column field, and then click Show Details or Hide Details on the PivotTable toolbar.

Note  To show or hide lower-level items, select the row or column field or item, and then click Expand or Collapse on the PivotTable toolbar.

Do one of the following:

• To show or hide the lower-level items of a specific item in the row or column axis, click the expand indicator ( or box) next to the item.

• To show or hide the lower-level items for all items in a row or column field, click the field label, and then click Expand or Collapse on the PivotTable toolbar.

Notes

• If the or box is not visible next to the item, select the Expand indicator check box on the Behavior tab in the Properties dialog box. If after selecting this check box you don't see an expand indicator next to an item, the item might be at the lowest level.

• To show or hide details, select the item or field, and then click Show Details or Hide Details on the PivotTable toolbar.

1. On the PivotTable toolbar, click Properties , and then click the Behavior tab.

2. Select or clear the Expand indicator check box.

Notes

• This setting does not affect the and boxes that appear to the right of the items at the lowest level.

• Items at the lowest level in a row or column area don't have expand indicators on their left.

1. On the PivotTable toolbar, click Properties , and then click the Report tab.

2. Select or clear the Empty rows and Empty columns check boxes.

1. On the PivotChart toolbar, click Properties , and then click the General tab.

2. Under General commands, in the Select box, click Chart Workspace.

3. Click the Show/Hide tab.

4. Select or clear the Field buttons/drop zones check box.

1. On the PivotChart toolbar, click Properties , and then click the General tab.

2. Under General commands, in the Select box, click Chart Workspace.

3. Click the Show/Hide tab.

4. Under Show by default, select or clear the ScreenTips check box.

1. On the PivotChart toolbar, click Properties , and then click the General tab.

2. Under General commands, in the Select box, click Chart Workspace.

3. Click the Show/Hide tab.

4. Under Show by default, select or clear the Field list check box.

A passive alert indicates that your chart contains an error. The alert appears as an exclamation point at the bottom of the chart, and when you click it, information about the error is displayed.

1. On the PivotChart toolbar, click Properties , and then click the General tab.

2. Under General commands, in the Select box, click Chart Workspace.

3. Click the Show/Hide tab.

4. Under Errors, select or clear the Show passive alerts check box.

You can expand or collapse items in series fields or category fields to show or hide more detailed information for a particular item. Expanding is similar to drilling except that items on the same level are not hidden. In the example, West is expanded to show detailed information for West, but other regions are still displayed without detailed information. Collapsing will remove the detailed information for that item, and return the chart to the state it was prior to expanding.

Chart that shows sales in each region

Chart with West expanded to show sales per product in the West region

1. Click the field that contains the items you want to expand or collapse.

2. On the PivotChart toolbar, click Expand or Collapse .

You can drill into an item in an outer field to focus on detailed information for that particular item, or you can drill out to remove detailed information for that item. Drilling is similar to expanding, except that when you drill into an item, other items on the same level are hidden.

In the example, the region North is drilled into, showing detail information for the North region and hiding all information for other regions. When you drill out of an item that you have drilled into, the detailed information is removed, and items on the same level are shown again.

Chart that shows sales information for products by region.

Chart that shows the North region item drilled into to show sales information for each type of product in the North region only. Other regions are hidden.

1. Click the item on the category axis for which you want to show or hide details.

2. On the PivotChart toolbar, click Drill into or Drill out .