Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inExcel.AutoFilter class
Represents the AutoFilter object. AutoFilter turns the values in Excel column into specific filters based on the cell contents.
- Extends
Remarks
Properties
| context | The request context associated with the object. This connects the add-in's process to the Office host application's process. |
| criteria | An array that holds all the filter criteria in the autofiltered range. |
| enabled | Specifies if the AutoFilter is enabled. |
| is |
Specifies if the AutoFilter has filter criteria. |
Methods
| apply(range, column |
Applies the AutoFilter to a range. This filters the column if column index and filter criteria are specified. |
| clear |
Clears the column filter criteria of the AutoFilter. |
| clear |
Clears the filter criteria and sort state of the AutoFilter. |
| get |
Returns the |
| get |
Returns the |
| load(options) | Queues up a command to load the specified properties of the object. You must call |
| load(property |
Queues up a command to load the specified properties of the object. You must call |
| load(property |
Queues up a command to load the specified properties of the object. You must call |
| reapply() | Applies the specified AutoFilter object currently on the range. |
| remove() | Removes the AutoFilter for the range. |
| toJSON() | Overrides the JavaScript |
context
The request context associated with the object. This connects the add-in's process to the Office host application's process.
context: RequestContext;Property Value
criteria
An array that holds all the filter criteria in the autofiltered range.
readonly criteria: Excel.FilterCriteria[];Property Value
Remarks
enabled
Specifies if the AutoFilter is enabled.
readonly enabled: boolean;Property Value
boolean
Remarks
isDataFiltered
Specifies if the AutoFilter has filter criteria.
readonly isDataFiltered: boolean;Property Value
boolean
Remarks
apply(range, columnIndex, criteria)
Applies the AutoFilter to a range. This filters the column if column index and filter criteria are specified.
apply(range: Range | string, columnIndex?: number, criteria?: Excel.FilterCriteria): void;Parameters
- range
-
Excel.Range | string
The range on which the AutoFilter will apply.
- columnIndex
-
number
The zero-based column index to which the AutoFilter is applied.
- criteria
- Excel.FilterCriteria
The filter criteria.
Returns
void
Remarks
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();
});
clearColumnCriteria(columnIndex)
Clears the column filter criteria of the AutoFilter.
clearColumnCriteria(columnIndex: number): void;Parameters
- columnIndex
-
number
The zero-based column index, which represents which column filter needs to be cleared. If the index value is not supported (for example, if the value is a negative number, or if the value is greater than the number of available columns in the range), then an InvalidArgument error will be thrown.
Returns
void
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/worksheet-auto-filter.yaml
// This function clears the AutoFilter setting from one column.
await Excel.run(async (context) => {
// Retrieve the active worksheet.
const sheet = context.workbook.worksheets.getActiveWorksheet();
// Clear the filter from only column 3.
sheet.autoFilter.clearColumnCriteria(3);
await context.sync();
});
clearCriteria()
Clears the filter criteria and sort state of the AutoFilter.
clearCriteria(): void;Returns
void
Remarks
getRange()
Returns the Range object that represents the range to which the AutoFilter applies.
getRange(): Excel.Range;Returns
Remarks
getRangeOrNullObject()
Returns the Range object that represents the range to which the AutoFilter applies. If there is no Range object associated with the AutoFilter, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.
getRangeOrNullObject(): Excel.Range;Returns
Remarks
load(options)
Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.
load(options?: Excel.Interfaces.AutoFilterLoadOptions): Excel.AutoFilter;Parameters
Provides options for which properties of the object to load.
Returns
load(propertyNames)
Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.
load(propertyNames?: string | string[]): Excel.AutoFilter;Parameters
- propertyNames
-
string | string[]
A comma-delimited string or an array of strings that specify the properties to load.
Returns
load(propertyNamesAndPaths)
Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.
load(propertyNamesAndPaths?: {
select?: string;
expand?: string;
}): Excel.AutoFilter;Parameters
- propertyNamesAndPaths
-
{ select?: string; expand?: string; }
propertyNamesAndPaths.select is a comma-delimited string that specifies the properties to load, and propertyNamesAndPaths.expand is a comma-delimited string that specifies the navigation properties to load.
Returns
reapply()
Applies the specified AutoFilter object currently on the range.
reapply(): void;Returns
void
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/worksheet-auto-filter.yaml
// This function refreshes the AutoFilter to ensure that changes are captured.
await Excel.run(async (context) => {
// Retrieve the active worksheet.
const sheet = context.workbook.worksheets.getActiveWorksheet();
// Reapply the filter to capture changes.
sheet.autoFilter.reapply();
await context.sync();
});
remove()
Removes the AutoFilter for the range.
remove(): void;Returns
void
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/worksheet-auto-filter.yaml
// This function removes all AutoFilters from the active worksheet.
await Excel.run(async (context) => {
// Retrieve the active worksheet.
const sheet = context.workbook.worksheets.getActiveWorksheet();
// Remove all filters.
sheet.autoFilter.remove();
await context.sync();
});
toJSON()
Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that's passed to it.) Whereas the original Excel.AutoFilter object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.AutoFilterData) that contains shallow copies of any loaded child properties from the original object.
toJSON(): Excel.Interfaces.AutoFilterData;