Filter data in a PivotTable

To focus on a smaller portion of a large amount of your PivotTable data for in-depth analysis, you can filter the data. There are several ways to do that. Start by inserting 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.

PivotTable slicer

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

PivotTable Tools

  1. Click Analyze > Insert Slicer.

Insert Slicer button on the Analysis tab

  1. In the Insert Slicers dialog box, check the boxes of the fields you want to create slicers for.
  2. Click OK.

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

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

To choose more than one item, hold down Ctrl, and then pick the items you want to show.

 Tip    To change how the slicer looks, click the slicer to show the Slicer Tools on the ribbon, and then apply a slicer style or change settings on the Options tab.

Other ways to filter PivotTable data

Use any of the following filtering features instead of or in addition to using slicers to show the exact data you want to analyze.



Filter data manually

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

Row Label filter

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

Select All box in the Filter gallery

To see more items in the list, drag the handle in the bottom-right corner of the filter gallery to enlarge it.

  1. Click OK.

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

 Tip    To remove all filtering at once, click anywhere in the PivotTable, and then click Analyze > Clear > Clear Filters.

Clear button on the Analyze tab

Show specific text, values, or dates only

  1. In the PivotTable, right-click any text, value, or date field label, and then click Label Filters, Value Filters, or Date Filters.
  2. Click the comparison operator command 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 don’t have text-based labels.

  1. Do one of the following:
  • In the Label Filter <Field name> dialog box, enter the text you want to filter by.

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.

If the PivotTable is based on a non-OLAP data source, you can use the following wild card characters to find data that have specific characters.

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?"
  • In the Value Filter <Field name> dialog box, enter the values you want to filter by.
  • In the Date Filter <Field name> dialog box, enter the dates you want to filter by.

If the PivotTable is based on 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, the date filter won’t be available.

 Tip    To remove a label, date, or value filter, click the arrow Applied filter icon in the row or column label, click Label Filter, Date Filter, or Value Filter, and then click Clear Filter.

Show the top or bottom 10 items

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

  1. In the PivotTable report, click the arrow Filter drop-down arrow on Row Labels or Column Labels, click Value Filters, 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, pick the option you want to filter by.
  • To filter by number of items, pick Items.
  • To filter by percentage, pick Percent.
  • To filter by sum, pick Sum.
  1. In the fourth box, pick the field you want.

Turn filtering options on or off

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 Tools on the ribbon.

PivotTable Tools

  1. On the Options tab, in the PivotTable group, click Options.

Options button on the Analyze tab

  1. In the PivotTable Options dialog box, click the Totals & Filters tab.
  2. Under Filters, do any of the following:
  • To use or not use multiple filters per field, check or uncheck the Allow multiple filters per field box.
  • To include or exclude filtered items in totals, check or uncheck the Include filtered items in totals box, and then check or uncheck the Mark totals with * box. If checked, an asterisk (*) indicator appears in totals outside of fields that have visual totals turned off. This option is available only in PivotTables that are connected to an OLAP data source that supports the MDX expression language. It affects all fields in the PivotTable.
  • To include or exclude filtered items in totals for named sets, check or uncheck the Include filtered items in set totals box. 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 box.
  1. To show or hide field captions and filter drop downs, click the Display tab, and then check or uncheck the Display field captions and filter drop downs check box.

More about PivotTables

 
 
Applies to:
Excel 2013