Filter data in a PivotTable report or PivotChart report

You filter data to quickly and easily find and work with a subset of data in a PivotTable report or PivotChart report.What do you want to do?

Learn about filtering

Filter labels or text items within labels

Filter numbers in the values area

Filter dates or times

Filter for top or bottom numbers

Filter by selection

Remove filters

Learn about filtering

Filtered data displays only the subset of data that meet the criteria 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.

In the PivotTable or PivotChart report, 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. You can control this behavior by selecting or clearing the Allow multiple filters per field check box in the Display tab of the PivotTable Options dialog box. For more information, see PivotTable options.

Notes:

• You can control whether the filter button is available by setting or clearing the Display field captions and filter drop downs check box in the Display tab of the PivotTable Options dialog box, or by clicking Field Headers in the Show/Hide group on the Options tab. For more information, see PivotTable options.
• You can also filter data in the PivotTable Field List. For more information, see Design the layout and format of a PivotTable report.
• You cannot filter by color, font color, or icon set in a PivotTable report or PivotChart report.
• You cannot filter by label, date or time, value, or top or bottom numbers if the PivotTable data source is an OLAP database that does not support the MDX expression subselect syntax.
• You can only do manual filtering in a report filter.

Filter labels or text items within labels

Labels

1. In the PivotTable report or PivotChart report, click the arrow in the column area (series field) or row area (category field).

   Tip To change the field without closing the Filter menu, in the Select field box at the top, select a field from the drop-down list.

2. Do one of the following:

   Select from a list of text values (manual filtering)

   • In the list of text values, select or clear one or more text values to filter by.

     If the list is large, clear (Select All) at the top, and then select the specific text values to filter by.

     Tip To make the Filter menu wider or longer, click and drag the grip handle at the bottom.

   Filter by a text value

   1. Point to Label Filters and then click one of the comparison operator commands.

      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.

   2. In the Label Filter <Field name> dialog box, in the box on the right, enter text.

      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 text that shares some characters but not others, use a wildcard character.

      How 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?"

Items

1. In the PivotTable report, right-click the item, point to Filter, and then click Label Filter.
2. In the Label Filter <Field name> dialog box, do the following:
   1. In the box on the left, click one of the comparison operator commands.

      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.

   2. In the box on the right, enter text.

      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 text that shares some characters but not others, use a wildcard character.

      How 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?"

Filter numbers in the values area

Labels

1. In the PivotTable or PivotChart report, click the arrow in the column label (series field) or row label (category field).

   Tip To change the field without closing the Filter menu, in the Select field box at the top, select a field from the drop-down list.

2. Do one of the following:

   Select from a list of numbers (manual filtering)

   • In the list of numbers, select or clear one or more numbers to filter by.

     If the list is large, clear (Select All) at the top, and then select the specific numbers to filter by.

     Tip To make the Filter menu wider or longer, click and drag the grip handle at the bottom.

   Filter by a number value

   1. Point to Value Filters and then click one of the comparison operator commands.

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

   2. In the Value Filter <Field Name> dialog box, in the box or boxes on the right, enter numbers.

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

Items

1. In the PivotTable report, right-click the item, point to Filter, and then click Value Filter.
2. In the Value Filter <Field name> dialog box, do the following:
   1. In the box on the left, select a field from the drop-down list.
   2. In the box on the middle, click one of the comparison operator commands.

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

   3. Iin the box or boxes on the right, enter numbers.

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

Filter dates or times

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 visible.

Labels

1. In the PivotTable or PivotChart report, click the arrow in the column area (series field) or row area (category field).

   Tip To change the field without closing the Filter menu, in the Select field box at the top, select a field from the drop-down list.

2. Do one of the following:

   Select from a list of dates or times (manual filtering)

   • In the list of dates or times, select or clear one or more dates or times to filter by.

     If the list of values is large, clear (Select All) at the top, and then select the values to filter by.

     Tip To make the Filter menu wider or longer, click and drag the grip handle at the bottom.

   Filter by a date or time value

   • Point to Date Filters and then do one of the following:

     Common filter

     Note A common filter is one based on a comparison operator .

     1. Click one of the comparison operator commands (Equals, Before, After, or Between), or click Custom Filter.
     2. In the Date Filter dialog box, in the box on the right, enter a date or time, select a date or time from the list, or click the Calendar button to find and enter a date.

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

     3. In the Date Filter dialog box, in the box or boxes on the right, enter a date or time, select dates or times from the list, or click the Calendar button to find and enter a date.

        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.

     Dynamic filter

     Note A dynamic filter is one where the criteria can change when you reapply the filter.

     1. Click one of the predefined date commands.

        For example, to filter all dates by the current date, select Today, or to filter by the following month, select Next Month.

     2. Click OK.

        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.

Items

1. In the PivotTable report, right-click the item, point to Filter, and then click Date Filters.
2. In the Date Filter <Field name> dialog box, do the following:
   1. Click one of the comparison operator commands (Equals, Before, After, or Between).
   2. In the Date Filter <Field Name> dialog box, in the box or boxes on the right, enter a date or time, select dates or times from the list, or click the Calendar button to find and enter a date.

      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.

Filter for top or bottom numbers

Labels

1. In the PivotTable or PivotChart report, click the arrow in the column label (series field) or row label (category field).

   Tip To change the field without closing the Filter menu, in the Select field box at the top, select a field from the drop-down list.

2. Point to Value Filters and then select Top 10.
3. In the Top 10 Filter <Field Name> dialog box, do the following.
   1. In the box on the left, click Top or Bottom.
   2. In the box on the middle left, enter a number.
   3. In the box on the middle right, 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.
   4. In the box on the right, select a field from the drop-down list.

Items

1. In the PivotTable report, right-click the item, point to Filter, and then click Top 10.
2. In the Top 10 <Field name> dialog box, do the following:
   1. In the box on the left, click Top or Bottom.
   2. In the box on the middle left, enter a number.
   3. In the box on the middle right, do one of the following:
      • To filter by number of items, click Items.
      • To filter by percentage, click Percent.
   4. In the box on the right, select a field from the drop-down list.

Filter by selection

You can quickly filter data with criteria that is equal to the contents of the active field.

1. In a PivotTable report or PivotChart report, select one or more items in the field that you want to filter by selection.
2. Right-click an item in the selection.
3. Click Filter, and then click Keep Only Selected Items or Hide Selected Items.

Remove filters

1. Do one of the following:
   • To remove all filtering on the Options tab, in the Actions group (PivotTable report), or on the Analyze tab, in the Data group (PivotChart report), click Clear, and then click Clear Filters.
   • To remove filtering for a specific field, click the arrow in the row or column label, and then click Clear Filter from <Field Name>.

     Tip To change the field without closing the Filter menu, in the Select field box at the top, select a field from the drop-down list.