Python Excel AutomationPractical guides for developers
Start learning
Guide
Formatting And Charting Excel Reports With PythonDeep dive

Add an Autofilter to an Excel Sheet with openpyxl

Set ws.auto_filter.ref so every data row is covered, freeze the header, apply it after pandas writes the sheet, and understand why recorded filter criteria hide nothing.

An autofilter is one assignment, and almost every problem with it comes from the range. Get ref right and readers can slice a 5,000-row export without touching the data; get it wrong and rows quietly escape the filter. This guide, part of Creating Excel Tables and Autofilters with Python, covers the range, the ordering, and what filter criteria really do.

A filter range that covers the data, and one that stops short A range from A1 to the last row covers the header and every data row, so filtering hides everything it should. A range ending two rows early leaves those rows outside the filter, and they stay visible whatever the reader selects. ref = A1:C6 — correct Region every row responds to the filter ref = A1:C4 — stops short Region outside the range outside the range two rows stay visible whatever is selected

Prerequisites

Bash
pip install pandas openpyxl

The one line, done properly

Python
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

df = pd.DataFrame({
    "Region": ["North", "South", "West", "North", "Central"],
    "SKU": ["A-100", "B-200", "C-300", "A-100", "D-400"],
    "Revenue": [79.96, 99.00, 85.75, 99.95, 26.25],
})
df.to_excel("orders.xlsx", index=False, sheet_name="Orders")

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]

ws.auto_filter.ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
ws.freeze_panes = "A2"

wb.save("orders_filtered.xlsx")
print("filter:", ws.auto_filter.ref)

Deriving both ends from the sheet — get_column_letter(ws.max_column) and ws.max_row — is what keeps the range right when the data grows or a column is added. A hardcoded "A1:C6" is correct exactly once.

Freezing the header alongside is close to mandatory. Without it, a reader who filters to their region and scrolls loses the column names, which is when people start counting columns and mis-reading figures.

Apply it after the data is written

to_excel replaces the whole sheet, taking any filter with it. The order is always: write, then decorate:

Python
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

def write_with_filter(df, path, sheet="Orders"):
    with pd.ExcelWriter(path, engine="openpyxl") as writer:
        df.to_excel(writer, sheet_name=sheet, index=False)      # data first

    wb = load_workbook(path)
    ws = wb[sheet]
    ws.auto_filter.ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
    ws.freeze_panes = "A2"
    wb.save(path)                                                # then decoration
    return path

write_with_filter(df, "orders_final.xlsx")

Keeping both steps inside one function is the practical defence. When they live apart in a script, the next person adds a to_excel call between them and the filter silently stops appearing — with no error to explain why.

Several sheets in one workbook

Each sheet has its own range, so loop and derive per sheet — and skip the ones with no data:

Python
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

wb = load_workbook("regional.xlsx")
applied = []

for ws in wb.worksheets:
    if ws.max_row < 2:                       # header only, or empty
        continue
    ws.auto_filter.ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
    ws.freeze_panes = "A2"
    applied.append(f"{ws.title}: {ws.auto_filter.ref}")

wb.save("regional_filtered.xlsx")
print("\n".join(applied))

An empty sheet still reports max_row == 1, so without the guard you get a header with dropdowns and nothing under it — which reads as a data-loading failure rather than as an empty result.

Criteria are recorded, not applied

add_filter_column writes the filter's state into the file. Excel shows the filter as active when it opens, but openpyxl hides no rows and a read-back returns everything:

Python
from openpyxl import load_workbook

wb = load_workbook("orders_filtered.xlsx")
ws = wb["Orders"]

ws.auto_filter.add_filter_column(0, ["North", "West"])     # column index 0 = A
wb.save("orders_criteria.xlsx")

check = load_workbook("orders_criteria.xlsx")["Orders"]
print("rows in the file:", check.max_row - 1)               # unchanged — nothing is hidden
Recorded criteria versus data that was actually filtered Recording criteria leaves every row in the file and only sets the filter state Excel displays. Filtering in pandas before writing produces a file that genuinely contains only the selected rows, which is what a per-recipient report needs. add_filter_column all 5,000 rows still in the file Excel shows the filter as set one click reveals everything a display convenience only filter in pandas first only the selected rows written nothing else to reveal safe to send per recipient the only real filtering

The distinction matters whenever the audience is not allowed to see everything. A regional manager's workbook should be produced by df[df["Region"] == "North"].to_excel(...), not by shipping the full dataset with a filter set — that arrangement is one click away from a data-protection incident.

Let readers filter a locked sheet

Protection and filtering coexist, with an inverted flag that catches everyone once:

Python
from openpyxl import load_workbook

wb = load_workbook("orders_final.xlsx")
ws = wb["Orders"]

ws.protection.sheet = True
ws.protection.autoFilter = False      # False = filtering is ALLOWED
ws.protection.sort = False            # sorting allowed too
ws.protection.formatCells = True      # formatting blocked

wb.save("orders_protected.xlsx")

On SheetProtection, each attribute answers "is this action protected against?" — so False permits it. Reading the code as if True meant "enabled" produces a sheet where the dropdowns are visible and refuse to work, which is the single most common complaint about protected report sheets.

Filter dates and numbers, not just text

Excel's dropdowns adapt to the column's type, but only if the values really are dates and numbers. A date column written as text gives readers an alphabetical list of strings instead of the date filters they expect:

Python
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

df = pd.DataFrame({
    "Order_Date": pd.to_datetime(["2026-01-14", "2026-02-02", "2026-02-11"]),
    "Region": ["North", "North", "Central"],
    "Revenue": [79.96, 99.95, 26.25],
})

with pd.ExcelWriter("typed.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Orders", index=False)

wb = load_workbook("typed.xlsx")
ws = wb["Orders"]
for row in range(2, ws.max_row + 1):
    ws[f"A{row}"].number_format = "yyyy-mm-dd"
    ws[f"C{row}"].number_format = "#,##0.00"
ws.auto_filter.ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
ws.freeze_panes = "A2"
wb.save("typed.xlsx")

Writing real datetime values and applying a date number format gives the reader Excel's date grouping — this month, last quarter — which is usually the filter they actually wanted.

Where the filter range comes from

Three sources for the range appear in real code, and only one of them stays correct:

Three ways to build a filter range A hardcoded range is correct once. A range derived from the DataFrame's length is correct until the sheet layout changes. A range derived from the worksheet's own dimensions stays correct in every case. hardcoded "A1:C6" correct exactly once silently wrong next month from len(df) f"A1:C{len(df) + 1}" tracks the row count breaks if a title row is added from the sheet ws.max_row / max_column reflects what was written the one to use

The middle option is the tempting one, because the DataFrame is right there. It breaks the moment the sheet gains a title row, a blank spacer or a second block above the data — all of which are normal in a formatted report. Reading ws.max_row after the write asks the file itself, which is the only source that knows about everything that landed on the sheet.

One caveat comes with that: ws.max_row counts any row Excel considers used, including one that holds only formatting. On a sheet built from a template, derive the last row from a column that is always populated instead, exactly as you would when copying a formula down.

Common pitfalls and gotchas

SymptomCauseFix
Some rows ignore the filterref ended before the last rowBuild the range from ws.max_row
Filter vanished from the outputpandas rewrote the sheet afterwardsWrite data first, then apply the filter
Dropdowns present but do nothingSheet protected without allowing filteringws.protection.autoFilter = False
Criteria set but every row is visibleopenpyxl records, it does not filterFilter in pandas before writing
Column names lost when scrollingNo frozen headerws.freeze_panes = "A2"
Dates filter alphabeticallyDates written as textWrite datetime values with a date format
Header row has dropdowns on an empty sheetFilter applied to a header-only sheetSkip sheets where max_row < 2

Tell readers the filter is there

A filter is invisible until someone notices the little arrows, and plenty of recipients never do. A one-line note above the table, or a sheet-level comment, converts a feature nobody uses into one people rely on:

Python
from openpyxl import load_workbook
from openpyxl.styles import Font

wb = load_workbook("orders_final.xlsx")
ws = wb["Orders"]

ws.insert_rows(1)
ws["A1"] = "Use the arrows on the header row to filter by region, SKU or date."
ws["A1"].font = Font(italic=True, size=10, color="595959")
ws.auto_filter.ref = f"A2:{ws.cell(row=2, column=ws.max_column).column_letter}{ws.max_row}"
ws.freeze_panes = "A3"

wb.save("orders_annotated.xlsx")

Note that inserting the note shifts everything down by one row, so both the filter range and the freeze pane have to move with it — which is the general hazard of adding rows above a decorated sheet, and the reason the decoration step should always run last.

Performance and scale notes

An autofilter is a single attribute in the sheet XML, so it costs nothing to write regardless of the number of rows it covers. The cost is on the reader's side: filtering a 200,000-row sheet is noticeably slow in Excel, and a sheet that large is usually better delivered as a summary plus a detail file. Applying the filter in a loop over many sheets is likewise free — the expensive part of that script is loading and saving the workbook, which happens once either way.

Conclusion

Set ws.auto_filter.ref from the sheet's own dimensions so the range always covers the header and every data row, freeze the header alongside it, and apply both after pandas has finished writing. Remember that criteria are recorded rather than applied: if a recipient must not see other rows, filter the DataFrame and write their own file. And on a protected sheet, autoFilter = False is what lets them filter at all.

Frequently asked questions

What range should auto_filter.ref cover? The header row plus every data row — for example A1 through the last column and ws.max_row. Excel draws the dropdowns on the first row of that range.

Does openpyxl hide rows when I add filter criteria? No. add_filter_column records the criteria so Excel shows the filter as set, but no rows are hidden in the file and reading it back returns everything.

Why did my filter disappear? pandas rewrote the sheet after you added it. Write the data first, then apply the filter to the finished sheet.

Can readers filter a protected sheet? Yes, if you allow it — set ws.protection.autoFilter = False, which on SheetProtection means the action is not blocked.

Up to the parent guide:

Related guides: