{"id":2303,"date":"2026-09-12T07:07:32","date_gmt":"2026-09-11T23:07:32","guid":{"rendered":"https:\/\/xlwings.net\/blog\/?p=2303"},"modified":"2026-03-28T12:42:52","modified_gmt":"2026-03-28T12:42:52","slug":"how-to-use-worksheetautofilter-in-the-xlwings-api-way","status":"publish","type":"post","link":"https:\/\/xlwings.net\/blog\/how-to-use-worksheetautofilter-in-the-xlwings-api-way\/","title":{"rendered":"How to use Worksheet.AutoFilter in the xlwings API way"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The AutoFilter member of the Worksheet object in xlwings provides a powerful way to programmatically manage Excel&#8217;s AutoFilter feature, which is essential for sorting, filtering, and analyzing data in ranges. By using the xlwings API, you can automate the process of applying, modifying, and clearing filters, enabling efficient data manipulation in Python scripts. This functionality is exposed through the <code>api.AutoFilter<\/code> property of a Worksheet object, which corresponds directly to the Excel VBA AutoFilter object model, allowing for detailed control over filter criteria and ranges.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The primary method to access the AutoFilter is via the <code>api<\/code> property of a worksheet. In xlwings, the <code>api<\/code> property grants direct access to the underlying Excel object model, making it possible to use Excel&#8217;s native methods and properties. The syntax for working with AutoFilter typically involves setting the filter range and applying criteria. For example, to apply an AutoFilter to a specific range, you can use <code>ws.api.AutoFilter<\/code>. The key parameters include the range to filter, field indices for columns, criteria for filtering, and optional operators. Here is a breakdown of common parameters in methods like <code>AutoFilter<\/code>:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Range<\/strong>: Specifies the range to apply the filter, usually a string like &#8220;A1:D10&#8221; or an xlwings Range object.<\/li>\n\n\n\n<li><strong>Field<\/strong>: An integer representing the column number in the filter range (1-based index).<\/li>\n\n\n\n<li><strong>Criteria1<\/strong>: The primary filter criterion, such as a string for text filters or a number for value filters.<\/li>\n\n\n\n<li><strong>Operator<\/strong>: An optional parameter that defines the filter type, using Excel constants like <code>xlAnd<\/code>, <code>xlOr<\/code>, <code>xlTop10Items<\/code>, etc. In xlwings, these are accessed via <code>app.constants<\/code> (e.g., <code>app.constants.xlAnd<\/code>).<\/li>\n\n\n\n<li><strong>Criteria2<\/strong>: A secondary criterion used with operators like <code>xlAnd<\/code> or <code>xlOr<\/code>.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">For instance, to filter a range to show rows where the first column equals &#8220;Product A&#8221;, you would set <code>Field=1<\/code>, <code>Criteria1=\"Product A\"<\/code>, and optionally use <code>Operator=app.constants.xlAnd<\/code> if combining criteria. It&#8217;s important to note that the AutoFilter must be applied to a range that includes headers; otherwise, Excel may not behave as expected. The xlwings API also allows checking if a filter is active via <code>ws.api.AutoFilterMode<\/code> and clearing it with <code>ws.api.AutoFilter.ShowAllData()<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here are some practical xlwings API code examples for using the Worksheet AutoFilter member:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Applying an AutoFilter to a Range<\/strong>: This example applies an AutoFilter to the range A1:D20 on the active worksheet, enabling the filter dropdowns in the header row.<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>import xlwings as xw\napp = xw.App(visible=False)\nwb = app.books.open('example.xlsx')\nws = wb.sheets&#91;'Sheet1']\nws.api.AutoFilter(ws.range('A1:D20').api)<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li><strong>Filtering Based on Text Criteria<\/strong>: This filters the first column to display only rows where the value is &#8220;Completed&#8221;.<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>ws.api.AutoFilter(ws.range('A1:D100').api, Field=1, Criteria1=\"Completed\")<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li><strong>Using Multiple Criteria with an Operator<\/strong>: This filters the second column for values greater than 50 and less than 100, using the <code>xlAnd<\/code> operator.<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>ws.api.AutoFilter(ws.range('A1:D100').api, Field=2, Criteria1=\"50\", Operator=app.constants.xlAnd, Criteria2=\"100\")<\/code><\/pre>\n\n\n\n<ol start=\"4\" class=\"wp-block-list\">\n<li><strong>Clearing All Filters<\/strong>: To remove filters and show all data in the worksheet, use the <code>ShowAllData<\/code> method.<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>if ws.api.AutoFilterMode:\n    ws.api.AutoFilter.ShowAllData()<\/code><\/pre>\n\n\n\n<ol start=\"5\" class=\"wp-block-list\">\n<li><strong>Checking Filter Status<\/strong>: This checks if an AutoFilter is currently applied to the worksheet.<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>filter_active = ws.api.AutoFilterMode\nprint(f\"Filter active: {filter_active}\")<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The AutoFilter member of the Worksheet object in xlwings provides a powerful way to programmatically&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25],"tags":[],"class_list":["post-2303","post","type-post","status-publish","format-standard","hentry","category-xlwings-api-reference"],"_links":{"self":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2303","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/comments?post=2303"}],"version-history":[{"count":1,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2303\/revisions"}],"predecessor-version":[{"id":3511,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2303\/revisions\/3511"}],"wp:attachment":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/media?parent=2303"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/categories?post=2303"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/tags?post=2303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}