# Filtering Pivot Table Fields

## Content

Spread for WPF allows you to filter values in a pivot table. You can add label, value, or date filters to pivot table fields by using the [PivotFilters](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotField.PivotFilters.html) property of the [IPivotField](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotField.html) interface. To add a filter, specify a [PivotFilterType](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.PivotFilterType.html) enumeration value as a parameter in the `Add` method of the [IPivotFilters](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotFilters.html) interface.
Spread for WPF also provides the [ClearLabelFilter](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotField.ClearLabelFilters.html), [ClearValueFilter](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotField.ClearValueFilters.html), and [ClearAllFilters](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotTable.ClearAllFilters.html) methods for removing filter conditions. The [ClearLabelFilter](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotField.ClearLabelFilters.html) method removes label filters, the [ClearValueFilter](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotField.ClearValueFilters.html) method removes value filters, and the [ClearAllFilters](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotTable.ClearAllFilters.html) method removes all filters applied to the field.

## Apply Label Filter

The following example demonstrates how to use the [Add](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotFilters.Add.html) method of [IPivotFilters](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotFilters.html) with [PivotFilterType.CaptionContains](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.PivotFilterType.html) to filter the Region labels to only show items containing "Ea".
**C#**

```csharp
IWorkbook WorkBook = GcSpreadSheet1.Workbook;
IWorksheet Sheet1 = WorkBook.ActiveSheet;

// Set data.
Sheet1.SetValue(0, 0, new object[,] {
    {"OrderDate", "Region", "City", "Category", "Product", "Quantity"},
    {"2025-07-01", "East", "Jersey", "Breads", "Bakery", 1120},
    {"2025-07-01", "East", "Jersey", "Breads", "Cookie", 563},
    {"2025-07-02", "East", "Washington, DC", "Breads", "Bakery", 1281},
    {"2025-07-02", "East", "Washington, DC", "Breads", "Cookie", 546},
    {"2025-07-01", "East", "Jersey", "Beverages", "Coffee", 326},
    {"2025-07-02", "East", "Washington, DC", "Beverages", "Coffee", 205},
    {"2025-07-02", "East", "Washington, DC", "Beverages", "Juice", 186},
    {"2025-07-01", "West", "San Francisco", "Breads", "Bakery", 1262},
    {"2025-07-01", "West", "San Francisco", "Breads", "Cookie", 349},
    {"2025-07-01", "West", "Seattle", "Breads", "Bakery", 524},
    {"2025-07-01", "West", "Seattle", "Breads", "Cookie", 196},
    {"2025-07-01", "West", "San Francisco", "Beverages", "Coffee", 363},
    {"2025-07-01", "West", "Seattle", "Beverages", "Coffee", 100},
    {"2025-07-02", "East", "Jersey", "Beverages", "Juice", 120},
    {"2025-07-02", "West", "San Francisco", "Breads", "Bakery", 350},
    {"2025-07-02", "West", "Seattle", "Beverages", "Tea", 180},
    {"2025-07-02", "East", "Jersey", "Breads", "Cookie", 75},
    {"2025-07-03", "West", "San Francisco", "Beverages", "Juice", 210},
    {"2025-07-03", "East", "Boston", "Breads", "Bakery", 420}
});
IPivotCache pvCache = WorkBook.PivotCaches.Create("Sheet1!A1:F20");

// Configure pivot table's fields.
IPivotTable pvTable = pvCache.CreatePivotTable(Sheet1.Cells["I1"]);
IPivotFields pvFields = pvTable.PivotFields;
pvTable.AddDataField(pvFields["Quantity"], "Sum of quantity", ConsolidationFunction.Sum);
pvFields["Region"].Orientation = PivotFieldOrientation.Row;
pvFields["City"].Orientation = PivotFieldOrientation.Row;
pvFields["Category"].Orientation = PivotFieldOrientation.Column;
pvFields["Product"].Orientation = PivotFieldOrientation.Column;
pvFields["OrderDate"].Orientation = PivotFieldOrientation.Page;

// Filter for products where the product name contains 'Ea'.
pvFields["Region"].PivotFilters.Add(PivotFilterType.CaptionContains, 0, "Ea");
```

**VB**

```vbnet
Dim WorkBook As IWorkbook = spreadSheet1.Workbook
Dim Sheet1 As IWorksheet = WorkBook.ActiveSheet

' Set data.
Sheet1.SetValue(0, 0, New Object(,) {
    {"OrderDate", "Region", "City", "Category", "Product", "Quantity"},
    {"2025-07-01", "East", "Jersey", "Breads", "Bakery", 1120},
    {"2025-07-01", "East", "Jersey", "Breads", "Cookie", 563},
    {"2025-07-02", "East", "Washington, DC", "Breads", "Bakery", 1281},
    {"2025-07-02", "East", "Washington, DC", "Breads", "Cookie", 546},
    {"2025-07-01", "East", "Jersey", "Beverages", "Coffee", 326},
    {"2025-07-02", "East", "Washington, DC", "Beverages", "Coffee", 205},
    {"2025-07-02", "East", "Washington, DC", "Beverages", "Juice", 186},
    {"2025-07-01", "West", "San Francisco", "Breads", "Bakery", 1262},
    {"2025-07-01", "West", "San Francisco", "Breads", "Cookie", 349},
    {"2025-07-01", "West", "Seattle", "Breads", "Bakery", 524},
    {"2025-07-01", "West", "Seattle", "Breads", "Cookie", 196},
    {"2025-07-01", "West", "San Francisco", "Beverages", "Coffee", 363},
    {"2025-07-01", "West", "Seattle", "Beverages", "Coffee", 100},
    {"2025-07-02", "East", "Jersey", "Beverages", "Juice", 120},
    {"2025-07-02", "West", "San Francisco", "Breads", "Bakery", 350},
    {"2025-07-02", "West", "Seattle", "Beverages", "Tea", 180},
    {"2025-07-02", "East", "Jersey", "Breads", "Cookie", 75},
    {"2025-07-03", "West", "San Francisco", "Beverages", "Juice", 210},
    {"2025-07-03", "East", "Boston", "Breads", "Bakery", 420}
})
Dim pvCache As IPivotCache = WorkBook.PivotCaches.Create("Sheet1!A1:F20")

' Configure pivot table's fields.
Dim pvTable As IPivotTable = pvCache.CreatePivotTable(Sheet1.Cells("I1"))
Dim pvFields As IPivotFields = pvTable.PivotFields
pvTable.AddDataField(pvFields("Quantity"), "Sum of quantity", ConsolidationFunction.Sum)
pvFields("Region").Orientation = PivotFieldOrientation.Row
pvFields("City").Orientation = PivotFieldOrientation.Row
pvFields("Category").Orientation = PivotFieldOrientation.Column
pvFields("Product").Orientation = PivotFieldOrientation.Column
pvFields("OrderDate").Orientation = PivotFieldOrientation.Page

' Filter for products where the product name contains "Ea".
pvFields("Region").PivotFilters.Add(PivotFilterType.CaptionContains, 0, "Ea")
```

![image](https://cdn.mescius.io/document-site-files/images/c76ee3c1-6329-4804-9e96-99555f506bc5/image.3d4a34.png)

## Apply Value Filter

The following example demonstrates how to use the [Add](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotFilters.Add.html) method of [IPivotFilters](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotFilters.html) with **PivotFilterType.ValueIsGreaterThan** to filter the Category labels, showing only those with values greater than 3000.
**C#**

```csharp
// Configure pivot table's fields.
IPivotTable pvTable = pvCache.CreatePivotTable(Sheet1.Cells["I1"]);
IPivotFields pvFields = pvTable.PivotFields;
pvTable.AddDataField(pvFields["Quantity"], "Sum of quantity", ConsolidationFunction.Sum);
pvFields["Region"].Orientation = PivotFieldOrientation.Row;
pvFields["City"].Orientation = PivotFieldOrientation.Row;
pvFields["Category"].Orientation = PivotFieldOrientation.Column;
pvFields["Product"].Orientation = PivotFieldOrientation.Column;
pvFields["OrderDate"].Orientation = PivotFieldOrientation.Page;

// Apply a value filter to show only categories with values greater than 3000.
pvFields["Category"].PivotFilters.Add(PivotFilterType.ValueIsGreaterThan, 0, 3000);
```

**VB**

```vbnet
' Configure pivot table's fields.
Dim pvTable As IPivotTable = pvCache.CreatePivotTable(Sheet1.Cells("I1"))
Dim pvFields As IPivotFields = pvTable.PivotFields
pvTable.AddDataField(pvFields("Quantity"), "Sum of quantity", ConsolidationFunction.Sum)
pvFields("Region").Orientation = PivotFieldOrientation.Row
pvFields("City").Orientation = PivotFieldOrientation.Row
pvFields("Category").Orientation = PivotFieldOrientation.Column
pvFields("Product").Orientation = PivotFieldOrientation.Column
pvFields("OrderDate").Orientation = PivotFieldOrientation.Page

' Apply a value filter to show only categories with values greater than 3000.
pvFields("Category").PivotFilters.Add(PivotFilterType.ValueIsGreaterThan, 0, 3000)
```

![image](https://cdn.mescius.io/document-site-files/images/c76ee3c1-6329-4804-9e96-99555f506bc5/image.e9dded.png)

## Apply Multiple Filters

The following example demonstrates how to filter using both label and value filters.
**C#**

```csharp
IPivotTable pvTable = pvCache.CreatePivotTable(Sheet1.Cells["I1"]);
IPivotFields pvFields = pvTable.PivotFields;
pvTable.AddDataField(pvFields["Quantity"], "Sum of quantity", ConsolidationFunction.Sum);
pvFields["Region"].Orientation = PivotFieldOrientation.Row;
pvFields["City"].Orientation = PivotFieldOrientation.Row;
pvFields["Category"].Orientation = PivotFieldOrientation.Column;
pvFields["Product"].Orientation = PivotFieldOrientation.Column;
pvFields["OrderDate"].Orientation = PivotFieldOrientation.Page;

// Filter for products where the product name contains 'Ea'.
pvFields["Region"].PivotFilters.Add(PivotFilterType.CaptionContains, 0, "Ea");

//Apply a value filter to show only categories with values greater than 3000.
pvFields["Category"].PivotFilters.Add(PivotFilterType.ValueIsGreaterThan, 0, 3000);
```

**VB**

```vbnet
Dim pvTable As IPivotTable = pvCache.CreatePivotTable(Sheet1.Cells("I1"))
Dim pvFields As IPivotFields = pvTable.PivotFields
pvTable.AddDataField(pvFields("Quantity"), "Sum of quantity", ConsolidationFunction.Sum)
pvFields("Region").Orientation = PivotFieldOrientation.Row
pvFields("City").Orientation = PivotFieldOrientation.Row
pvFields("Category").Orientation = PivotFieldOrientation.Column
pvFields("Product").Orientation = PivotFieldOrientation.Column
pvFields("OrderDate").Orientation = PivotFieldOrientation.Page

' Filter for products where the product name contains "Ea".
pvFields("Region").PivotFilters.Add(PivotFilterType.CaptionContains, 0, "Ea")

' Apply a value filter to show only categories with values greater than 3000.
pvFields("Category").PivotFilters.Add(PivotFilterType.ValueIsGreaterThan, 0, 3000)
```

![image](https://cdn.mescius.io/document-site-files/images/c76ee3c1-6329-4804-9e96-99555f506bc5/image.91ee6b.png?width=550)

## Clear Filters

You can clear all filters from the pivot table using the [IPivotTable.ClearAllFilters()](/spreadnet/api/latest/online-wpf/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.PivotTables.IPivotTable.ClearAllFilters.html) method.
**C#**

```auto
pvTable.ClearAllFilters();
```

**VB**

```auto
pvTable.ClearAllFilters()
```

## Filter Dialog

You can use the Filter dialog by clicking on a column header in a pivot table. It provides various sorting and filtering options. You need to select the required items from the filter dialog's list box for applying the filter. The below image shows the filter dialog box.
![image](https://cdn.mescius.io/document-site-files/images/c76ee3c1-6329-4804-9e96-99555f506bc5/image.b813a8.png)
The filter dialog also provides a search box, allowing you to conveniently apply filtering settings. You can enter search items in a case-insensitive manner.