Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Apply Autofilter Criteria and Hide the Filtered Rows with openpyxl

openpyxl can record autofilter criteria, but it never hides a row — the file opens showing everything under a filter that claims otherwise. Write the criteria and hide the non-matching rows yourself so the workbook opens already filtered, with SUBTOTAL totals that match what readers see.
Tables, filters & outlinesopenpyxl

Filtering a report before sending it — only open orders, only this region, only amounts over a threshold — saves every reader the same clicks. openpyxl has the pieces to write filter criteria into an autofilter, and many people stop there. Then the file opens with the filter dropdowns showing a filter icon, but every row still visible: openpyxl records criteria, while hiding rows is something Excel does only when a user applies a filter. This guide, part of Creating Excel Tables and Autofilters with Python, writes the criteria and hides the matching rows so the workbook opens exactly as a filtered sheet would. Setting up the autofilter range is covered in Add an Autofilter to an Excel Sheet with openpyxl.

Criteria alone versus criteria plus hidden rows With criteria only, the sheet opens showing all six orders under a filter that claims to show North and South orders of at least 100. With the non-matching rows also hidden, only orders 101 and 106 are visible and the SUBTOTAL total reads 2,020. A B C 1 Order Region Amount 2 101 North 1,200 3 102 South 90 4 103 East 640 5 104 North 75 6 105 West 1,500 7 106 South 820 A B C 1 Order Region Amount 2 101 North 1,200 3 106 South 820 4 Visible total 2,020 before after hide non-matching rows

Prerequisites

Bash
pip install openpyxl pandas

An order list with a visible-total row below the data:

Python
from openpyxl import Workbook, load_workbook
from openpyxl.worksheet.filters import CustomFilter, CustomFilters, FilterColumn, Filters

rows = [(101, "North", 1200), (102, "South", 90), (103, "East", 640),
        (104, "North", 75), (105, "West", 1500), (106, "South", 820)]
wb = Workbook()
ws = wb.active
ws.title = "Orders"
ws.append(["Order", "Region", "Amount"])
for row in rows:
    ws.append(row)
last = ws.max_row
ws.append(["Visible total", None, f"=SUBTOTAL(109,C2:C{last})"])
ws.auto_filter.ref = f"A1:C{last}"

The total sits outside the filter range on purpose, so it is never hidden itself. SUBTOTAL with function 109 sums only visible rows, so it follows the filter.

Step 1 — Write the criteria

Each filtered column is a FilterColumn whose colId is the column's zero-based position within the autofilter range — here Region is 1 and Amount is 2. A list of values to show goes in Filters; comparisons go in CustomFilters:

Python
region_filter = FilterColumn(colId=1, filters=Filters(filter=["North", "South"]))
amount_filter = FilterColumn(colId=2, customFilters=CustomFilters(customFilter=[
    CustomFilter(operator="greaterThanOrEqual", val="100")]))
ws.auto_filter.filterColumn.append(region_filter)
ws.auto_filter.filterColumn.append(amount_filter)

Custom filter operators are equal, notEqual, greaterThan, greaterThanOrEqual, lessThan and lessThanOrEqual, and val is always a string in the file, even for numbers. Up to two custom filters can share a column, combined with CustomFilters(_and=True, customFilter=[...]) for a range such as 100 to 1,000; without _and, either condition is enough. At this point the file would open with both filter buttons showing as active — and all six orders visible.

Step 2 — Evaluate the same criteria and hide rows

Hiding is a row property: row_dimensions[row].hidden = True. Evaluate the criteria in Python against each data row and hide the ones that fail. Keep the criteria in one structure so the written filter and the hidden rows cannot drift apart:

Python
import operator

OPS = {"equal": operator.eq, "notEqual": operator.ne, "greaterThan": operator.gt,
       "greaterThanOrEqual": operator.ge, "lessThan": operator.lt, "lessThanOrEqual": operator.le}

def row_matches(values, filter_columns):
    for fc in filter_columns:
        value = values[fc.colId]
        if fc.filters is not None and str(value) not in fc.filters.filter:
            return False
        if fc.customFilters is not None:
            tests = [OPS[cf.operator](value, float(cf.val)) for cf in fc.customFilters.customFilter]
            combined = all(tests) if fc.customFilters._and else any(tests)
            if not combined:
                return False
    return True

for r in range(2, last + 1):
    values = [ws.cell(r, c).value for c in range(1, 4)]
    ws.row_dimensions[r].hidden = not row_matches(values, ws.auto_filter.filterColumn)
wb.save("orders_filtered.xlsx")

check = load_workbook("orders_filtered.xlsx")["Orders"]
print([r for r in range(2, last + 1) if check.row_dimensions[r].hidden])     # [3, 4, 5, 6]

Rows 3 to 6 — South at 90, East, North at 75 and West — are hidden, and orders 101 and 106 remain. Rendering the file in LibreOffice shows exactly those two rows with a visible total of 2,020; the other four are still in the file, just hidden, and Excel will show them again when the reader clears the filter.

Opening a workbook already filtered Set the autofilter range, describe the criteria once as FilterColumn objects, write them to the autofilter, evaluate the same criteria against every data row in Python, hide the rows that fail, and keep SUBTOTAL totals outside the range so they sum only visible rows. Filter range header + data Criteria Filters, CustomFilters Evaluate in Python same criteria Hide rows row_dimensions SUBTOTAL totals visible rows only

Step 3 — Drive the filter from pandas

When the report is built from a DataFrame, compute the mask with pandas — where the logic is easiest to test — and use it for the hidden rows, writing matching criteria for the dropdowns:

Python
import pandas as pd

df = pd.DataFrame(rows, columns=["Order", "Region", "Amount"])
mask = df["Region"].isin(["North", "South"]) & (df["Amount"] >= 100)

with pd.ExcelWriter("orders_pd.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Orders", index=False)
    sheet = writer.sheets["Orders"]
    sheet.auto_filter.ref = f"A1:C{len(df) + 1}"
    sheet.auto_filter.filterColumn.append(FilterColumn(colId=1, filters=Filters(filter=["North", "South"])))
    sheet.auto_filter.filterColumn.append(FilterColumn(colId=2, customFilters=CustomFilters(
        customFilter=[CustomFilter(operator="greaterThanOrEqual", val="100")])))
    for i, keep in enumerate(mask, start=2):
        sheet.row_dimensions[i].hidden = not keep
print(int(mask.sum()), "rows visible")          # 2 rows visible

Row i + 2 of the sheet is DataFrame row i because of the header — keep that offset in one place. If the pandas mask and the written criteria ever disagree, Excel will show one thing until the reader touches the filter and another after, so a test that compares them is worth the few lines. Reading such a sheet back while respecting the hidden rows is covered in Skip Hidden and Filtered Rows When Reading Excel.

Step 4 — Clear a filter

Removing a filter means undoing both halves: drop the criteria and unhide the rows. Leaving rows hidden under a filter with no criteria produces a sheet where rows are missing for no visible reason:

Python
wb = load_workbook("orders_filtered.xlsx")
ws = wb["Orders"]
ws.auto_filter.filterColumn = []
for r in range(2, ws.max_row + 1):
    ws.row_dimensions[r].hidden = False
wb.save("orders_unfiltered.xlsx")
print(sum(load_workbook("orders_unfiltered.xlsx")["Orders"].row_dimensions[r].hidden
          for r in range(2, 8)))                  # 0

The autofilter range stays, so readers still get the dropdowns. To remove the dropdowns too, set ws.auto_filter.ref = None. Clearing is also the right first step when re-filtering an existing workbook: start from all rows visible, then apply the new criteria, so no row stays hidden by an old rule.

The two halves of an applied filter The filter criteria are what Excel's dropdowns show and what Excel uses when the reader changes the filter. The hidden rows are what the reader sees on opening. openpyxl writes the criteria when asked but never hides rows, so both halves must be written together and cleared together. Criteria (auto_filter.filterColumn) dropdowns show the filter Excel reapplies on change hides nothing by itself Hidden rows (row_dimensions) what readers see on opening SUBTOTAL respects them invisible without criteria

Step 5 — Top-N filters

Excel's "Top 10" filter shows the largest (or smallest) N values or N percent. openpyxl writes it with Top10; as before, the hiding is up to you, and pandas' nlargest gives the rows to keep:

Python
from openpyxl.worksheet.filters import Top10

wb = Workbook()
ws = wb.active
ws.append(["Order", "Region", "Amount"])
for row in rows:
    ws.append(row)
ws.auto_filter.ref = f"A1:C{len(rows) + 1}"
ws.auto_filter.filterColumn.append(FilterColumn(colId=2, top10=Top10(top=True, val=3)))

keep = set(df.nlargest(3, "Amount").index)
for i in range(len(df)):
    ws.row_dimensions[i + 2].hidden = i not in keep
wb.save("orders_top3.xlsx")
print(sorted(df.loc[list(keep), "Order"]))       # [101, 105, 106]

The three largest orders — 1,500, 1,200 and 820 — stay visible. top=False gives the bottom N, and percent=True makes val a percentage of the rows. Ties need a decision: Excel shows every row tied with the Nth value, while nlargest keeps exactly N unless you pass keep="all", which matches Excel's behaviour. Date filters such as "this month" (DynamicFilter) follow the same pattern — write the dynamic filter, compute the date range in Python, hide the rest — but remember that a dynamic filter is re-evaluated against the date when the reader reapplies it, while your hidden rows reflect the day the file was written.

Common pitfalls

  • colId as a sheet column. For a filter range starting in column C, the first column is colId=0, not 2.
  • Filters on a sheet that pandas rewrites. Writing the DataFrame again after setting the filter replaces the sheet and loses both the criteria and the hidden rows. Apply filters last.
  • Numbers as values in Filters. Filters(filter=[...]) matches displayed text; for numeric conditions use CustomFilters, whose val is compared numerically.
  • Totals inside the filter range. A total row inside the range can itself be hidden by the filter. Keep it below the range or in a table's totals row, as in Add a Total Row to an Excel Table with Python.

Conclusion

openpyxl's autofilter records criteria but never hides rows. Describe the criteria once as FilterColumn objects with Filters for value lists and CustomFilters for comparisons, write them to ws.auto_filter, evaluate the same criteria in Python — or with a pandas mask — and hide the rows that fail with row_dimensions[row].hidden. Keep SUBTOTAL totals outside the range so they sum what readers see, and clear both halves together when removing a filter.

Frequently asked questions

Why does my openpyxl autofilter not hide any rows? openpyxl only writes the filter settings into the file. Hiding rows is something Excel does when a user applies a filter; the saved file stores hidden rows separately, as row properties. Set row_dimensionsrow.hidden for the rows that do not match.

How do I filter for several values in one column? Add a FilterColumn with Filters(filter=...) listing the values to show. Its colId is the zero-based position of the column within the autofilter range, not the sheet column letter.

How do I filter numbers greater than a value? Use CustomFilters with a CustomFilter such as operator="greaterThanOrEqual" and val="100". Two custom filters in one column can be combined with _and=True so both must hold.

Will the filter still work in Excel after I hide rows myself? Yes. Excel sees an active filter with its criteria and rows already hidden to match. When the reader clears or changes the filter, Excel recalculates which rows to show from the data.

More on tables, filters & outlines

More on tables, filters & outlines →