# Pivot Table Filter

DsExcel is a Document Solutions product that offers a comprehensive library to create, manipulate, convert, and share Microsoft Excel-compatible spreadsheets.

## Content

DsExcel enables you to filter values in pivot tables. You can either hide row fields or pages using [Visible](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotItem.Visible.html) property of [IPivotItem](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotItem.html) interface or use [PivotFilters](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotField.PivotFilters.html) property of [IPivotField](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotField.html) interface, which enables you to add label, value, or date filters to the pivot table field using [PivotFilterType](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFilterType.html) enumeration that will be passed as a parameter in the [Add](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotFilters.Add.html) method of [IPivotFilters](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotFilters.html) interface.
Furthermore, DsExcel provides [AllowMultipleFilters](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotTable.AllowMultipleFilters.html) property of [IPivotTable](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotTable.html) interface, enabling you to add label and value filters simultaneously to a **PivotField**. The default value of this property is **true**. If you set this property to **false**, the filter set later will be used.

> !type=note
> **Note:** When the value of **AllowMultipleFilters** changes from **true** to **false**, it will go through the **IPivotField**. If IPivotField has both the label and value filters, DsExcel will discard both filters.

DsExcel also provides [ClearLabelFilter](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotField.ClearLabelFilter.html), [ClearValueFilter](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotField.ClearValueFilter.html), and [ClearAllFilters](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotField.ClearAllFilters.html) methods to delete the filters. ClearLabelFilter deletes the label filter, whereas ClearValueFilter deletes the value filter. ClearAllFilters deletes all the filters added to the field.

> !type=note
> **Note:** If you add two label filters or two value filters simultaneously using Add method, then the second filter will replace the first.

> !type=note
> **Note:** Most of the properties of the [IPivotFilter](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotFilter.html) interface are read-only, except for the [WholeDayFilter](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotFilter.WholeDayFilter.html) property, which is read-write. However, any changes to the value of WholeDayFilter property will not trigger a refresh of the current pivot table. Hence, you have to call [Update](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotTable.Update.html) or [Refresh](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotTable.Refresh.html) methods of [IPivotTable](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IPivotTable.html) interface to update or refresh the filter results.

You can apply the following types of filters to the pivot table:

* [Apply Filter](/document-solutions/dot-net-excel-api/docs/online/Features/UsePivotTable/PivotTableFilter#apply-filter)
* [Apply Label Filter](/document-solutions/dot-net-excel-api/docs/online/Features/UsePivotTable/PivotTableFilter#apply-label-filter)
* [Apply Value Filter](/document-solutions/dot-net-excel-api/docs/online/Features/UsePivotTable/PivotTableFilter#apply-value-filter)
* [Apply Date Filter](/document-solutions/dot-net-excel-api/docs/online/Features/UsePivotTable/PivotTableFilter#apply-date-filter)
* [Apply Top 10 Filter](/document-solutions/dot-net-excel-api/docs/online/Features/UsePivotTable/PivotTableFilter#apply-top-10-filter)
* [Apply Multiple Filters](/document-solutions/dot-net-excel-api/docs/online/Features/UsePivotTable/PivotTableFilter#apply-multiple-filters)

## Apply Filter

Refer to the following example code to hide row fields or pages using Visible property:

```csharp
// Create a new workbook.
Workbook workbook = new Workbook();

// Add data for the pivot table.
object[,] sourceData = new object[,] {
{ "Order ID", "Product",               "Category",              "Amount", "Date",                    "Country" },
{ 1,          "Bose 785593-0050",      "Consumer Electronics",  4270,     new DateTime(2018, 1, 6),  "United States" },
{ 2,          "Canon EOS 1500D",       "Consumer Electronics",  8239,     new DateTime(2018, 1, 7),  "United Kingdom" },
{ 3,          "Haier 394L 4Star",      "Consumer Electronics",  617,      new DateTime(2018, 1, 8),  "United States" },
{ 4,          "IFB 6.5 Kg FullyAuto",  "Consumer Electronics",  8384,     new DateTime(2018, 1, 10), "Canada" },
{ 5,          "Mi LED 40inch",         "Consumer Electronics",  2626,     new DateTime(2018, 1, 10), "Germany" },
{ 6,          "Sennheiser HD 4.40-BT", "Consumer Electronics",  3610,     new DateTime(2018, 1, 11), "United States" },
{ 7,          "Iphone XR",             "Mobile",                9062,     new DateTime(2018, 1, 11), "Australia" },
{ 8,          "OnePlus 7Pro",          "Mobile",                6906,     new DateTime(2018, 1, 16), "New Zealand" },
{ 9,          "Redmi 7",               "Mobile",                2417,     new DateTime(2018, 1, 16), "France" },
{ 10,         "Samsung S9",            "Mobile",                7431,     new DateTime(2018, 1, 16), "Canada" },
{ 11,         "OnePlus 7Pro",          "Mobile",                8250,     new DateTime(2018, 1, 16), "Germany" },
{ 12,         "Redmi 7",               "Mobile",                7012,     new DateTime(2018, 1, 18), "United States" },
{ 13,         "Bose 785593-0050",      "Consumer Electronics",  1903,     new DateTime(2018, 1, 20), "Germany" },
{ 14,         "Canon EOS 1500D",       "Consumer Electronics",  2824,     new DateTime(2018, 1, 22), "Canada" },
{ 15,         "Haier 394L 4Star",      "Consumer Electronics",  6946,     new DateTime(2018, 1, 24), "France" },
};

// Access first worksheet.
IWorksheet worksheet = workbook.Worksheets[0];

// Add values to the range.
worksheet.Range["G1:L16"].Value = sourceData;

// Set column width.
worksheet.Range["G:L"].ColumnWidth = 15;

// Create pivot cache.
var pivotcache = workbook.PivotCaches.Create(worksheet.Range["G1:L16"]);

// Add data to the pivot table.
var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1");

// Set number format.
worksheet.Range["J1:J16"].NumberFormat = "$#,##0.00";

// Configure pivot table's fields.
var field_product = pivottable.PivotFields[1];
field_product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields[3];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

var field_Country = pivottable.PivotFields[5];
field_Country.Orientation = PivotFieldOrientation.PageField;

// Add row field filter.
field_product.PivotItems["Bose 785593-0050"].Visible = false;
field_product.PivotItems["Haier 394L 4Star"].Visible = false;
field_product.PivotItems["Iphone XR"].Visible = false;

// Add page filter.
field_Country.PivotItems["United States"].Visible = false;
field_Country.PivotItems["Canada"].Visible = false;

worksheet.Range["A:B"].EntireColumn.AutoFit();

// Save WorkBook.
workbook.Save("FilterPivotTable.xlsx");
```

## Apply Label Filter

Refer to the example code to filter products based on the item names containing "mi":

```csharp
// Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for products where the product name contains 'mi'.
field_Product.PivotFilters.Add(PivotFilterType.CaptionContains, "mi");

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("PivotLabelFilter.xlsx");
```

## Apply Value Filter

Refer to the example code to filter products based on value:

```csharp
// Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for products with sales volume greater than 7000.
field_Product.PivotFilters.Add(PivotFilterType.ValueGreaterThan, 7000, options: new PivotFilterOptions { AppliedDataField = 0 });

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("PivotValueFilter.xlsx");
```

> !type=note
> **Note:** You must set [AppliedDataField](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFilterOptions.AppliedDataField.html) property of [PivotFilterOptions](/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.PivotFilterOptions.html) class with the value filter to specify which **DataField** to filter by.

## Apply Date Filter

Refer to the following example code to filter products dated between two specified dates:

```csharp
// Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Date = pivottable.PivotFields["Date"];
field_Date.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for products between two dates.
field_Date.PivotFilters.Add(PivotFilterType.DateBetween, new DateTime(2018, 1, 1), new DateTime(2018, 1, 15));

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("PivotDateFilter.xlsx");
```

## Apply Top 10 Filter

Refer to the example code to filter the top twelve products based on value:

```csharp
// Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Filter for top 12 products.
field_Product.PivotFilters.Add(PivotFilterType.Count, 12, options: new PivotFilterOptions { IsTop10Type = true });

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("PivotTopTenFilter.xlsx");
```

## Apply Multiple Filters

Refer to the following example code to filter products using both lable and value filters:

```csharp
// Configure pivot table's fields.
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.ColumnField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";

// Allow adding label filter and value filter to a field at the same time.
pivottable.AllowMultipleFilters = true;

// Filter for products with sales volume greater than 7000.
field_Product.PivotFilters.Add(PivotFilterType.ValueGreaterThan, 7000, options: new PivotFilterOptions { AppliedDataField = 0 });

// Filter for products where the product name contains 'mi'.
field_Product.PivotFilters.Add(PivotFilterType.CaptionContains, "mi");

worksheet.Range["A:D"].EntireColumn.AutoFit();

// Save the workbook.
workbook.Save("MultipleFilter.xlsx");
```