How to Add a Filter to a Pivot Table

Table of contents:

How to Add a Filter to a Pivot Table
How to Add a Filter to a Pivot Table
Anonim

PivotTables can provide great support for analyzing the data in a worksheet, but sometimes even the best (best designed) PivotTable can show more information than needed. In these cases, it may be useful to add filters. Once set, a filter can be changed to suit the viewing needs of different users. This article shows you how to add a filter to a PivotTable so that you have an adequate level of control over the displayed data.

Steps

Add Filter to Pivot Table Step 1
Add Filter to Pivot Table Step 1

Step 1. Open Microsoft Excel

Add Filter to Pivot Table Step 2
Add Filter to Pivot Table Step 2

Step 2. Navigate and open the document that contains both the pivot table and the data you want to add a filter for

Add Filter to Pivot Table Step 3
Add Filter to Pivot Table Step 3

Step 3. Select the sheet containing the pivot table by clicking on the appropriate tab

Add Filter to Pivot Table Step 4
Add Filter to Pivot Table Step 4

Step 4. Determine the attribute you want to use as a filter for the pivot table

  • The attribute must be chosen from the data columns that feed into the pivot table.
  • For example, let's assume your data contains sales by product, month, and region. You can use any of these attributes as a filter and show data for some products, some months or some regions. If you change the field used as a filter, you determine exactly which values to show.
Add Filter to Pivot Table Step 5
Add Filter to Pivot Table Step 5

Step 5. Force the Pivot Table Creation Wizard (or field list display) to run by clicking a cell within the pivot table

Add Filter to Pivot Table Step 6
Add Filter to Pivot Table Step 6

Step 6. Drag the field you want to use as a filter into the "Report Filter" section of the fields list

  • This field may already exist in the "Column Labels" or "Row Labels" section.
  • The field may also appear in the Field List as an unused element.
Add Filter to Pivot Table Step 7
Add Filter to Pivot Table Step 7

Step 7. Set the filter to show only one of the attribute values

You can set the filter to see a single value or all available ones. Click on the arrow next to the field used as a filter and select "Select Multiple Objects" if you want to select more than one value for your filter

Advice

  • Hide the rows that contain the filter selection menu, use the "Protect Sheet" feature and set a password if you don't want other users to be able to modify your PivotTable by changing the current filter. This way you can ship different versions (of the same pivot table) to different people.
  • You can filter your data using any of the fields marked as "Row Label" or "Column Label", but to simplify the use of the pivot table and its understanding it is advisable to drag the fields into the "Report Filter" section.

Recommended:

How to Add a Column to a Pivot Table

How to Add a Column to a Pivot Table

This article explains how to create and insert a new column in a PivotTable in Microsoft Excel with the PivotTable tools. You can turn a row, field or value into a column, or create a new one calculated with a custom formula. Steps Method 1 of 2:

How to Add Data to a Pivot Table: 11 Steps

How to Add Data to a Pivot Table: 11 Steps

This article explains how you can add new data to an existing Microsoft Excel PivotTable. You can perform this change on both a Windows and a Mac computer. Steps Step 1. Open the Excel file which contains the pivot table Double-click the document icon to open it directly in Excel.

How to Add Rows to a Pivot Table

How to Add Rows to a Pivot Table

Pivot tables are used to group and organize data in a spreadsheet. The primary advantage of pivot tables is that you can organize and manipulate data in a variety of ways, based on user needs and the conclusions that can be drawn from the information.

How to Add a Custom Field to a Pivot Table

How to Add a Custom Field to a Pivot Table

On some occasions, you need to enter more information than a normal pivot table can show. In these rare cases, it might be helpful to add a custom and calculated field to the pivot table. You can configure these fields to show averages, percentages, or the maximum and minimum values of a field.

How to Change the Data Source of an Excel Pivot Table

How to Change the Data Source of an Excel Pivot Table

The Microsoft Excel program is designed to allow users to sort and interpret data using advanced features such as pivot tables, formulas and macros. It may happen that a user wants to modify the input data to carry out evaluations on the results.