Pivot Table Filter Options

Modified on Sun, 26 Jul at 4:09 AM

Overview

Excel PivotTables provide four primary filtering methods:

Filter Type	Purpose
Pick Filters	Manually include or exclude specific items.
Label Filters	Filter row or column labels by their text.
Value Filters	Filter labels according to a summarized numeric value.
Calendar Filters	Filter dates using standard Excel periods and QQube calendar fields.

PivotTable Filter Types

Pick Filters

Purpose: Manually select one or more items from the field list.

Clear items you do not want to display, or select only the specific items that should remain in the PivotTable.

Excel PivotTable Pick Filter with manually selected items

Label Filters

Purpose: Filter a row or column field according to its label text.

In a PivotTable, fields placed in the Rows area are commonly called labels. In a Dynamic Range, the comparable option is called a Text Filter.

Label Filters can identify items that:

Equal or do not equal specific text.
Begin with or end with specific text.
Contain or do not contain specific text.

Excel PivotTable Label Filter options

Value Filters

Purpose: Filter a row or column field according to a summarized measure.

For example, you can display only Items whose Sales Amount is greater than $50.00.

Important: Create the Value Filter from the drop-down menu in the row or column label field. Do not create it from the numeric Values column itself.

Excel PivotTable Value Filter applied from a row-label field

Calendar Filters

Purpose: Filter report results by dates and calendar periods.

QQube includes calendar fields that provide additional ways to analyze each date.

For example, a single date can also be identified by:

Day of the week.
Day number within the year.
Day number within the week.
Month, quarter, and year.

Excel also provides built-in relative date filters similar to the date ranges commonly used in QuickBooks.

Excel PivotTable Calendar Filter options

Choose the Appropriate Filter

Filter Type	Use When
Pick Filter	You already know the exact items to include or exclude.
Label Filter	You want to filter according to the text in a row or column label.
Value Filter	You want to filter categories according to an amount, quantity, average, or other summarized value.
Calendar Filter	You want to limit the report to a date, period, or relative calendar range.

Expected Result

Use Pick Filters for individual selections, Label Filters for text conditions, Value Filters for numeric thresholds, and Calendar Filters for date-based reporting.