Filter items in a PivotTable report

Filtering enables in-depth analysis of large amounts of data in a PivotTable report. There are different ways to filter PivotTable items. You can use report filters to quickly display a subset of data, such as a product line, a time span, or a geographic region. For easy access, report filters that you add are conveniently placed above the PivotTable report.

In addition to or instead of using report filters, you can apply label, value, or date filters to filter by specific text-based labels, specific values, or a specific date and time frame that you want to analyze. You can also apply filters that instantly show the top or bottom 10 values, or that meet the criteria that you specify. If needed, you can specify filter options to determine what filters display or hide. And when you no longer need a filter applied, you can remove it.

In Excel 2010, you can also use slicers to filter PivotTable data. For more information about slicers, see Use slicers to filter PivotTable data.

What do you want to do?


Learn about filtering

Filtered data displays only the subset of data that meet the criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) that you specify and hides data that you do not want displayed. Unlike filtering a cell range or table, you do not need to reapply a filter. Filters are automatically reapplied every time the PivotTable is refreshed or updated.

PivotTable filters are additive, which means that each additional filter is based on the current filter and further reduces the subset of data. In a subset of data, you can create up to three types of filters at the same time: manual, label or date, and value, and they are evaluated in that order. However, if you want to allow multiple filters per field, you must turn this option on before you get started. The option is turned off by default.

Filter buttons are displayed by default. However, you can turn them on or off as needed.

When filtering in a PivotTable report, be aware that you:

Watch a video

To see how you can use filters in a PivotTable report, watch Video: Filter items in a PivotTable report.

Top of Page Top of Page

Specify filter options

Allow multiple filters per field

  1. Click anywhere in the PivotTable report.
  2. On the Options tab, in the PivotTable group, click Options.

PivotTable group on the Options tab under PivotTable Tools

  1. In the PivotTable Options dialog box, click the Totals & Filters tab.
  2. Under Filters, select or clear the Allow multiple filters per field check box to allow or prevent the use of multiple filters per field.

Top of Page Top of Page

Include or exclude filtered items in totals

  1. Click anywhere in the PivotTable report.
  2. On the Options tab, in the PivotTable group, click Options.

PivotTable group on the Options tab under PivotTable Tools

  1. In the PivotTable Options dialog box, click the Totals & Filters tab.
  2. Under Filters, do any of the following:
  • To include or exclude filtered items in totals, do the following:
  • Select or clear the Include filtered items in totals check box.
  • To add a * indicator to totals outside of fields that have visual totals turned off, select the Mark totals with * check box. To remove the *, clear the check box.

 Note    These options are available only in PivotTables that are connected to an OLAP data source that supports the MDX expression subselect syntax and non-visual totals. The options affect all fields in the PivotTable. You cannot set them for individual fields.

  • To include or exclude filtered items in set totals, select or clear the Include filtered items in set totals check box.

 Note    This option is available only in PivotTables that are connected to an OLAP data source.

  • To include or exclude filtered items in subtotals, select or clear the Subtotal filtered page items check box.

Top of Page Top of Page

Display or hide field captions and filter drop downs

  1. Click anywhere in the PivotTable report.
  2. On the Options tab, in the PivotTable group, click Options.

PivotTable group on the Options tab under PivotTable Tools

  1. In the PivotTable Options dialog box, click the Display tab.
  2. To display or hide the field captions and filter drop downs, select or clear the Display field captions and filter drop downs check box.

 Tip    You can also click the Field Headers button on the ribbon (PivotTable Tools, Options tab, Show group).

Top of Page Top of Page

Use a report filter to filter items

By using a report filter, you can quickly display a different set of values in the PivotTable report. By default report filters are displayed in rows above the PivotTable, but you can display multiple report filters in columns instead. Items you select in the report filter are displayed in the PivotTable report, and items that are not selected will be hidden. If you want to display report filter pages (the set of values that match the selected report filter items) on separate worksheets, you can specify that option.

Add a report filter to the PivotTable report

  1. In the PivotTable Field List, drag the field that you want to use as a report filter to the Report Filter area.

You can repeat this step to create more than one report filter. Report filters are displayed above the PivotTable report for easy access.

  1. To change the order of the fields, in the Report Filter area, drag the fields to the position that you want. The order of the report filters will be reflected above the PivotTable report.

Top of Page Top of Page

Display report filters in rows or columns above the PivotTable report

  1. Click the PivotTable report or the associated PivotTable report of a PivotChart report.
  2. Do one of the following:
  • On the Options tab, in the PivotTable group, click Options.

PivotTable group on the Options tab under PivotTable Tools

  • Right-click anywhere in the PivotTable report, and then click PivotTable Options.
  1. In the PivotTable Options dialog box, click the Layout & Format tab.
  2. Under Layout, in the Display fields in report filter area list box do one of the following:
  • To display report filters in columns from left to right, select Over, Then Down.
  • To display report filters in rows from top to bottom, select Down, Then Over.
  1. 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

Select items in the report filter

  1. In the PivotTable report, click the arrow Filter drop-down arrow in the report filter.

Report filter arrow

  1. To display a check box for all items so that you can clear or select them as needed, select the Select Multiple Items check box.
  2. To hide or display items in the PivotTable report, do any of the following:
  • Clear the check boxes of items that you do not want to display in the PivotTable report.
  • Clear the check box of (All) to clear all currently selected check boxes, and then select the check boxes of items that you want to display in the PivotTable report.
  • Select the check box of (All) to display all items.

If items have hierarchical levels, you can display or hide the lower-level items by clicking Plus box or Minus box beside a level.

  1. Click OK. At least one check box should be selected for this button to be enabled.

The report filter clearly shows that items are filtered.

Report filter with USA selected

 Note   If you are using an 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 is Microsoft SQL Server Analysis Services (version 2005 or later), 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 report filter pages on separate worksheets

  1. Click anywhere in the PivotTable report (or the associated PivotTable report of a PivotChart report) that has one or more report filters.
  2. On the Options tab, in the PivotTable group, click the arrow next to Options, and then click Show Report Filter Pages.

  1. In the Show Report Filter Pages dialog box, select a report filter field, and then click OK.

Top of Page Top of Page

Filter items manually by selecting row or column label fields

Depending of the layout of the PivotTable report, the list of row and column labels may contain text labels such as names or geographic regions, dates or times, or numbers, such as order IDs or amounts.

  1. In the PivotTable report, click the arrow Filter drop-down arrow on Row Labels or Column Labels.

Row Labels filter box

  1. In the list of row or column labels, select or clear the check boxes of the labels of fields that you want to display or hide in the PivotTable report.

If the list is large, clear the (Select All) check box at the top, and then select the check boxes of the fields that you want to display.

 Tip   To enlarge the Row or Column Label filter list, click and drag the sizing handle at the bottom right of the list.

  1. Click OK.

The report filter clearly shows that items are filtered.

Row Labels filter box items selected

Top of Page Top of Page

Filter items by applying a label filter

  1. In the PivotTable report, do one of the following:
  • Click the arrow Filter drop-down arrow on Row Labels or Column Labels, click Label Filters, and then click the comparison operator command that you want to use.

For example, to filter by text that begins with a specific character, select Begins With, or to filter by text that has specific characters anywhere in the text, select Contains.

 Note    Label Filters is not available when row label or column label fields do not contain text-based labels.

  1. In the Label Filter <Field name> dialog box, in the box on the right, enter the data that you want to use as criteria in the comparison.

For example, to filter by text that begins with the letter "J", enter J, or to filter by text that has "bell" anywhere in the text, enter bell.

In a non-OLAP data source, if you need to find data that shares some characters but not others, use a wildcard character.

ShowHow to use wildcard characters

The following wildcard characters can be used as comparison criteria for text filters.

Use To find
? (question mark) Any single character
For example, sm?th finds "smith" and "smyth"
* (asterisk) Any number of characters
For example, *east finds "Northeast" and "Southeast"
~ (tilde) followed by ?, *, or ~ A question mark, asterisk, or tilde
For example, fy06~? finds "fy06?"

Top of Page Top of Page

Filter items by applying a value filter

  1. In the PivotTable report, do one of the following:
  1. In the Value Filter <Field name> dialog box, under Show items for which do the following:
  1. In the first box, select the field you that you want to filter.
  2. In the second box, click the comparison operator (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.) command that you want to use.

For example, to filter by a lower and upper number limit, select Between.

  1. In the third box, enter the number that you want to use in the comparison.

If a comparison, such as Between, provides a fourth box, enter another number in that box.

For example, to filter by a lower number of 25 and an upper number of 50, enter 25 and 50.

Top of Page Top of Page

Filter items by applying a date filter

 Note   For an OLAP data source, date filters require the OLAP cube field hierarchy data type of time. If a date is entered as text in a text field, then the date filter is not available.

  1. In the PivotTable report, do one of the following:
  • Click the arrow Filter drop-down arrow on Row Labels or Column Labels, click Date Filters, and then click the comparison operator command or any predefined date format that you want to use.

When you select a comparison operator such as Before or After, you create a common date filter. When you select a predefined date format such as Today or Next Week, you create a dynamic date filter, where the filter results can change when you reapply the filter.

 Notes 

  • The commands under the All Dates in the Period menu, such as January or Quarter 2, filter by the period no matter what the year. This can be useful, for example, to compare sales by a period across several years.
  • This Year and Year to Date are different in the way that future dates are handled. This Year can return dates in the future for the current year, whereas Year to Date only returns dates up to and including the current date.

 Note   This method does not provide dynamic date filters. You can only apply a common date filter.

  1. In the Date Filter <Field name> dialog box, do the following:
  • In the first box, verify or select a comparison operator.

For example, to filter by a lower and upper date or time, select is between.

  • In the second box, enter a date or time. You can also click the Calendar button to find and enter a date

If a comparison, such as Between, provides a fourth box, enter another date or time in that box.

For example, to filter by an earlier date of "3/1/2006" and a later date of "6/1/2006", enter 3/1/2006 and 6/1/2006. Or, to filter by an earlier time of "8:00 AM" and a later time of "12:00 PM", enter 8:00 AM and 12:00 PM

Top of Page Top of Page

Apply a filter to display the top or bottom 10 items

  1. In the PivotTable report, do one of the following:
  • Click the arrow Filter drop-down arrow on Row Labels or Column Labels, click Value Filters, and then click Top 10.
  • Right-click a value field, click Filter, and then click Top 10.
  1. In the Top 10 Filter <Field Name> dialog box, do the following.
  1. In the first box, click Top or Bottom.
  2. In the second box, enter a number.
  3. In the third box, do one of the following:
  • To filter by number of items, click Items.
  • To filter by percentage, click Percent.
  • To filter by sum, click Sum.
  1. In the fourth box, select the field that you want to use from the drop-down list.

Top of Page Top of Page

Filter by selection to display or hide selected items only

  1. In a PivotTable report, select one or more items in the field that you want to filter by selection.

You can make a discontinuous selection by holding down CTRL when you select items.

  1. Right-click an item in the selection, and then click Filter.
  2. Do one of the following:
  • To display the selected items, click Keep Only Selected Items.
  • To hide the selected items, click Hide Selected Items.

 Tip    You can display hidden items again by removing the filter. Right-click another item in the same field, click Filter, and then click Clear Filter.

Top of Page Top of Page

Remove filters

To remove filtering in a PivotTable report, do one of the following:

  • To remove a report filter, click the arrow Filter drop-down arrow in the report filter, and then select the (All) check box.
  • To remove all filtering, click anywhere in the PivotTable, and then on the Options tab, in the Actions group, click Clear, and then click Clear Filters.

Actions group on the Options tab under PivotTable Tools

  • To remove filtering from a row or column label field, click the arrow Filter drop-down arrow in the row or column label, and then click Clear Filter from <Field Name>.
  • To remove a label, date, or value filter, click the arrow Filter drop-down arrow in the row or column label, click Label Filter, Date Filter, or Value Filter, and then click Clear Filter.

Top of Page Top of Page

 
 
Applies to:
Excel 2010