Filtering a field (Autofiltering)   You can filter fields in the row, column, and detail areas. When you filter a field, you select one or more items of data in the field that you want to view, and hide the other items.

  First, the Sport field is filtered to display only Golf sales ...

  ... and then the Quarter field is filtered to display only Golf sales in Qtr3.

  When you filter a field, the drop-down arrow for the filtered field changes to blue instead of black, and the AutoFilter button on the PivotTable list toolbar is selected.

Filtering a field is particularly helpful when you have a large amount of source data but you want to focus on specific areas. When you filter a field, you can display the data for a single item, or you can select some items to display and other items to hide. You can filter on more than one field at a time to further narrow the focus.

Filtering based on the data in one cell (Filter by Selection)  You can filter a field to display only data that matches the value in a selected cell. Filtering by selection is particularly useful for fields in the detail area, when you want to view all of the rows that contain a particular value.

Using a field in the Filter axis   You can also filter data by using filter fields. When you select one or more items in the filter field, the data that's displayed and calculated in the entire PivotTable list changes to reflect those items. You can add new filter fields or move existing fields to the filter area.

  Filter field

Conditional filtering   You can apply a filter to a row or column field to show the top or bottom n items based on a total. For example, you can filter for the three cities that generated the most sales or the five products that are least profitable. Instead of n items, you can also choose to filter for a certain percentage of items. For example, you can filter for the top or bottom 25% of salespeople based on the number of orders handled. If there are 40 salespeople, after the filter is applied, you will see data for 10 people.

Filters are additive. Each filter you apply is added to the ones you already have in effect. However, when you apply a filter by selection, the conditional filter already applied on the field is removed before the filter by selection is applied.

Conditional filters on a PivotTable list are independent of each other. The result of one conditional filter does not affect the result of another.

An autofilter is always applied before a conditional filter, regardless of the order in which you applied them. For example, if you set a conditional filter to show the top two cities based on sales, followed by an autofilter on the ShippedCity field to include only five cities, the PivotTable list will show the top two of the five cities you selected.

You can display all data for all fields by turning off autofiltering. However, the filter settings are retained, so that when you turn autofiltering back on, the same data that was previously displayed or hidden is again displayed or hidden.

Filter settings are retained when you move fields to change the layout. Also, if you remove a field and later add the field back to the layout, the same items are again hidden.

If you don't want to retain your filter selections, make sure the AutoFilter button is not selected before you start selecting items to filter. If the button is not selected, selecting new items to filter automatically turns filtering on and removes your former filter settings.

When items in a field are hidden by autofiltering or filter by selection, the drop-down arrow in the field label is blue. Click the blue arrow to see the filter that is in effect.

When items in a field are hidden by conditional filtering, a funnel appears to the left of the drop-down arrow . You can get more information about the current filter in the Commands and Options dialog box.

Totals can include only the displayed data, or both visible and hidden data. The default is to exclude hidden data from totals — with the exception of PivotTable lists that are based on source data from OLAP databases and published from Microsoft Excel, which include hidden data in grand totals. You can change the setting that's in effect to include or exclude hidden data in totals. This setting affects only data that's hidden by filtering row and column fields; data that's hidden by using filter fields is always excluded from totals.

To search through the data instead of filtering, or to find data that matches a pattern, you can export the PivotTable list to Microsoft Excel. For information about using the Excel Find command, see Excel Help.