{"id":2367,"date":"2026-10-14T07:42:19","date_gmt":"2026-10-13T23:42:19","guid":{"rendered":"https:\/\/xlwings.net\/blog\/?p=2367"},"modified":"2026-03-29T09:58:20","modified_gmt":"2026-03-29T09:58:20","slug":"how-to-use-rangeautofilter-in-the-xlwings-api-way","status":"publish","type":"post","link":"https:\/\/xlwings.net\/blog\/how-to-use-rangeautofilter-in-the-xlwings-api-way\/","title":{"rendered":"How to use Range.AutoFilter in the xlwings API way"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The AutoFilter member of the Range object in Excel, accessible via the xlwings API, is a powerful feature for programmatically applying and managing autofilters on worksheet data. It allows for dynamic data filtering based on specified criteria, enabling users to focus on relevant subsets of information within a larger dataset. This functionality is essential for data analysis tasks where sifting through rows to find matching records is required.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In xlwings, the AutoFilter is applied through the <code>api<\/code> property, which provides direct access to the underlying Excel object model. The primary method used is <code>AutoFilter<\/code>. The syntax for applying a basic autofilter to a range is straightforward. For more complex filtering involving specific criteria, the <code>AutoFilter<\/code> method can accept parameters.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The core syntax for applying an autofilter in xlwings is:<br><code>range.api.AutoFilter<\/code><br>This will apply filters to the headers of the specified range. To set specific filter criteria, you can use the extended syntax:<br><code>range.api.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)<\/code><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>Field<\/code>: The integer offset of the field (column) to filter on, where the first column in the range is 1.<\/li>\n\n\n\n<li><code>Criteria1<\/code>: The primary filter criterion as a string (e.g., &#8220;>100&#8221;, &#8220;Product A&#8221;).<\/li>\n\n\n\n<li><code>Operator<\/code>: An optional constant from the <code>XlAutoFilterOperator<\/code> enumeration (e.g., <code>xlAnd<\/code>, <code>xlOr<\/code>) to define the relationship between Criteria1 and Criteria2. Common xlwings constants are <code>win32c.XlAutoFilterOperator.xlAnd<\/code> or <code>win32c.XlAutoFilterOperator.xlOr<\/code>.<\/li>\n\n\n\n<li><code>Criteria2<\/code>: The secondary filter criterion, used when <code>Operator<\/code> is <code>xlAnd<\/code> or <code>xlOr<\/code>.<\/li>\n\n\n\n<li><code>VisibleDropDown<\/code>: An optional boolean to show or hide the filter dropdown arrow (default is <code>True<\/code>).<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">For example, to filter the first column for values equal to &#8220;Yes&#8221;:<br><code>range.api.AutoFilter(Field:=1, Criteria1:=\"Yes\")<\/code><br>To filter a numeric column for values greater than 50:<br><code>range.api.AutoFilter(Field:=2, Criteria1:=\"&gt;50\")<\/code><br>For a filter with two conditions (e.g., values between 10 and 100), you would use:<br><code>range.api.AutoFilter(Field:=3, Criteria1:=\"&gt;=10\", Operator:=win32c.XlAutoFilterOperator.xlAnd, Criteria2:=\"&lt;=100\")<\/code><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Below are practical xlwings code examples. First, ensure you have a reference to the desired worksheet and range.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import xlwings as xw\n# Connect to the active workbook and sheet\nwb = xw.books.active\nws = wb.sheets&#91;'Sheet1']\n# Define the range to filter, including headers\ndata_range = ws.range('A1').current_region\n\n# Example 1: Apply a basic autofilter to the entire data range\ndata_range.api.AutoFilter()\nprint(\"Basic autofilter applied.\")\n\n# Example 2: Filter the first column for the value \"Completed\"\ndata_range.api.AutoFilter(Field=1, Criteria1=\"Completed\")\nprint(\"Filtered column 1 for 'Completed'.\")\n\n# Example 3: Filter the second column for values greater than 100\ndata_range.api.AutoFilter(Field=2, Criteria1=\"&gt;100\")\nprint(\"Filtered column 2 for values &gt; 100.\")\n\n# Example 4: Clear all filters from the worksheet\nws.api.AutoFilterMode = False\nprint(\"All filters cleared.\")\n\n# Example 5: Complex filter on the third column for values between 50 and 150\nimport win32com.client.constants as win32c\ndata_range.api.AutoFilter(Field=3, Criteria1=\"&gt;=50\", Operator=win32c.XlAutoFilterOperator.xlAnd, Criteria2=\"&lt;=150\")\nprint(\"Filtered column 3 for values between 50 and 150.\")<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The AutoFilter member of the Range object in Excel, accessible via the xlwings API, is a powerful fe&#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-2367","post","type-post","status-publish","format-standard","hentry","category-xlwings-api-reference"],"_links":{"self":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2367","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=2367"}],"version-history":[{"count":2,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2367\/revisions"}],"predecessor-version":[{"id":3607,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2367\/revisions\/3607"}],"wp:attachment":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/media?parent=2367"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/categories?post=2367"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/tags?post=2367"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}