Excel.FilterCriteria interface

Package:

excel

Represents the filtering criteria applied to a column.

Remarks

[ API set: ExcelApi 1.2 ]

Properties

color	The HTML color string used to filter cells. Used with cellColor and fontColor filtering.
criterion1	The first criterion used to filter data. Used as an operator in the case of custom filtering. For example ">50" for numbers greater than 50, or "=*s" for values ending in "s". Used as a number in the case of top/bottom items/percents (e.g., "5" for the top 5 items if filterOn is set to topItems).
criterion2	The second criterion used to filter data. Only used as an operator in the case of custom filtering.
dynamicCriteria	The dynamic criteria from the Excel.DynamicFilterCriteria set to apply on this column. Used with dynamic filtering.
filterOn	The property used by the filter to determine whether the values should stay visible.
icon	The icon used to filter cells. Used with icon filtering.
operator	The operator used to combine criterion 1 and 2 when using custom filtering.
subField	The property used by the filter to do a rich filter on rich values.
values	The set of values to be used as part of values filtering.

Property Details

color

The HTML color string used to filter cells. Used with cellColor and fontColor filtering.

color?: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.2 ]

criterion1

The first criterion used to filter data. Used as an operator in the case of custom filtering. For example ">50" for numbers greater than 50, or "=*s" for values ending in "s".

Used as a number in the case of top/bottom items/percents (e.g., "5" for the top 5 items if filterOn is set to topItems).

criterion1?: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.2 ]

criterion2

The second criterion used to filter data. Only used as an operator in the case of custom filtering.

criterion2?: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.2 ]

dynamicCriteria

The dynamic criteria from the Excel.DynamicFilterCriteria set to apply on this column. Used with dynamic filtering.

dynamicCriteria?: Excel.DynamicFilterCriteria | "Unknown" | "AboveAverage" | "AllDatesInPeriodApril" | "AllDatesInPeriodAugust" | "AllDatesInPeriodDecember" | "AllDatesInPeriodFebruray" | "AllDatesInPeriodJanuary" | "AllDatesInPeriodJuly" | "AllDatesInPeriodJune" | "AllDatesInPeriodMarch" | "AllDatesInPeriodMay" | "AllDatesInPeriodNovember" | "AllDatesInPeriodOctober" | "AllDatesInPeriodQuarter1" | "AllDatesInPeriodQuarter2" | "AllDatesInPeriodQuarter3" | "AllDatesInPeriodQuarter4" | "AllDatesInPeriodSeptember" | "BelowAverage" | "LastMonth" | "LastQuarter" | "LastWeek" | "LastYear" | "NextMonth" | "NextQuarter" | "NextWeek" | "NextYear" | "ThisMonth" | "ThisQuarter" | "ThisWeek" | "ThisYear" | "Today" | "Tomorrow" | "YearToDate" | "Yesterday";

Property Value

Excel.DynamicFilterCriteria | "Unknown" | "AboveAverage" | "AllDatesInPeriodApril" | "AllDatesInPeriodAugust" | "AllDatesInPeriodDecember" | "AllDatesInPeriodFebruray" | "AllDatesInPeriodJanuary" | "AllDatesInPeriodJuly" | "AllDatesInPeriodJune" | "AllDatesInPeriodMarch" | "AllDatesInPeriodMay" | "AllDatesInPeriodNovember" | "AllDatesInPeriodOctober" | "AllDatesInPeriodQuarter1" | "AllDatesInPeriodQuarter2" | "AllDatesInPeriodQuarter3" | "AllDatesInPeriodQuarter4" | "AllDatesInPeriodSeptember" | "BelowAverage" | "LastMonth" | "LastQuarter" | "LastWeek" | "LastYear" | "NextMonth" | "NextQuarter" | "NextWeek" | "NextYear" | "ThisMonth" | "ThisQuarter" | "ThisWeek" | "ThisYear" | "Today" | "Tomorrow" | "YearToDate" | "Yesterday"

Remarks

[ API set: ExcelApi 1.2 ]

filterOn

The property used by the filter to determine whether the values should stay visible.

filterOn: Excel.FilterOn | "BottomItems" | "BottomPercent" | "CellColor" | "Dynamic" | "FontColor" | "Values" | "TopItems" | "TopPercent" | "Icon" | "Custom";

Property Value

Excel.FilterOn | "BottomItems" | "BottomPercent" | "CellColor" | "Dynamic" | "FontColor" | "Values" | "TopItems" | "TopPercent" | "Icon" | "Custom"

Remarks

[ API set: ExcelApi 1.2 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/worksheet-auto-filter.yaml

// This function adds a percentage AutoFilter to the active worksheet 
// and applies the filter to a column of the used range.
await Excel.run(async (context) => {
    // Retrieve the active worksheet and the used range on that worksheet.
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const farmData = sheet.getUsedRange();

    // Add a filter that will only show the rows with the top 50% of values in column 3.
    sheet.autoFilter.apply(farmData, 3, {
        criterion1: "50",
        filterOn: Excel.FilterOn.topPercent
    });

    await context.sync();
});

icon

The icon used to filter cells. Used with icon filtering.

icon?: Excel.Icon;

Property Value

Excel.Icon

Remarks

[ API set: ExcelApi 1.2 ]

operator

The operator used to combine criterion 1 and 2 when using custom filtering.

operator?: Excel.FilterOperator | "And" | "Or";

Property Value

Excel.FilterOperator | "And" | "Or"

Remarks

[ API set: ExcelApi 1.2 ]

subField

The property used by the filter to do a rich filter on rich values.

subField?: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.9 ]

values

The set of values to be used as part of values filtering.

values?: Array<string | FilterDatetime>;

Property Value

Array<string | Excel.FilterDatetime>

Remarks

[ API set: ExcelApi 1.2 ]