Filter data in a PivotTable or PivotChart report

Try Office 2010 In Excel 2010, you can use slicers to filter PivotTable data.
Watch a video or try Office 2010!

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

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.

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 Totals & Filters tab of the PivotTable Options dialog box. For more information, see PivotTable options.

 Notes 

  • You can control whether the filter button is available in a PivotTable report 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.

Top of Page Top of Page

Filter for text

Labels

  1. Do one of the following:
    • In the PivotTable report, click the arrow Filter drop-down arrow on Row Labels or Column Labels.
    • In the PivotChart Filter Pane, click the arrow Filter drop-down arrow on Axis Fields (Categories) or Legend Fields (Series).

 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.

  1. 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 (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.) 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.

  1. 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 or 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.) of a PivotChart 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 (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.) 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?"

Top of Page Top of Page

Filter numbers in the values area

Labels

  1. In the PivotTable report or 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.) of a PivotChart report, click the arrow Filter drop-down arrow in Row Labels or Column Labels.

 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.

  1. 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 (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.) commands.

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

  1. 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 or 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.) of a PivotChart 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 (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.) commands.
      For example, to filter by a lower and upper number limit, select Between.
    3. 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.

Top of Page Top of Page

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. Do one of the following:
    • In the PivotTable report, click the arrow Filter drop-down arrow on Row Labels or Column Labels.
    • In the PivotChart Filter Pane, click the arrow Filter drop-down arrow on Axis Fields (Categories) or Legend Fields (Series).

 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.

  1. 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 (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.).

  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 left, select a comparison operator.

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

  1. In the Date Filter dialog box, in the box or boxes on the right, enter a date or time, 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.

  1. 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 or 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.) of a PivotChart 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. In the box on the left, select a comparison operator.
      For example, to filter by a lower and upper date or time, select is between.
    2. In the box or boxes on the right, enter a date or time, 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.

Top of Page Top of Page

Filter for top or bottom numbers

Labels

  1. Do one of the following:
    • In the PivotTable report, click the arrow Filter drop-down arrow on Row Labels or Column Labels.
    • In the PivotChart Filter Pane, click the arrow Filter drop-down arrow on Axis Fields (Categories) or Legend Fields (Series).

 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.

  1. Point to Value Filters and then select Top 10.
  2. 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 or 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.) of a PivotChart 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.

Top of Page Top of Page

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 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.) of a 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.
  4. If items are hidden and you want to display those items, right-click a related item, point to Filter, and then click Clear Filter.

Top of Page Top of Page

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 Filter drop-down 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.

Top of Page Top of Page

 
 
Applies to:
Excel 2007