Excel Pivot Table Report Filters

Contextures - Microsoft Excel Tips and Tutorials
Search Contextures Sites

Home
Excel Tips
Excel Files
Products
Blog
Contact
RSS

Contextures
Excel news
by email

Email:

Name (optional):

We respect your email privacy

Excel Pivot Table Report Filters

Report Filter Video
Using Report Filters
Add a Report Filter
Apply a Filter
Filter for Multiple Items
Clear a Report Filter
Video: Apply Multiple Filters to Pivot Table Field
Report Filters are Not Dependent
Video: Change the Report Filter Layout
Change the Report Filters Layout

Pivot Table Tutorial List

Report Filter Video

See the steps for adding and applying report filters in this short video tutorial. There are written instructions below the video.

Using Report Filters

After you summarized your data by creating an Excel Pivot Table, you can focus on specific portions of the data by using 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 below, there are Report Filters for Region and City, and Seattle has been selected in the City Report Filter.

Add a Report Filter

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

In the PivotTable Field list, click on the field that you want to use as a Report Filter.

Drag the field into the Report Filter box, as shown in the screen shot below.

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

Click the Report Filter's drop-down arrow, to see a list of items in the field.

Click on an item to select it, and click OK.

In the screen shot below, 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 below, City has been added to the Report Filter area.

In the pivot table, click the drop-down arrow for a report filter.

At the bottom of the items list, add a check mark to Select Multiple Items

Check boxes will appear beside the field items, and any currently selected item is checked.

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.

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

In the pivot table, click on the drop down arrow for a Report Filter.

Click (All), to remove the filter criteria, and show all the data.

If other Report Filters have criteria applied, follow the same steps to clear their criteria.

Video: Apply Multiple Filters to Pivot Table Field

You can filter a pivot field in a pivot table, to see specific results. However, when you apply a different filter, the first filter is removed.

Watch this video to see how you can apply multiple pivot table filters at the same time. With this technique, you can use a Label filter, Value filter and Manual filter simultaneously, to fine tune your pivot table reports.

Report Filters are Not Dependent

The 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 below, 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.

In Excel 2010, and later versions, use Slicers to see related items from other fields, after applying a filter

Video: Change the Report Filters Layout

By default, the Report Filters are shown in a single vertical list at the top of a pivot table. Watch this video to see how to change the layout. The written instructions are below the video.

Change the Report Filters Layout

By default, the Report Filters are shown in a single vertical list at the top of a pivot table.

To save space, you can change the Report Filter layout. You can either:

Limit the number of fields in the vertical list, and create more vertical lists, if necessary

Show the Report Filters across the row

The report filters should be easily accessible, not spread out too far across the worksheet. Avoid a long column of filters above the pivot table, pushing the pivot table body far down the worksheet.

In the PivotTable Options, you can change the 'Display Fields in Report Filter Area' option, to find the best balance of height and width for the report filter layout. The report filters can be arranged in the following layouts:

a single column,

a single row,

columns of a set number of filters,

rows of a set number of filters.

For column arrangements, use the Down, Then Over option, and for row arrangements, use the Over, Then Down option.

To limit the number of fields in the vertical list:

Right-click a cell in the pivot table, and click Pivot Table Options

On the Layout & Format tab, the 'Display Fields in Report Filter Area' is set for 'Down, Then Over'

In the 'Report filter fields per column' box, select the number of filters to go in each column.
NOTE: The default setting is zero, which means "No limit"

Click OK to close the PivotTable Options dialog box

The Report Filters change to show the specified number of fields per column.

NOTE: Changing the layout might create blank rows above the filters, and those can be deleted.

To show the Report Filters across the row:

Right-click a cell in the pivot table, and click Pivot Table Options

On the Layout & Format tab, click the drop down arrow beside 'Display Fields in Report Filter Area'

Click 'Over, Then Down'

In the 'Report filter fields per row' box, select the number of filters to go across each row.
NOTE: If the number is set at zero, all the filters will be shown in one row.

Click OK to close the PivotTable Options dialog box

The Report Filters change to a horizontal layout, with the specified number of fields per row.

NOTE: Changing the layout might create blank rows above the filters, and those can be deleted.

Pivot Table Tutorials

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

Pivot Table Books

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

Pivot Table Add-Ins

Pivot Power
Pivot Play PLUS

Pivot Table Videos

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

Home

Excel Tips

Excel Files

Products

Blog

Contact

Privacy Policy

Contextures Inc., Copyright �2013
All rights reserved.

Debra Dalgleish

Last updated: November 15, 2009 2:24 PM

Contextures Excel news by email Email: Name (optional): We respect your email privacy	Excel Pivot Table Report Filters Report Filter Video Using Report Filters Add a Report Filter Apply a Filter Filter for Multiple Items Clear a Report Filter Video: Apply Multiple Filters to Pivot Table Field Report Filters are Not Dependent Video: Change the Report Filter Layout Change the Report Filters Layout Pivot Table Tutorial List Report Filter Video See the steps for adding and applying report filters in this short video tutorial. There are written instructions below the video. Using Report Filters After you summarized your data by creating an Excel Pivot Table, you can focus on specific portions of the data by using 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 below, there are Report Filters for Region and City, and Seattle has been selected in the City Report Filter. Add a Report Filter To use a pivot table field as a Report Filter, follow these steps. In the PivotTable Field list, click on the field that you want to use as a Report Filter. Drag the field into the Report Filter box, as shown in the screen shot below. 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 below, 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. Click the Report Filter's drop-down arrow, to see a list of items in the field. Click on an item to select it, and click OK. In the screen shot below, 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 below, City has been added to the Report Filter area. In the pivot table, click the drop-down arrow for a report filter. At the bottom of the items list, add a check mark to Select Multiple Items Check boxes will appear beside the field items, and any currently selected item is checked. 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. 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 a 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. In the pivot table, click on the drop down arrow for a Report Filter. Click (All), to remove the filter criteria, and show all the data. If other Report Filters have criteria applied, follow the same steps to clear their criteria. Video: Apply Multiple Filters to Pivot Table Field You can filter a pivot field in a pivot table, to see specific results. However, when you apply a different filter, the first filter is removed. Watch this video to see how you can apply multiple pivot table filters at the same time. With this technique, you can use a Label filter, Value filter and Manual filter simultaneously, to fine tune your pivot table reports. Report Filters are Not Dependent The 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 below, 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. In Excel 2010, and later versions, use Slicers to see related items from other fields, after applying a filter Video: Change the Report Filters Layout By default, the Report Filters are shown in a single vertical list at the top of a pivot table. Watch this video to see how to change the layout. The written instructions are below the video. Change the Report Filters Layout By default, the Report Filters are shown in a single vertical list at the top of a pivot table. To save space, you can change the Report Filter layout. You can either: Limit the number of fields in the vertical list, and create more vertical lists, if necessary Show the Report Filters across the row The report filters should be easily accessible, not spread out too far across the worksheet. Avoid a long column of filters above the pivot table, pushing the pivot table body far down the worksheet. In the PivotTable Options, you can change the 'Display Fields in Report Filter Area' option, to find the best balance of height and width for the report filter layout. The report filters can be arranged in the following layouts: a single column, a single row, columns of a set number of filters, rows of a set number of filters. For column arrangements, use the Down, Then Over option, and for row arrangements, use the Over, Then Down option. To limit the number of fields in the vertical list: Right-click a cell in the pivot table, and click Pivot Table Options On the Layout & Format tab, the 'Display Fields in Report Filter Area' is set for 'Down, Then Over' In the 'Report filter fields per column' box, select the number of filters to go in each column. NOTE: The default setting is zero, which means "No limit" Click OK to close the PivotTable Options dialog box The Report Filters change to show the specified number of fields per column. NOTE: Changing the layout might create blank rows above the filters, and those can be deleted. To show the Report Filters across the row: Right-click a cell in the pivot table, and click Pivot Table Options On the Layout & Format tab, click the drop down arrow beside 'Display Fields in Report Filter Area' Click 'Over, Then Down' In the 'Report filter fields per row' box, select the number of filters to go across each row. NOTE: If the number is set at zero, all the filters will be shown in one row. Click OK to close the PivotTable Options dialog box The Report Filters change to a horizontal layout, with the specified number of fields per row. NOTE: Changing the layout might create blank rows above the filters, and those can be deleted.