Display or hide items in a PivotTable or PivotChart field

Hiding an item in a row field (row field: A field that's assigned a row orientation in a PivotTable report. Items associated with a row field are displayed as row labels.) or column field (column field: A field that's assigned a column orientation in a PivotTable report. Items associated with a column field are displayed as column labels.) removes it from the report, but the item still appears in the dropdown list for the field. Hiding an item in a page field (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.) removes it both from the report and from the dropdown list for the field.

Do one or more of the following:

ShowDisplay the top or bottom items in a field

  1. Click the field. For a PivotChart report, click the field in its associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.).
  2. On the PivotTable toolbar, click PivotTable, and then click Sort and Top 10.
  3. Under Top 10 AutoShow, click On.
  4. In the Show box, click Top or Bottom, and in the box to the right, enter the number of items to display.
  5. In the Using field box, click the data field (data field: A field from a source list, table, or database that contains data that is summarized in a PivotTable report or PivotChart report. A data field usually contains numeric data, such as statistics or sales amounts.) to use to calculate the top or bottom items.

ShowDisplay or hide items in a row (category) or column (series) field

  1. Click the arrow Field arrow in the field.
  2. Select the check box for each item that you want to show, and clear the check box for each item that you want to hide.

For fields that are organized in levels, click Plus box to list lower-level items so that you can display or hide them individually. The double-check Double-checked box indicates that some or all of the lower-level items are displayed, single-check indicates that only the checked item is displayed, and cleared indicates that neither the item nor its lower-level items are displayed.

  1. To show or hide items that have no data, double-click the field, and then in the PivotTable Field dialog box, select or clear the Show items with no data check box. (Some types of source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.) don't support this option.)

For example, if there were no sales in April, you will see an item for April sales only if you show items with no data.

ShowDisplay or hide items in a page field

  1. Double-click the page field.
  2. If the Hide items box is present, select each item that you want to hide, and clear the selection from each item that you want to show.

If the Hide items box is missing, your source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.) always lists all available items in the dropdown list for the page field.

  1. To show or hide items that have no data, select or clear the Show items with no data check box. (Some types of source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.) don't support this option.)

For example, if there were no sales in April, you will see an item for April sales only if you show items with no data.

ShowRedisplay hidden items in a field

  1. Double-click the field.
  2. In the PivotTable Field dialog box, click Advanced.
  3. Under Top 10 AutoShow, click Off.
  4. Click OK, and if the Hide items box is present and has items selected, clear selection from all such items.
  5. If the field is a row (category) or column (series) field, click its arrow Field arrow, and then click (Show All).

 Note   When you display or hide items in a PivotChart report or its associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.), some chart formatting may be lost.

ShowTip

When calculating subtotals and grand totals in PivotTable reports, you can include or exclude the hidden items. On the PivotTable toolbar, click Include Hidden Items in Totals Button image. If this button is unavailable, your source data allows you to include or exclude hidden items in page fields: click PivotTable, click Table Options, and then select or clear the Subtotal hidden page items check box.

 
 
Applies to:
Excel 2003