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

Watch a video

Specify filter options

Allow multiple filters per field

Include or exclude filtered items in totals

Display or hide field captions and filter drop downs

Use a report filter to filter items

Add a report filter to the PivotTable report

Display report filters in rows or columns above the PivotTable report

Select items in the report filter

Display report filter pages on separate worksheets

Filter items manually by selecting row or column label fields

Filter items by applying a label filter

Filter items by applying a value filter

Filter items by applying a date filter

Apply a filter to display the top or bottom 10 items

Filter by selection to display or hide selected items only

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.

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:

  • Cannot filter by color, font color, or icon set.

  • 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 Multidimensional Expressions (MDX) expression subselect syntax.

  • Can only do manual filtering in a report filter.

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

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

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

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

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

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.

  • In the PivotTable Options dialog box, click the Layout & Format tab.

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

  • 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

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

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

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

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

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.

    PivotTable group on the Options tab under PivotTable Tools

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

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.

  2. Click OK.

    The report filter clearly shows that items are filtered.

Row Labels filter box with items selected

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.

    • Right-click any text field label, click Filter, and then click Label Filters. In the Label Filter <Field name> dialog box, in the Show items for which the label box, 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.

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

    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

Filter items by applying a value 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 Value Filters, and then click the comparison operator command that you want to use.

    • Right-click any value field label, click Filter, and then click Value Filters. In the Value Filter <Field name> dialog box, in the Show items for which the label box, click the comparison operator command that you want to use

  2. 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 command that you want to use.

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

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

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.

      Note   

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

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

    • Right-click any date field label, click Filter, and then click Date Filters. In the Date Filter <Field name> dialog box, in the Show items for which the label box, click the comparison operator command that you want to use.

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

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

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.

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

    4. In the fourth box, select the field that you want to use from the drop-down list.

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.

  2. Right-click an item in the selection, and then click Filter.

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

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

Applies To: Excel 2010



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language