After the data is summarized in a Pivot Table, focus on specific portions of the data, by 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.
See the steps for adding and applying report filters in this short video tutorial. There are written instructions below the video.
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.
To use a pivot table field as a Report Filter, follow these steps.
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 Filters box in the PivotTable Field List.
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.
In the screen shot below, the North item in the Region field has been selected.
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.
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.
When you've finished analyzing the filtered data in a pivot table, use this shortcut, to quickly see all the data again.
Thanks to AlexJ, who shared this tip. You can find more of his tutorials and tips here: AlexJ's Excel sample files.
When you've finished analyzing the filtered data in a pivot table, you can clear the Report Filters, to see all the data again.
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.
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
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.
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:
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:
For column arrangements, use the Down, Then Over option, and for row arrangements, use the Over, Then Down option.
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.
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.
Click here to get the zipped sample file with the Region Sales data for this tutorial. The zipped file is in xlsx format, and does NOT contain macros.
Last updated: January 12, 2022 3:59 PM