Add Filter to Pivot Table

Pivot tables can provide a great deal of information and analysis about the data contained in a worksheet, but, sometimes, even the most well-designed pivot table can display more information than you need. In these cases, it can be helpful to set up filters within your pivot table. Filters can be set up once and then changed as needed to display different information for different users. Here's how to add filter functionality to pivot tables so you can gain more control over the data that is displayed.

Steps

1. Launch Microsoft Excel.
2. Browse to and open the workbook file containing the pivot table and source data for which you need filter data.
3. Select the worksheet containing the pivot tab and make it active by clicking the appropriate tab.
4. Determine the attribute by which you want to filter data in your pivot table.
   • The attribute should be one of the column labels from the source data that is populating your pivot table.
   • For example, assume your source data contains sales by product, month and region. You could choose any one of these attributes for your filter and have your pivot table display data for only certain products, certain months or certain regions. Changing the filter field would determine which values for that attribute are shown.
5. Force the Pivot Table Wizard or Field List to launch by clicking a cell inside the pivot table.
6. Drag and drop the column label field name you wish to apply as a filter to the "Report Filter" section of the pivot table field list.
   • This field name may already be in the "Column Labels" or "Row Labels" section.
   • It may be in the list of all field names as an unused field.
7. Set the filter to display one of the values for the field.
   • You can set the filter to display all values or only one. Click the arrow beside the filtered label and check the "Select Multiple Items" check box if you would like to select certain values for your filter.

Tips

• Hide the rows containing the filter drop-down box, use the Protect Sheet utility and set a password if you do not want other users of your pivot table to be able to manipulate the report filter . This allows you to send different versions of the pivot table file to different users.
• You can filter data for any field name selected as a "Row Label" or "Column Label," but moving the field name into the "Report Filter" section will keep your pivot table easier to manipulate and less complicated to understand.

Related Articles

• Add Data to a Pivot Table
• Change an Excel Pivot Table Source
• Add a Column in a Pivot Table
• Create a Chart from a Pivot Table
• Calculate Difference in Pivot Table
• Create a Currency Converter With Microsoft Excel

Sources and Citations

• http://office.microsoft.com/en-us/excel-help/use-a-report-filter-in-a-pivottable-or-pivotchart-report-HP010167854.aspx
• http://www.contextures.com/Excel-Pivot-Table-Report-Filters.html

See also

• Use Google Spreadsheets
• Read and Interpret a Spread Sheet
• Edit the Contents of a Spreadsheet Cell
• Create an XL Worksheet for Continued Fractions
• Create a Graph Using a Spreadsheet
• Add a Column in a Pivot Table
• Create a Spirallic Numbers Dataset
• Do Cost Volume Profit Analysis
• Bulldog Through an Intellectually Simple Problem
• Use the Newton Raphson Method of Quickly Finding Roots

Read more ...

What links here

• Create Pivot Tables in Excel
• Become an Excel Expert