Search Contextures Sites

 

 

 

Excel Pivot Table Report Filters

Using Excel Pivot Table Report Filters  
Add an Excel Pivot Table Report Filter  
Apply a Filter  
Filter for Multiple Items  
Clear an Excel Pivot Table Report Filter  
Report Filters are Not Dependent  
Pivot Table Tutorial List  

View the steps in a short Pivot Table Report Filters video  

   

Using Excel Pivot Table Report Filters 

After you summarized your data by creating an Excel Pivot Table, you can focus on specific portions of the data by using Excel Pivot Table Report Filters.

For example, insteading of showing the sales amounts for all regions, you can select one or two regions, and show their results. Or, show the product sales for a specific city, or one salesperson, instead of the entire company's results.

In the pivot table shown at the right, there are Report Filters for Region and City, and Seattle has been selected in the City Report Filter.

   

Add an Excel Pivot Table Report Filter 

To use a pivot table field as a Report Filter, follow these steps.

  1. In the PivotTable Field list, click on the field that you want to use as a Report Filter.
  2. Drag the field into the Report Filter box, as shown in the screen shot at the right.

 

   

On the worksheet, Excel adds the selected field to the top of the pivot table, with the item (All) showing.

The values in the pivot table do not change.  

In the screen shot at the right, the Region Report Filter is now in place at the top of the pivot table.

NOTE: You can add additional Report Filters by dragging more fields to the Report Filter box in the PivotTable Field List.

   

Apply a Filter 

After you add a Report Filter, you can select an item from the filter, to change the data that is summarized in the Pivot Table.

  1. Click the Report Filter's drop-down arrow, to see a list of items in the field.
  2. Click on an item to select it, and click OK.

In the screen shot at the right, the North item in the Region field has been selected.

   

Filter for Multiple Items 

In a Report Filter, you can select multiple items, instead of selecting only one item. For example, when filtering for cities, you might want to see the results for two or more cities, instead of a single city.

In the pivot table shown at the right, City has been added to the Report Filter area.

  1. In the pivot table, click the drop-down arrow for a report filter.
  2. At the bottom of the items list, add a check mark to Select Multiple Items
  3. Check boxes will appear beside the field items, and any currently selected item is checked.
  4. To quickly remove the check marks from all the items, click the (All) check box at the top of the list, to clear its check mark. This clears all the check marks in the list.
  5. Add check marks to one or more items, then click OK.
    Note: Unless at least one item is selected, the OK button will not be available.

The Report Filter now shows (Multiple Items), indicating that two or more items have been selected. The pivot table shows the summarized values for the selected items.

 

   

Clear an Excel Pivot Table Report Filter 

When you've finished analyzing the filtered data in a pivot table, you can clear the Report Filters, to see all the data again.

  1. In the pivot table, click on the drop down arrow for a Report Filter.
  2. Click (All), to remove the filter criteria, and show all the data.
  3. If other Report Filters have criteria applied, follow the same steps to clear their criteria.

   

Report Filters are Not Dependent 

The Excel Pivot Table Report Filters are not dependent, so items that you select in one Report Filter will not affect the items available in any other Report Filters.

For example, in the pivot table shown at the right, East has been selected from the Region drop down.

However, all the cities show up in the item list for the City Report Filter. Only Boston, New York and Philadelphia are in the East region. If you select a city that's not in the East region, like Seattle, the pivot table won't show any records.

 

 

View the steps in a short Pivot Table Report Filters video  

 

 

Learn how to create Excel dashboards.

Pivot Table Tutorials

Excel Pivot Table -- Introduction 
Excel Pivot Table -- Clear Old Items
Excel Pivot Table -- Create a Pivot Table in Excel 2007 
Excel Pivot Table -- Custom Calculations 
Excel Pivot Table -- Data Field Layout
Excel Pivot Table -- Dynamic Data Source
Excel Pivot Table -- FAQs
Excel Pivot Table -- Field Settings
Excel Pivot Table -- Filter Source Data  
Excel Pivot Table -- Filters, Top 10 
Excel Pivot Table -- GetPivotData
Excel Pivot Table -- Grand Totals
Excel Pivot Table -- Grouping Data
Excel Pivot Table -- Layout, Excel 2007
Excel Pivot Table -- Multiple Consolidation Ranges
Excel Pivot Table -- Pivot Cache   
Excel Pivot Table -- PivotTable Style
Excel Pivot Table -- Printing   
Excel Pivot Table -- Protection  
Excel Pivot Table -- Report Filters
Excel Pivot Table -- Running Totals  
Excel Pivot Table -- Show and Hide Items 
Excel Pivot Table -- Sorting
Excel Pivot Table -- Subtotals 
Excel Pivot Table -- Summary Functions
Excel Pivot Table -- Unique Items

Pivot Table Books

Pivot Tables, Beginning (Excel 2007) 
Pivot Tables, Recipe Book (Excel 2003) 
Pivot Tables, Recipe Book (Excel 2007) 

Pivot Table Add-Ins

Pivot Tables - Add-in -- Pivot Power 
Pivot Tables - Add-in - Pivot Play PLUS 

Pivot Table Videos

Pivot Tables - Clear Old Items
Pivot Tables - Copy a Custom PivotTable Style
Pivot Tables - Create in Excel 2007
Pivot Tables - Create from Multiple Sheets
Pivot Tables - Data Field Layout
Pivot Tables - Date Filters, Add
Pivot Tables - GetPivotData
Pivot Tables - Group Data
Pivot Tables - Layout, Excel 2007
Pivot Tables - Report Filters, Add
Pivot Tables - Running Totals
Pivot Tables - Select Sections
Pivot Tables - Subtotals, Create Multiple
Pivot Tables - Top 10 Filters

 

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright ©2010
All rights reserved.

 

Last updated: November 15, 2009 2:24 PM