Home > Pivot > Filters > Report

Excel Pivot Table Report Filters

After you summarize data in a Pivot Table, you can use Report filters, to focus on specific portions of the data. Change Report filter layout, to save space on your worksheet.

Tip: You can also use Pivot Table Slicers to filter an Excel pivot table.

connected slicers

Macros: To automate some report filter tasks, you can use Excel macros. On the Report Filter Macros page, you'll find macros to scroll through report filter items, change all filters, sort report filter fields, and more.

Using Report Filters 

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

Video Timeline

  • 0:00 Add Report Filter to Pivot Table
  • 0:50 Use Report Filter
  • 1:11 Clear Report Filter
  • 1:19 Add 2nd Report Filter
  • 1:46 Select Multiple Items
  • 2:13 Report Filters Not Dependent

Report Filter Example

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

For example, instead 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 screenshot shown below:

  • There are Report Filters for Region and City
  • Seattle has been selected in the City Report Filter.
  • A small filter icon is on the City drop down arrow button, to show that a filter is applied

report filter region city

Add a 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 Filters box, as shown in the screen shot below.

add report filter

See the Report Filter

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.

report filter in place

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 in the drop-down list, to select it
  3. Then, to filter for the selected item, click the OK button.
    • Or, if you change your mind, and don't want to apply the filter, click the Cancel button

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

select item in report filter

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.

Note: The Report Filter will show (Multiple Items), if two or more items have been selected.

select multiple items

Allow Multiple Selections

To allow multiple selections in a filter, follow these steps:

  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.

After you click OK, the pivot table shows the summarized values for all of the selected items.

Video: Quickly Clear All Filters 

When you've finished analyzing the filtered data in a pivot table, use this shortcut, to quickly see all the data again. The Clear Filter button can save you lots of time!

Thanks to AlexJ, who shared this tip. You can find more of his tutorials and tips here: AlexJ's Excel sample files.

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.

  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.

clear report filter

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

report filters not dependent

Video: Change 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.

If you add several filters, that list can get rather long, and it pushes the data down the worksheet.

For example, there are 7 Report Filters in the pivot table shown below. That long list uses lots of valuable real estate at the top of the worksheet!

vertical stack of report filters

Report Filter Layout Options

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

  • Set a limit for the number of fields in the vertical list
    • Excel will automatically create more vertical lists, if needed
  • OR, Change the report filters to a horizontal layout. Excel will show the Report Filters across the row

Report Filter Layout Tips

Here are a couple of tips to consider, when you're selecing the layout options for a pivot table's report filters.

  • All fields in the report filter layout should be easy to reach, without scrolling
  • Do not spread report filter fields out too far across the worksheet.
  • Avoid a long column of filters at the top of a pivot table
  • That will push the pivot table body far down the worksheet.

Report Filter Layout Options

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.

The basic structure will be either in rows (horizontal) or columns (vertical)

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

To limit number of fields in 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

report filter layout settings

After you select those layout option setttings, the Report Filters change, to show the specified number of fields per column.

▶  Note: Changing the layout might create blank rows above the filters. Those blank rows can be deleted.

report filters with number limit

To show Report Filters across row:

  1. Right-click a cell in the pivot table, and click Pivot Table Options
  2. On the Layout & Format tab, click the drop down arrow beside 'Display Fields in Report Filter Area'
  3. Click 'Over, Then Down'
  4. 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.

    report filters layout option settings

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

report filters horizontal, short stacks

Grouping Dates in Report Fields

If you put a date field in the Report Filter area, there might be a long list of dates in the drop down list. Instead of seeing the individual dates, you might prefer to group them, by year or month.

However, if you right-click on the Report Filter field, there isn’t a command that lets you group the data.

report filters no grouping command

Note: You cannot apply dynamic date fields on a Report field, such as Last Year, Next Month, or Yesterday. Those date filters are only available in the Row or Column fields.

For more information, to see a Report Filter Date Filters video, go to the Pivot Table Date Filters page.

Workaround to Group Report Field Dates

Fortunately, there is a workaround that solves the Report Filter grouping problem. It’s not an elegant solution, but it works!

To enable the grouping command, you’ll temporarily move the Report Filter field to the Row Labels area. In the screen shot below, the OrderDate field is being dragged to the Row Labels area, in the PivotTable fields pane.

move report field temporarily

Then, right-click on the field in the pivot table, and click Group. Select the Grouping options that you want, and click OK

change date grouping options

Back to Report Filter Area

After you apply the date grouping, move the grouped fields back to the Report Filter area.

In this example, the OrderDate field was grouped by Year and Month, and that created a new field – Years.

Both the Years field and the OrderDate field are dragged back to the Report Filter area.

move field back to report filters area

Now, the pivot table can be filtered by year and/or month

report filters show grouped dates

Pivot Table Slicers

Another way to filter a pivot table is with one or more Slicers. Pivot Table Slicers can apply filters to a single pivot table, or you can connect them to multiple pivot tables (from the same source data).

Then, just click a button on a Slicer, to quickly filter all the connected pivot tables.

In the screenshot below, there are two pivot tables, and two Slicers, at the bottom left - Product and Region.

connected slicers

Get the Sample File

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.

Table of Contents

Using Report Filters

Add a Report Filter

Apply a Filter

Filter for Multiple Items

Clear a Report Filter

Video: Quickly Clear All Filters

Video: Apply Multiple Filters to Pivot Table Field

Report Filters are Not Dependent

Video: Change the Report Filter Layout

Change the Report Filters Layout

Grouping Dates in Report Field

Get the Sample File

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Links

Report Filter Macros

Change Multiple Filters, Macros

Pivot Table Label Filters

Pivot Table Top 10 Filters

Pivot Table Slicers

FAQs - Pivot Tables

Interactive Pivot Table

Pivot Table Introduction

 

Last updated: June 3, 2024 9:59 AM