How to apply multiple filters to Pivot Table at Once in Excel?

Understanding Default Pivot Table Filter Limitations

By default, Excel only allows you to apply one filter per field in a pivot table. This means that if you apply a filter based on the item label (a label filter), and subsequently try to apply a filter based on the item’s aggregated data (a value filter), the original label filter is immediately removed or overwritten. This default configuration simplifies the interface but often frustrates users requiring detailed, layered filtering.

The software is designed this way to maintain performance and prevent potentially conflicting filter types from being applied accidentally. However, we can change this default setting, allowing for more advanced conditional analysis, by using the PivotTable Options button.

The following practical example shows exactly how to encounter this limitation and, more importantly, how to successfully implement the necessary setting change to overcome it. We will demonstrate how to successfully combine a Label Filter and a Value Filter on the same field.

Example: Applying Multiple Filters to an Excel Pivot Table Field

Let us begin with a common scenario. Suppose we have the following pivot table in Excel that summarizes the total sales of various products. Our goal is to filter this list based on two distinct criteria: the product name must contain a specific word, AND the total sales must exceed a certain threshold.

This initial table provides the raw summary data. We are interested in refining the visibility of our data by focusing only on items that meet strict, simultaneous requirements. The field we are focusing on is the Row Labels field, which contains the names of the products.

Step 1: Applying the Initial Label Filter

Our first filtering criterion is based on the label, or text content, of the row. Suppose we want to see only products whose names contain the word “shirt.” We initiate this process by clicking the dropdown arrow next to Row Labels. From the context menu that appears, we then click Label Filters, and finally, we select the Contains option.

We proceed to input the specific text, “shirt,” into the filter dialogue box. This action filters the table to show only those rows that contain “shirt” in the row label, effectively narrowing our focus based on product nomenclature.

At this point, the Pivot Table is successfully filtered using a Label Filter. The filter icon next to Row Labels indicates that criteria are currently active. Now we move to apply our second, numeric criterion.

Step 2: Attempting to Apply the Value Filter (The Conflict)

Now, suppose we would also like to filter the remaining rows further, specifically selecting only those where the sum of sales is greater than 10. This requires applying a Value Filters based on the calculated sales amount.

We again click the dropdown arrow next to Row Labels, then click Value Filters, and subsequently click the Greater Than option:

We then specify the condition to filter for rows where the sum of sales is greater than 10, completing the setup for the value filter:

However, upon successful application of the value filter, we immediately notice the conflict inherent in Excel’s default settings: the previous label filter has been removed. The Pivot Table now only displays rows where the total sales are greater than 10, regardless of the product name.

This illustrates the core problem: By default, Excel does not allow multiple filter types (Label and Value) to be active simultaneously within one field in a pivot table. We must change this behavior to perform the desired combined analysis.

Step 3: Accessing and Modifying PivotTable Options

To resolve this filter conflict, we need to access the advanced settings for the Pivot Table. This can be accomplished by right-clicking anywhere within the Pivot Table area and selecting PivotTable Options… from the context menu. Alternatively, you can select the Pivot Table and navigate to the PivotTable Analyze tab in the ribbon, then click the Options button.

Once the PivotTable Options dialog box appears, it contains several tabs dedicated to different aspects of Pivot Table behavior, formatting, and calculation. The specific modification we require is found under the section dedicated to calculations and filtering protocols.

Navigating through these options ensures that the Pivot Table functions according to your specific analytical needs, especially when dealing with complex, multi-layered criteria. The default settings are conservative, prioritizing speed and general usage, but are easily customized for power users.

Step 4: Enabling Multiple Filters Per Field

Within the PivotTable Options window, locate and click the Totals & Filters tab. This section manages how subtotals, grand totals, and filtering behaviors are handled by the Pivot Table engine.

Towards the bottom of this tab, look for the filtering section. You will find a critical setting that reads: Allow multiple filters per field. This is the setting that dictates whether Excel can stack different filter types (like Label and Value filters) on a single field simultaneously.

In the new window that appears, click the Totals & Filters tab, then check the box next to Allow multiple filters per field, then click OK:

By checking this box, you are instructing Excel to store and apply multiple filter parameters against the same data field, significantly enhancing the granularity of your data cuts. Once you confirm by clicking OK, the Pivot Table is configured to accept layered filtering conditions.

Step 5: Verification of Dual Filtering Success

With the setting changed, we can now repeat our filtering attempt, starting with the numeric criteria if it was previously removed, and then re-applying the label filter. If the value filter (greater than 10) is still active from Step 2, we can proceed directly to applying the label filter once more.

We click the dropdown arrow next to Row Labels, navigate to Label Filters, select Contains, and input “shirt.” This time, Excel will allow this label filter to be applied alongside the existing Value Filters without overriding it, resulting in a dual-filtered view:

Observe the final result: we are now able to filter the pivot table to only show rows that contain “shirt” and where the sum of sales is greater than 10. Both criteria are applied simultaneously to the Row Labels field, indicated by the dual filter icons typically visible in the field header. This successful implementation allows for highly specific data segmentation.

Summary of Advantages

The ability to utilize multiple filters on a single field offers significant analytical advantages, moving beyond simple selection criteria. It allows analysts to define complex subsets of data that meet stringent requirements across different dimensions of the data (label content vs. aggregated value).

• Increased Specificity: You can target data subsets with much greater precision, such as finding products that match a naming convention and exceed profitability targets.
• Streamlined Workflow: Avoids the need for creating helper columns or using complex formulas outside the Pivot Table structure to achieve the same result.
• Flexibility: The setting is persistent for that specific Pivot Table, meaning you can easily switch between different label and value filter combinations without having to re-enable the option every time.