Creating Excel Tables and Autofilters with Python

A generated report is usually read, not just looked at: someone wants their region, this quarter, the rows over a threshold. Two features make that possible without anyone touching the data — an autofilter for the simple case, and a real Excel table for everything else. This guide, part of Formatting and Charting Excel Reports with Python, covers both, plus the sorting, grouping and protection that go with them.

Install the dependencies

pip install pandas openpyxl

Build a sheet worth filtering

import pandas as pd

rows = [
    ("2026-01-14", "North", "A-100", 4, 19.99),
    ("2026-01-15", "South", "B-200", 2, 49.50),
    ("2026-01-19", "West", "C-300", 7, 12.25),
    ("2026-02-02", "North", "A-100", 5, 19.99),
    ("2026-02-11", "Central", "D-400", 3, 8.75),
]
df = pd.DataFrame(rows, columns=["Order_Date", "Region", "SKU", "Quantity", "Unit_Price"])
df["Revenue"] = (df["Quantity"] * df["Unit_Price"]).round(2)
df.to_excel("orders.xlsx", index=False, sheet_name="Orders")
print(df)

The one-line autofilter

ws.auto_filter.ref is the whole feature. Point it at the header plus the data and Excel adds the dropdowns:

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

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

last_col = get_column_letter(ws.max_column)
ws.auto_filter.ref = f"A1:{last_col}{ws.max_row}"
ws.freeze_panes = "A2"                       # keep the header visible while scrolling

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

Freezing the header is not optional in practice. A filter on a sheet where the header scrolls away leaves the reader looking at anonymous columns, and the two features are always worth applying together — see freezing the header row for the variations.

The range must include the header row, because that is where the dropdowns are drawn. Ending it short of the last data row is the usual bug: rows below the range are not filtered, so a reader who filters to North still sees stray rows from other regions at the bottom.

A real Excel table

A Table object gives the filter plus banding, a name and structured references. It is stricter about its range, and that strictness is what lets Excel maintain it:

from openpyxl import load_workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.utils import get_column_letter

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

ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
table = Table(displayName="Orders", ref=ref)
table.tableStyleInfo = TableStyleInfo(
    name="TableStyleMedium9",
    showRowStripes=True,
    showColumnStripes=False,
    showFirstColumn=False,
    showLastColumn=False,
)
ws.add_table(table)
ws.freeze_panes = "A2"

wb.save("orders_table.xlsx")
print("tables:", list(ws.tables))

displayName follows the same rules as a defined name: no spaces, not starting with a digit, unique within the workbook. The name is what makes =SUM(Orders[Revenue]) work, which is far more robust than =SUM(F2:F6) when rows are added.

Three rules prevent the "Excel found unreadable content" dialog that follows a badly built table:

• every header cell in the first row of ref must be a non-empty string
• header names must be unique, including after Excel's own case-insensitive comparison
• ref must cover exactly the header plus the data, with no trailing blank rows

Sort before you write

auto_filter.add_sort_condition records a sort state for Excel's interface — it does not reorder anything in the file. If the order matters, sort the data before writing it:

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

df = pd.read_excel("orders.xlsx", sheet_name="Orders")
ordered = df.sort_values(["Region", "Revenue"], ascending=[True, False])

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

wb = load_workbook("orders_sorted.xlsx")
ws = wb["Orders"]
ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
ws.auto_filter.ref = ref
ws.auto_filter.add_sort_condition(f"F2:F{ws.max_row}", descending=True)   # UI state only
wb.save("orders_sorted.xlsx")

The sort condition is still worth setting, because it tells Excel to show the sort indicator on the column a reader expects — but the rows are in the order pandas put them, and that is the order anyone reading the raw file will see.

Group rows so a long sheet collapses

Outline groups turn a thousand-row detail sheet into a summary a reader can expand selectively:

from openpyxl import load_workbook

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

# Collapse the January block (rows 2-4 in this example)
ws.row_dimensions.group(2, 4, outline_level=1, hidden=True)
ws.sheet_properties.outlinePr.summaryBelow = True

wb.save("orders_grouped.xlsx")

summaryBelow tells Excel where the group's summary row sits, which decides which way the little plus and minus buttons point. Groups nest up to seven levels, and a report that groups by month and then by region reads far better than the same data as one long list.

Keep a total visible while filtering

A total inside the filtered range disappears the moment a reader filters. Put it outside the range and make it follow the visible rows with SUBTOTAL:

from openpyxl import load_workbook

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

last_data = ws.max_row
total_row = last_data + 2                      # one blank row between data and total
ws[f"E{total_row}"] = "Visible total"
ws[f"F{total_row}"] = f"=SUBTOTAL(109,F2:F{last_data})"
ws[f"F{total_row}"].number_format = "#,##0.00"

wb.save("orders_table_total.xlsx")

SUBTOTAL(109, …) ignores rows the filter has hidden, so the number always matches what the reader can see — which is what they will assume regardless of what you wrote. A plain SUM there is the classic source of "the total does not match the rows" complaints. The SUM and AVERAGE guide covers the function numbers in more detail.

Let readers filter without letting them edit

Filtering and sorting can be permitted on a protected sheet, which is the combination most delivered reports want:

from openpyxl import load_workbook

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

ws.protection.sheet = True
ws.protection.autoFilter = False      # False = the action is ALLOWED
ws.protection.sort = False
ws.protection.formatCells = True      # True = blocked
ws.protection.password = "report"     # deterrent, not security

wb.save("orders_protected.xlsx")

The inverted booleans are a genuine trap: on SheetProtection, a False means "this action is not protected against", so autoFilter = False is what permits filtering. Worksheet passwords are trivially removable and should be treated as a way to prevent accidents, never as access control — for that, control who receives the file.

Filters on several sheets at once

A workbook with a tab per region needs the same treatment on each, and the range differs per sheet. Deriving it from the sheet rather than hardcoding it is what makes the loop safe:

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

wb = load_workbook("regional.xlsx")

for ws in wb.worksheets:
    if ws.max_row < 2:                        # header only, or empty — skip it
        continue
    ws.auto_filter.ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
    ws.freeze_panes = "A2"
    ws.sheet_view.zoomScale = 100

wb.save("regional_filtered.xlsx")
print([f"{ws.title}: {ws.auto_filter.ref}" for ws in wb.worksheets])

The max_row < 2 guard matters because an empty sheet still reports a row count of one, and setting a filter over A1:F1 gives the reader dropdowns on a header with nothing beneath — which looks like the data failed to load. Skipping such sheets, or better still not creating them, is the honest option.

Setting the zoom explicitly is a small courtesy on a wide table: a workbook saved by someone at 70% opens at 70% for everyone, and a reader who has to fix the zoom before reading the report starts out mildly annoyed.

Apply it after pandas has written the sheet

Order of operations catches almost everyone once. DataFrame.to_excel replaces the target sheet wholesale, so a filter, table or freeze applied before the write is discarded without a warning:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo

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

    wb = load_workbook(path)                                     # 2. then decoration
    ws = wb[sheet]
    ref = f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"
    table = Table(displayName=table_name, ref=ref)
    table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True)
    ws.add_table(table)
    ws.freeze_panes = "A2"
    wb.save(path)
    return path

write_filtered(df, "orders_final.xlsx")

Two passes, and the second one owns everything a reader sees. It is worth writing this as a single function rather than as two steps in a script, because the moment they are separated someone will insert a to_excel between them and quietly lose the table.

The same ordering applies to conditional formatting, column widths and cell styles: data first, presentation second, save once.

Size the columns so filtered data stays readable

A filter is only useful if the reader can see what they filtered to. Column widths are the cheapest fix, and they can be derived from the content rather than guessed:

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

def autosize(ws, min_width=9, max_width=42, padding=3):
    for column_cells in ws.columns:
        letter = get_column_letter(column_cells[0].column)
        longest = max(
            (len(str(cell.value)) for cell in column_cells if cell.value is not None),
            default=0,
        )
        ws.column_dimensions[letter].width = min(max(longest + padding, min_width), max_width)

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

The padding allowance is for the filter arrow, which overlaps the last few characters of a header when the column is sized exactly to its content — a header reading Unit_Pri▾ is a small thing that makes a generated report look unfinished. Capping the width keeps one long free-text column from pushing everything else off the screen.

Number formats deserve the same attention on a filtered sheet, because a reader comparing filtered subsets is reading columns of figures rather than individual cells. Applying #,##0.00 to money and a date format to dates is covered in applying number and date formats, and it matters more here than anywhere else in a report.

What openpyxl cannot do

Three things readers ask for are outside the library's reach, and knowing that saves an afternoon:

• Applying a filter. openpyxl records that a filter exists and which criteria are set, but it does not hide rows. A file written with filter criteria opens showing everything until the reader re-applies the filter, and reading such a file back gives you every row regardless.
• Slicers. The button-style filter controls introduced with tables have no openpyxl API. A workbook that already contains slicers keeps them through a round trip in recent versions, but you cannot create one.
• Sorting a range. There is no sort operation on a worksheet. Ordering comes from whatever you wrote, which is why sorting belongs in pandas.

from openpyxl import load_workbook

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

# Recording criteria: Excel shows the filter as set, but rows are NOT hidden here
ws.auto_filter.add_filter_column(1, ["North", "West"])
print(ws.auto_filter.ref, [f.vals for f in ws.auto_filter.filterColumn])

wb.save("orders_criteria.xlsx")

If a recipient must receive only their own rows, filter the data in pandas and write a file per recipient. That is both simpler and safer than shipping one workbook with a filter set, where a single click reveals everything to everyone.

A complete report

Putting the pieces in the right order gives a small, reusable function:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo

def publish(df, path, sheet="Orders", sort_by=("Region", "Revenue"), money=("Revenue",)):
    ordered = df.sort_values(list(sort_by), ascending=[True, False])

    with pd.ExcelWriter(path, engine="openpyxl") as writer:
        ordered.to_excel(writer, sheet_name=sheet, index=False)

    wb = load_workbook(path)
    ws = wb[sheet]
    last_row, last_col = ws.max_row, get_column_letter(ws.max_column)

    table = Table(displayName=sheet, ref=f"A1:{last_col}{last_row}")
    table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True)
    ws.add_table(table)
    ws.freeze_panes = "A2"
    autosize(ws)

    headers = {cell.value: cell.column_letter for cell in ws[1]}
    for name in money:
        letter = headers.get(name)
        if letter:
            for row in range(2, last_row + 1):
                ws[f"{letter}{row}"].number_format = "#,##0.00"
            total_row = last_row + 2
            ws[f"{get_column_letter(ws.max_column - 1)}{total_row}"] = "Visible total"
            ws[f"{letter}{total_row}"] = f"=SUBTOTAL(109,{letter}2:{letter}{last_row})"
            ws[f"{letter}{total_row}"].number_format = "#,##0.00"

    ws.protection.sheet = True
    ws.protection.autoFilter = False        # readers may filter
    ws.protection.sort = False              # and sort
    wb.save(path)
    return path

publish(df, "orders_published.xlsx")

Sorted rows, a named table with banding, a frozen header, sized columns, formatted money, a total that follows the filter, and a sheet nobody can accidentally edit — in one function, in the order that survives. Everything the reader needs to explore the data is there, and nothing they do can change the numbers.

Common errors and fixes

Which feature for which job

Read the filters an existing workbook already has

Before adding anything to a file someone else built, look at what is there. A template often carries a filter or a table whose range stopped matching the data several months ago:

from openpyxl import load_workbook

wb = load_workbook("regional_filtered.xlsx")
for ws in wb.worksheets:
    ref = ws.auto_filter.ref
    tables = {name: table.ref for name, table in ws.tables.items()}
    print(f"{ws.title:12} rows={ws.max_row:<6} filter={ref or '—':12} tables={tables or '—'}")

The output makes a stale range obvious: a filter reading A1:F200 on a sheet with 4,000 rows means 3,800 rows have been outside it since whenever the template was made. Refreshing both the filter and any table ref from ws.max_row on every run is a one-line fix that keeps a delivered workbook honest as the data grows.

Key takeaways

• ws.auto_filter.ref is one line and must span the header row plus every data row.
• Freeze the header whenever you add a filter, or the columns lose their names as soon as someone scrolls.
• A Table adds banding, a name and structured references, and requires unique, non-empty headers with a range that matches the data exactly.
• add_sort_condition sets Excel's sort indicator only — sort the rows in pandas if the order matters.
• Outline groups make long detail sheets navigable, and summaryBelow decides which way the buttons point.
• Keep totals outside the filtered range and use SUBTOTAL(109, …) so they follow what the reader sees.
• On SheetProtection, False means allowed — set autoFilter = False to let readers filter a protected sheet.

Frequently asked questions

What is the difference between an autofilter and a table? An autofilter adds dropdown arrows to a header row. A table is a named object with its own range, banded formatting, structured references and a filter built in — a superset, at the cost of a stricter range.

Why does Excel say my file needs repairing after adding a table? Almost always a duplicate or blank header, a table name that clashes, or a ref that does not match the data. Table headers must be unique, non-empty strings matching the cells in the first row of the range.

Does openpyxl actually sort the rows when I filter? No. auto_filter.add_sort_condition records the filter's state for Excel's interface; it does not reorder anything. Sort the data in pandas before writing if the order matters.

Can I keep a total row visible while filtering? Yes — put it outside the table or filter range and use SUBTOTAL so it follows the visible rows.

Related

Up to the parent guide:

• Formatting and Charting Excel Reports with Python — the wider presentation track.

Go deeper here:

• Add an Autofilter to an Excel Sheet with openpyxl — ranges, multiple sheets and the common mistakes.
• Create an Excel Table with Python — styles, names, structured references and repair errors.
• Sort Excel Rows with Python Before Writing — the order readers actually get.

Related areas:

• Styling Excel Cells with openpyxl — fonts, fills and borders around the table.
• Freeze the Header Row in Excel with openpyxl — the companion to every filter.
• Building Multi-Sheet Excel Dashboards — where filtered detail sheets sit in a report.