Use a report filter in a PivotTable or PivotChart report

Use a report filter to conveniently display a subset of data in a PivotTable report or PivotChart report. A report filter helps to manage the display of large amounts of data, and to focus on a subset of data in the report, such as a product line, a time span, or a geographic region.

What do you want to do?


Display a different set of values in a report based on a report filter item

  1. Click the arrow Filter drop-down arrow in the cell with the item (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) currently displayed in the report filter displayed on the PivotTable report or PivotChart Filter Pane.
  2. Do one of the following:

Select a single item     Click the item that you want. In fields organized in levels, click Plus box beside a level to display the lower-level items, or click Minus box to display higher-level items until the item that you want appears in the list.

Select multiple items     Click the Select multiple items check box, and then click the items that you want.

Display the summary of all items     Click (All).

 Note   If your OLAP data source is Microsoft SQL Server Analysis Services version 2005, you can only select a calculated member if it is a single item, you cannot select multiple items when one or more of those items are calculated members.

Top of Page Top of Page

Display each set of values in a report based on a report filter item on a separate worksheet

You can display each report filter page, which is the set of values that match the selected report filter item, on a separate worksheet.

  1. Click the PivotTable report or the associated PivotTable report of a PivotChart report.
  2. On the Options tab, in the PivotTable group, click the arrow next to Options, and then click Show Report Filter Pages.
  3. In the Show Report Filter Pages dialog box, select a report filter field, and then click OK.

Top of Page Top of Page

Arrange multiple report filters

If you have multiple report filters, you can arrange them on the PivotTable report or PivotChart report by doing the following:

  1. Click the PivotTable report or the associated PivotTable report of a PivotChart report.
  2. On the Options tab, in the PivotTable group, click Options.
  3. In the PivotTable Options dialog box, click the Layout & Format tab.
  4. Under Layout, in the Display fields in report filter area list box do one of the following:
    • Select Down, Then Over to first display fields in the report filter area from the top to the bottom, as fields are added to it, before taking up another column.
    • Select Over, Then Down to first display fields in the report filter area from left to right, as fields are added to it, before taking up another row.
  5. In the Report filter fields per column or Report filter fields per row box, type or select the number of fields to display before taking up another column or row based on the setting of Display fields in report filter area.

Top of Page Top of Page

 
 
Applies to:
Excel 2007