How to filter a pivot table globally

There are many ways to filter information in a pivot table, but the report filter is the most basic tool to limit information in a pivot table globally.

Let’s take a look.

By default, a new pivot table will summarize all of the source data. However, the report filter gives you a way to quickly limit the information that appears in the table by filtering on one or more fields.

For example, this pivot table shows Total Sales by product. If we add the City field as a Column Label, we see a breakdown of sales by city in each column. If we now add the field Region as a report filter, we’ll gain the ability to filter the report globally by region.

By default, a new report filter shows all items. If we click the arrow next to the report filter, we see a drop-down menu that shows all items that appear in the Region field. When we select West, we see only sales for cities in the West region. If we select East, we see sales for Cities in the East.

The “Select Multiple Items” setting, allows you to select more than one item at a time. For example, we can show sales for the Midwest and South.

To clear the report filter, you have two options. You can select All in the menu, or, if we undo that, you can also just drag the Region field out of the Report Filter area.

Report filters maintain a “memory” of their last configuration. If we drag Region back into the Report Filter area, we see it is still filtering to show only the Midwest and South regions. Select all to reset the filter.

You can add more than one field to the report filter at the same time. For example, we can add the Category field to the report filter, and then filter to show only Products in the Exotic category in the West region.

If you want to clear all filters from a pivot table at once, you can use the clear menu, on the Options tab on the PivotTable Tools ribbon. Click Clear, then select, Clear filters. This will clear all report filters, and any other filters in the pivot table.

Note that slicers are another way to filter an entire pivot table. We’ll look at Slicers in an upcoming video.