Filter Data in Spreadsheet Documents

• Aug 14, 2024
• 3 minutes to read

The SpreadsheetControl allows users to handle large amounts of data. To quickly access required records, a user can apply a filtering criteria and hide all records that don’t match the condition.

Filter Data in the User Interface

Users can specify a filter, reapply filters, or remove all filters in a worksheet through filtering commands located on the Data tab in the Sort & Filter group.

When filtering is active, a drop-down arrow appears on the right side of each column header in the range. Users can click the arrow of the required column and select a predefined filter type from the AutoFilter drop-down menu. The menu content depends on the type of data in the filtered column (text, numeric values, color, or dates).

If cells in the target column use different styles, the Filter by Color option is available. Choose Cell colors… to apply a background color or pattern filter, and Font colors… to apply a font filter.

When you select the required option, a dialog with available colors appears. Select the target color and click OK.

Filter Data in Code

Obtain Filter Settings for a Cell Range and a Table

Use the Worksheet.AutoFilter property to get the SheetAutoFilter object. This object implements filtering functionality for a worksheet.

For a Table, filtering functionality activates by default. Use the Table.AutoFilter property to get the TableAutoFilter object, which specifies filtering options for a table.

Both SheetAutoFilter and TableAutoFilter implement the AutoFilterBase interface so that you can filter table data similarly to worksheet ranges.

Refer to the following article for an example on how to activate filtering functionality for a worksheet range or table: How to: Enable Filtering.

Filter a Column

To filter values in a specific column, obtain a collection of columns in the filtered range through the SheetAutoFilter.Columns or TableAutoFilter.Columns property. Get the column by its index in the AutoFilterColumnCollection. Each AutoFilterColumn object in AutoFilterColumnCollection contains methods to filter data in a column.

Call one of these methods based on the desired filter type:

Method	Description	Example
AutoFilterColumn.ApplyCustomFilter	Applies a custom filter based on one or two criteria using comparison operators.	How to: Apply a Custom Text Filter How to: Apply a Custom Number Filter How to: Apply a Custom Date Filter
AutoFilterColumn.ApplyFilterCriteria	Filters column data by a list of cell values and/or date/time values.	How to: Filter by Cell Values How to: Filter by Date Values
AutoFilterColumn.ApplyDynamicFilter	Applies a dynamic filter to show values above or below the average or within a specified time period.	How to: Apply a Dynamic Filter
AutoFilterColumn.ApplyTop10Filter	Applies a Top 10 filter to display top or bottom-ranked values.	How to: Filter Top or Bottom Ranked Values
AutoFilterColumn.ApplyFillColorFilter	Applies a background color filter.	How to: Filter Cells by Color
AutoFilterColumn.ApplyFontColorFilter	Applies a font color filter.	How to: Filter Cells by Color
AutoFilterColumn.ApplyFillFilter	Applies a filter by cell fill pattern.	How to: Filter Cells by Color

Tip

Filter data using multiple columns. Filters are additive, meaning each new filter applies in addition to existing filters, further refining your data.

Apply, Re-apply, or Clear Filter

Turn on filtering for a specified cell range in a worksheet by the SheetAutoFilter.Apply method call.

Call the AutoFilterBase.ReApply method to reapply a filter if data in the filtered range changes. Refer to the following example for details: How to: Reapply a Filter

To remove a filter from a specific column, use the AutoFilterColumn.Clear method. To clear all the filters specified in a worksheet, call the AutoFilterBase.Clear method. For details, refer to the How to: Clear a Filter example.