General Information
WinForms Controls
	.NET Core Support
	Prerequisites
	What's Installed
	Build an Application
	Controls and Libraries
		Forms and User Controls
		Messages, Notifications, and Dialogs
		Editors and Simple Controls
		Ribbon, Bars and Menu
		Application UI Manager
		Docking Library
		Data Grid
		Vertical Grid
		Property Grid
		Pivot Grid
		Tree List
		Gantt Control
		Chart Control
		Diagrams
		Gauges
		Map Control
		Scheduler
		Spreadsheet
			Product Information
			Product Structure
			Getting Started
			Spreadsheet Document
			Supported Formats
			Cell Basics
			Formulas
			Defined Names
			Data Binding
			Data Validation
			Data Presentation
				Sorting
				Filtering
				Conditional Formatting
			Data Grouping
			Pivot Tables
			Charts and Graphics
			Printing
			Events
			Mail Merge
			Protection
			Operation Restrictions
			Find and Replace
			Visual Elements
			Services
			Keyboard Shortcuts
			Examples
		Rich Text Editor
		Spell Checker
		Form Layout Managers
		Navigation Controls
		Printing-Exporting
		PDF Viewer
		Reporting
		Snap
		TreeMap Control
		Sunburst Control
	Common Features
	Get More Help
	API Reference
ASP.NET Controls and MVC Extensions
ASP.NET Bootstrap Controls
ASP.NET Core Bootstrap Controls
WPF Controls
Xamarin Controls
Windows 10 App Controls
Office File API
Reporting
Report and Dashboard Server
Dashboard
eXpressApp Framework
eXpress Persistent Objects
CodeRush
CodeRush Classic
Cross-Platform Core Libraries
Tools and Utilities
End-User Documentation

WinForms Controls > Controls and Libraries > Spreadsheet > Data Presentation > Filtering

View this topic on docs.devexpress.com (Learn more)

Filtering

The SpreadsheetControl allows you to use the filtering functionality to arrange large amounts of data by displaying only rows that meet the filtering criteria.

Filter a range of cells

Use the Worksheet.AutoFilter property to get access to the SheetAutoFilter object, implementing the filtering functionality for a worksheet. The SheetAutoFilter object inherits from the AutoFilterBase base interface that contains basic methods and properties used to specify different types of filters in the API. To turn on filtering for the specified cell range in a worksheet, call the SheetAutoFilter.Apply method.
Filter a table

For a Table, the filtering functionality is activated by default. Use the Table.AutoFilter property to access the TableAutoFilter object, which specifies filtering options for a table. The TableAutoFilter object inherits from the AutoFilterBase base interface that provides basic methods and properties used to filter table data in the same manner as it can be done for a worksheet range.

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

To filter values in a particular column, access a collection of columns in the filtered range by using the AutoFilterBase.Columns property of the SheetAutoFilter object (or TableAutoFilter for a table). Each column in the AutoFilterColumnCollection collection is defined by the AutoFilterColumn object, which contains basic methods used to filter data in a column. Get access to the column you wish to filter by its index in the AutoFilterColumnCollection collection, then call one of the following methods, depending on the filter type you wish to apply.

Method	Description	Example
AutoFilterColumn.ApplyCustomFilter	Applies a custom filter based on one or two filter criteria using the comparison operator(s) to construct the filter expression.	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 and time values.	How to: Filter by Cell Values How to: Filter by Date Values
AutoFilterColumn.ApplyDynamicFilter	Applies a dynamic filter to display values that are above or below the average or to show dates that fall within a specified time period.	How to: Apply a Dynamic Filter
AutoFilterColumn.ApplyTop10Filter	Applies the Top 10 filter that allows you to display top/bottom ranked values.	How to: Filter Top or Bottom Ranked Values

Note

You can filter your data by multiple columns. Filters are additive: each new filter is applied in addition to the existing filter(s) and further reduces your data.

If data in the filtered range has been changed, you can reapply a filter by using the AutoFilterBase.ReApply method. For an example, refer to 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.

Manage Filters in the User Interface

End-users can use filtering commands located on the Data tab in the Sort & Filter group to activate filtering, reapply the filter or completely remove all filters in a worksheet.

Once filtering is activated, a drop-down arrow appears on the right side of each column header in the range. An end-user can click the arrow of the required column and select one of predefined filter types in the AutoFilter drop-down menu. The content of the AutoFilter menu depends on the type of data in the filtered column (text, numeric values or dates).

Filtering

Manage Filters in the User Interface

See Also