Read an Excel Table by Name into pandas

An Excel table — the striped, filterable block you get from Insert → Table — records its own name and exact range in the file. That makes it the most reliable thing to read from a workbook somebody else maintains: rows can be added, a title can be inserted above it, the table can be moved to another sheet, and Sales is still Sales. pandas does not know about tables, though. read_excel reads sheets and ranges, not table names. This guide, part of Creating Excel Tables and Autofilters with Python, bridges the gap with openpyxl.

Prerequisites

pip install pandas openpyxl

This workbook has a title above a table named Sales that includes a totals row and calculated columns, plus a second table on another sheet:

from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active
ws.title = "Data"
ws["A1"] = "Sales report — September"
ws.append([])
for row in [["Region", "Rep", "Revenue", "Tax"],
            ["North", "Ana", 1200, "=C4*0.2"],
            ["South", "Ben", 950, "=C5*0.2"],
            ["Total", None, "=SUBTOTAL(109,C4:C5)", None]]:
    ws.append(row)
sales = Table(displayName="Sales", ref="A3:D6", totalsRowCount=1)
sales.tableStyleInfo = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True)
ws.add_table(sales)

lookup = wb.create_sheet("Lookup")
lookup.append(["Code", "Region"])
lookup.append(["N", "North"])
lookup.append(["S", "South"])
lookup.add_table(Table(displayName="Regions", ref="A1:B3"))
wb.save("sales_tables.xlsx")

Step 1 — List the tables in a workbook

Each openpyxl worksheet has a tables mapping. Iterating values() gives Table objects with the name, range and row counts; table names are unique across the whole workbook, so a flat listing is unambiguous.

from openpyxl import load_workbook

wb = load_workbook("sales_tables.xlsx")
for sheet in wb.worksheets:
    for table in sheet.tables.values():
        print(f"{table.displayName:10} sheet={sheet.title:7} ref={table.ref:6} "
              f"header_rows={table.headerRowCount} totals_rows={table.totalsRowCount or 0}")

sheet.tables.items() yields (name, ref) pairs with the range as a string, which is convenient when that is all you need. totalsRowCount is None rather than 0 when a table has no totals row, hence the or 0.

Step 2 — Read one table into a DataFrame

Find the table by name on whichever sheet holds it, read the cells in its range, use the header row for column names, and exclude the totals row. Loading with data_only=True returns the cached results of formulas — the Tax values — rather than formula strings.

import pandas as pd

def read_table(path: str, name: str, include_totals: bool = False) -> pd.DataFrame:
    wb = load_workbook(path, data_only=True)
    for sheet in wb.worksheets:
        if name in sheet.tables:
            table = sheet.tables[name]
            rows = [[cell.value for cell in row] for row in sheet[table.ref]]
            header_count = table.headerRowCount if table.headerRowCount is not None else 1
            totals = 0 if include_totals else (table.totalsRowCount or 0)
            header = rows[0] if header_count else [f"Column{i + 1}" for i in range(len(rows[0]))]
            body = rows[header_count:len(rows) - totals]
            return pd.DataFrame(body, columns=header)
    available = [t for s in wb.worksheets for t in s.tables]
    raise KeyError(f"no table named {name!r}; workbook has {available}")

sales = read_table("sales_tables.xlsx", "Sales")
regions = read_table("sales_tables.xlsx", "Regions")
print(sales)
print(regions)

The title in A1 and the blank row beneath it are never read, because the range comes from the table definition. If someone inserts three more rows of notes above the table next month, Excel moves the table and updates its range, and this function still reads exactly the right cells.

Step 3 — Understand formula values and data_only

The Tax column is calculated, and data_only=True returns the values Excel stored the last time it calculated and saved the file. A workbook written by openpyxl or xlsxwriter and never opened in Excel has no stored results, so those cells read as None — exactly what this sample shows.

print(sales["Tax"].tolist())                     # [None, None] — never calculated by Excel

formulas = load_workbook("sales_tables.xlsx")    # without data_only: the formula strings
print([formulas["Data"][f"D{r}"].value for r in (4, 5)])

For files that come from people working in Excel this is rarely a problem, because Excel saves the results. For files produced by other scripts, either recalculate them first or compute the column in pandas. Both routes are covered in Read Formula Results with openpyxl data_only.

Step 4 — Use read_excel with the table's range

When you prefer pandas' own parsing — its dtype handling, its date conversion, a faster engine — take only the range from openpyxl and translate it into skiprows, nrows and usecols. The range lookup needs a normal openpyxl load, because table definitions are not available in read-only mode, but it only has to happen once per file.

from openpyxl.utils import range_boundaries, get_column_letter

def table_location(path: str, name: str) -> tuple[str, dict]:
    wb = load_workbook(path, read_only=False)
    for sheet in wb.worksheets:
        if name in sheet.tables:
            table = sheet.tables[name]
            min_col, min_row, max_col, max_row = range_boundaries(table.ref)
            data_rows = max_row - min_row - (table.totalsRowCount or 0)
            return sheet.title, {
                "skiprows": min_row - 1,
                "nrows": data_rows,
                "usecols": f"{get_column_letter(min_col)}:{get_column_letter(max_col)}",
            }
    raise KeyError(name)

sheet_name, options = table_location("sales_tables.xlsx", "Sales")
print(sheet_name, options)
print(pd.read_excel("sales_tables.xlsx", sheet_name=sheet_name, **options))

nrows counts data rows after the header, so the totals row is excluded by subtracting totalsRowCount. The explicit read_only=False is a reminder: a read-only worksheet has no tables attribute at all, so the lookup would fail with an AttributeError.

Step 5 — Load every table into a dictionary

Workbooks built as small databases — a table per entity — are easiest to work with as a dictionary of DataFrames keyed by table name, the tabular equivalent of read_excel(sheet_name=None).

def read_all_tables(path: str) -> dict[str, pd.DataFrame]:
    wb = load_workbook(path, data_only=True)
    frames = {}
    for sheet in wb.worksheets:
        for table in sheet.tables.values():
            rows = [[cell.value for cell in row] for row in sheet[table.ref]]
            end = len(rows) - (table.totalsRowCount or 0)
            frames[table.displayName] = pd.DataFrame(rows[1:end], columns=rows[0])
    return frames

tables = read_all_tables("sales_tables.xlsx")
enriched = tables["Sales"].merge(tables["Regions"], on="Region", how="left")
print(enriched)

Loading the workbook once for all tables is noticeably faster than calling read_table for each name, since each call reloads the file. The merge shows the payoff: two tables from different sheets, joined by column name without a single cell address in the code.

Step 6 — Check the table's shape before trusting it

Reading by name protects against the table moving; it does not protect against the table changing. Someone can rename a column header, delete a column they thought was unused, or type text into a numeric column. A short contract check straight after the read turns those edits into a clear error at the point the data enters your job.

EXPECTED = {"Region": "object", "Rep": "object", "Revenue": "number"}

def check_table(frame: pd.DataFrame, name: str, expected: dict[str, str]) -> None:
    problems = []
    missing = [column for column in expected if column not in frame.columns]
    if missing:
        problems.append(f"missing columns {missing}; found {list(frame.columns)}")
    for column, kind in expected.items():
        if column in frame.columns and kind == "number":
            bad = pd.to_numeric(frame[column], errors="coerce").isna() & frame[column].notna()
            if bad.any():
                problems.append(f"{column}: non-numeric values in rows {list(frame.index[bad] + 1)}")
    if frame.empty:
        problems.append("table has no data rows")
    if problems:
        raise ValueError(f"table {name!r} failed checks: " + "; ".join(problems))

check_table(sales, "Sales", EXPECTED)
print("Sales table passed its checks with", len(sales), "rows")

Row numbers in the message are positions within the table's data rows, which is how a person looking at the table in Excel would count them. For richer rules — allowed values, ranges, uniqueness — the schema approach in Validate Excel Data with pandera Schemas applies unchanged to a frame read from a table.

Common pitfalls

Performance and scale notes

A full load_workbook parses every cell of every sheet, which is the dominant cost for large workbooks. When a big workbook contains one small table you need, use Step 4: get the range from a normal load of the table definitions, then read just that range with pandas, optionally with the faster calamine engine as in Speed Up pandas Excel Reads with the calamine Engine. For repeated reads, cache the range per file modification time so the table definitions are only parsed once.

Conclusion

Excel tables carry their own name and range, which makes them the most robust way to read data from a workbook someone else edits. Find the table by name across all sheets with openpyxl, read the cells in table.ref, take the header row for column names and drop the totals row using totalsRowCount. Use data_only=True for formula results, fall back to read_excel with a computed skiprows, nrows and usecols when you want pandas' parser, and load every table at once when the workbook is a collection of them.

Frequently asked questions

Can pandas read an Excel table by name directly? No. read_excel works with sheets and cell ranges. Use openpyxl to look up the table's range by name, then build the DataFrame or pass the range to read_excel.

Do table names need to be unique? Yes. Excel enforces unique table names across the whole workbook, so searching every sheet for a name always finds at most one table.

What happens if the table has no header row? headerRowCount is 0 and every row in the range is data. Supply column names yourself, as the read_table function does with generated names.

Can I write a DataFrame back into an existing table? Yes. Write the values into the table's cells with openpyxl and update table.ref if the number of rows changes, so Excel's table covers the new data.

Related

• Up one level: Creating Excel Tables and Autofilters with Python — building tables and filters from Python.
• Create an Excel Table with Python — the write side of the same objects.
• Read Multiple Tables from One Excel Sheet — when the author did not create table objects.
• Read All Sheets from an Excel File into DataFrames — the sheet-based equivalent.
• Reference Cells, Ranges and Tables with xl() in Python in Excel — reading tables by name from inside Excel.