Related topics
×
PivotTables
PivotTables

Filter data in a PivotTable

PivotTables are great for taking large datasets and creating in-depth detail summaries. 

Make a big impact with Announcements

You can insert one or more slicers for a quick and effective way to filter your data. Slicers have buttons you can click to filter the data, and they stay visible with your data, so you always know what fields are shown or hidden in the filtered PivotTable.

  1. Select any cell within the PivotTable, then go to Pivot Table Analyze > Insert Slicer Slicer.

  2. Select the fields you want to create slicers for. Then select OK.

  3. Excel will place one slicer on the worksheet for each selection you made, but it's up to you to arrange and size them however is best for you.

  4. Click the slicer buttons to select the items you want to show in the PivotTable.

Slicer choices with the multi-select button highlighted

Manual filters use AutoFilter. They work in conjunction with slicers, so you can use a slicer to create a high-level filter, then use AutoFilter to dive deeper.

  1. To display AutoFilter, select the Filter drop-down arrow  Filter drop-down arrow, which varies depending on the report layout.

    Compact Layout   

    A PivotTable in the default Compact form with the Value field in the Rows area

    A PivotTable in the default Compact form with the Value field in the Columns area

    The Value field is in the Rows area

    The Value field is in the Columns area

    Outline/Tabular Layout   

    A PivotTable in Outline or Tabular form

    Displays the Values field name in the top left corner

  2. To filter by creating a conditional expression, select Label Filters, and then create a label filter.

  3. To filter by values, select Values Filters and then create a values filter.

  4. To filter by specific row labels (in Compact Layout) or column labels (in Outline or Tabular Layout), uncheck Select All, and then select the check boxes next to the items you want to show. You can also filter by entering text in the Search box.

  5. Select OK.

Tip: You can also add filters to the PivotTable's Filter field. This also gives you the ability to create individual PivotTable worksheets for each item in the Filter field. For more information, see Use the Field List to arrange fields in a PivotTable.

You can also apply filters to show the top or bottom 10 values or data that meets the certain conditions.

  1. To display AutoFilter, select the Filter drop-down arrow  Filter drop-down arrow, which varies depending on the report layout.

    Compact Layout   

    A PivotTable in the default Compact form with the Value field in the Rows area

    A PivotTable in the default Compact form with the Value field in the Columns area

    The Value field is in the Rows area

    The Value field is in the Columns area

    Outline/Tabular Layout   

    A PivotTable in Outline or Tabular form

    Displays the Values field name in the top left corner

  2. Select Values Filters Top 10.

  3. In the first box, select Top or Bottom.

  4. In the second box, enter a number.

  5. In the third box, do the following:

    • To filter by number of items, pick Items.

    • To filter by percentage, pick Percentage.

    • To filter by sum, pick Sum.

  6. In the fourth box, select a Values field.

By using a report filter, you can quickly display a different set of values in the PivotTable. Items you select in the filter are displayed in the PivotTable, and items that are not selected will be hidden. If you want to display 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

  1. Click anywhere inside the PivotTable.

    The PivotTable Fields pane appears.

  2. In the PivotTable Field List, click on the field in an area and select Move to Report Filter.

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

  • To change the order of the fields, in the Filters area, you can either drag the fields to the position that you want, or double-click on a field and select Move Up or Move Down. The order of the report filters will be reflected accordingly in the PivotTable.

Display report filters in rows or columns

  1. Click the PivotTable or the associated PivotTable of a PivotChart.

  2. Right-click anywhere in the PivotTable, and then click PivotTable Options.

  3. In the Layout tab, specify these options:

    1. In Report Filter area, in the Arrange fields list box, do one of the following:

      • To display report filters in rows from top to bottom, select Down, Then Over.

      • To display report filters in columns from left to right, select Over, Then Down.

    2. In the Filter fields per column box, type or select the number of fields to display before taking up another column or row (based on the setting of Arrange fields you specified in the previous step).

Select items in the report filter

  1. In the PivotTable, click the dropdown arrow next to the report filter.

  2. Select the checkboxes next to the items that you want to display in the report. To select all items, click the checkbox next to (Select All).

    The report filter now displays the filtered items.

Display report filter pages on separate worksheets

  1. Click anywhere in the PivotTable (or the associated PivotTable of a PivotChart ) that has one or more report filters.

  2. Click PivotTable Analyze (on the ribbon) > Options > Show Report Filter Pages.

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

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

  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.

If you want to apply multiple filters per field, or if you don’t want to show Filter buttons in your PivotTable, here’s how you can turn these and other filtering options on or off:

  1. Click anywhere in the PivotTable to show the PivotTable tabs on the ribbon.

  2. On the PivotTable Analyze tab, click Options.

    1. In the PivotTable Options dialog box, click the Totals & Filters tab.

    2. In the Filters area, check or uncheck the Allow multiple filters per field box depending on what you need.

    3. Click the Display tab, and then check or uncheck the Display Field captions and filters check box, to show or hide field captions and filter drop downs.

You can view and interact with PivotTables in Excel for the web by creating slicers and by manual filtering.

Slicers provide buttons that you can click to filter tables, or PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed.

Slicer with selected items

For more information, see Use slicers to filter data.

If you have the Excel desktop application, you can use the Open in Excel button to open the workbook and create new slicers for your PivotTable data there. Click Open in Excel and filter your data in the PivotTable.

Manual filters use AutoFilter. They work in conjunction with slicers, so you can use a slicer to create a high-level filter, then use AutoFilter to dive deeper.

  1. To display AutoFilter, select the Filter drop-down arrow  Filter drop-down arrow, which varies depending on the report layout.

    Single Column   

    The default Layout form with the Value field in the Rows area
    The default Layout displays the Value field in the Rows area

    Separate Column   

    The Separate Column Layout form with the nested Row field in a distinct column
    Displays the nested Row field in a distinct column

  2. To filter by creating a conditional expression, select <Field name>Label Filters, and then create a label filter.

  3. To filter by values, select <Field name>Values Filters and then create a values filter.

  4. To filter by specific row labels, select Filter, uncheck Select All, and then select the check boxes next to the items you want to show. You can also filter by entering text in the Search box.

  5. Select OK.

Tip: You can also add filters to the PivotTable's Filter field. This also gives you the ability to create individual PivotTable worksheets for each item in the Filter field. For more information, see Use the Field List to arrange fields in a PivotTable.

  1. Click anywhere in the PivotTable to show the PivotTable tabs (PivotTable Analyze and Design) on the ribbon.

  2. Click PivotTable Analyze > Insert Slicer.

  3. In the Insert Slicers dialog box, check the boxes of the fields you want to create slicers for.

  4. Click OK.

    A slicer appears for each field you checked in the Insert Slicers dialog box.

  5. In each slicer, click the items you want to show in the PivotTable.

Slicer with selected items

Tip: To change how the slicer looks, click the slicer to show the Slicer tab on the ribbon. You can apply a slicer style or change settings using the various tab options.

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

  2. In the list of row or column labels, uncheck the (Select All) box at the top of the list, and then check the boxes of the items you want to show in your PivotTable.

  3. The filtering arrow changes to this icon Applied filter icon to indicate that a filter is applied. Click it to change or clear the filter by clicking Clear Filter From <Field Name>.

    To remove all filtering at once, click PivotTable Analyze tab > Clear > Clear Filters.

By using a report filter, you can quickly display a different set of values in the PivotTable. Items you select in the filter are displayed in the PivotTable, and items that are not selected will be hidden. If you want to display 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

  1. Click anywhere inside the PivotTable.

    The PivotTable Fields pane appears.

  2. In the PivotTable Field List, click on the field in an area and select Move to Report Filter.

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

  • To change the order of the fields, in the Filters area, you can either drag the fields to the position that you want, or double-click on a field and select Move Up or Move Down. The order of the report filters will be reflected accordingly in the PivotTable.

Display report filters in rows or columns

  1. Click the PivotTable or the associated PivotTable of a PivotChart.

  2. Right-click anywhere in the PivotTable, and then click PivotTable Options.

  3. In the Layout tab, specify these options:

    1. In Report Filter area, in the Arrange fields list box, do one of the following:

      • To display report filters in rows from top to bottom, select Down, Then Over.

      • To display report filters in columns from left to right, select Over, Then Down.

    2. In the Filter fields per column box, type or select the number of fields to display before taking up another column or row (based on the setting of Arrange fields you specified in the previous step).

Select items in the report filter

  1. In the PivotTable, click the dropdown arrow next to the report filter.

  2. Select the checkboxes next to the items that you want to display in the report. To select all items, click the checkbox next to (Select All).

    The report filter now displays the filtered items.

Display report filter pages on separate worksheets

  1. Click anywhere in the PivotTable (or the associated PivotTable of a PivotChart ) that has one or more report filters.

  2. Click PivotTable Analyze (on the ribbon) > Options > Show Report Filter Pages.

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

You can also apply filters to show the top or bottom 10 values or data that meets the certain conditions.

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

  2. Right-click an item in the selection, and then click Filter > Top 10 or Bottom 10.

  3. In the first box, enter a number.

  4. In the second box, pick the option you want to filter by. The following options are available:

    • To filter by number of items, pick Items.

    • To filter by percentage, pick Percentage.

    • To filter by sum, pick Sum.

  5. In the search box, you can optionally search for a particular value.

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

  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.

If you want to apply multiple filters per field, or if you don’t want to show Filter buttons in your PivotTable, here’s how you can turn these and other filtering options on or off:

  1. Click anywhere in the PivotTable to show the PivotTable tabs on the ribbon.

  2. On the PivotTable Analyze tab, click Options.

    1. In the PivotTable Options dialog box, click the Layout tab.

    2. In the Layout area, check or uncheck the Allow multiple filters per field box depending on what you need.

    3. Click the Display tab, and then check or uncheck the Field captions and filters check box, to show or hide field captions and filter drop downs.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Video: Filter data in a PivotTable

Create a PivotTable to analyze worksheet data

Create a PivotTable to analyze external data

Create a PivotTable to analyze data in multiple tables

Sort data in a PivotTable

Group or ungroup data in a PivotTable

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×