Python Doc & Data Automation

Add Excel Tables and Named Ranges with openpyxl

The generated workbook holds the data, and everything downstream treats it as a loose block: readers add filters by hand each month, formulas reference A2:H500 and break when the data grows to 600 rows, and the loader that reads the file back guesses which rows are the table. Adding a table should fix all three, and the first attempt makes Excel refuse to open the file:

We found a problem with some content in 'report.xlsx'. Do you want us to try to recover as much as we can?

After recovery, the table is gone and a log entry mentions "Repaired Records: Table from /xl/tables/table1.xml".

Root Cause

An Excel table is not formatting — it is a tableParts entry in the sheet plus a table1.xml part whose contents must satisfy several rules that openpyxl does not check for you. The table's ref must cover a header row plus at least one data row, the header cells in the sheet must exactly match the column names declared in the table XML, column names must be unique and non-empty, the table name must be unique in the workbook, must not contain spaces, and must not look like a cell reference. Break any of those and Excel treats the part as corrupt and repairs the file by discarding it. Named ranges have their own rules — no spaces, cannot start with a digit, cannot clash with a cell address such as Q1 — and openpyxl will happily write invalid ones. The fix is to validate the names and the reference before writing, which is a dozen lines of code.

Minimal Diagnostic

Check a table definition against the sheet it describes before saving: bounds, header match, name validity and collisions.

# pip install openpyxl
import re
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter, range_boundaries

CELL_LIKE = re.compile(r"^[A-Za-z]{1,3}\d+$")
VALID_NAME = re.compile(r"^[A-Za-z_\\][A-Za-z0-9_.\\]*$")

def check_table(path: Path, sheet: str, name: str, ref: str) -> list[str]:
    problems: list[str] = []
    if not VALID_NAME.match(name):
        problems.append(f"name {name!r} is invalid (letters, digits, underscore and dot only, no spaces)")
    if CELL_LIKE.match(name):
        problems.append(f"name {name!r} looks like a cell reference")
    wb = load_workbook(path)
    try:
        existing = {t for ws in wb.worksheets for t in ws.tables} | set(wb.defined_names)
        if name in existing:
            problems.append(f"name {name!r} already used in the workbook")
        ws = wb[sheet]
        min_col, min_row, max_col, max_row = range_boundaries(ref)
        if max_row <= min_row:
            problems.append(f"ref {ref} has no data rows below the header")
        headers = [ws.cell(min_row, c).value for c in range(min_col, max_col + 1)]
        blanks = [get_column_letter(min_col + i) for i, h in enumerate(headers) if h in (None, "")]
        if blanks:
            problems.append(f"blank header cell(s) in columns {blanks}")
        lowered = [str(h).strip().lower() for h in headers if h not in (None, "")]
        if len(set(lowered)) != len(lowered):
            problems.append(f"duplicate header names: {headers}")
        if max_row > ws.max_row or max_col > ws.max_column:
            problems.append(f"ref {ref} extends past the written data ({ws.dimensions})")
    finally:
        wb.close()
    return problems

if __name__ == "__main__":
    for issue in check_table(Path("out/report.xlsx"), "Data", "Sales Data", "A1:H500") or ["ok"]:
        print(issue)

name 'Sales Data' is invalid (letters, digits, underscore and dot only, no spaces)
duplicate header names: ['Region', 'Amount', 'Amount', 'Status', None, 'Qty', 'Qty', 'Notes']
blank header cell(s) in columns ['E']
ref A1:H500 extends past the written data (A1:H412)

Four separate reasons Excel would have repaired the file, each reported before anything is written.

Rules an Excel table must satisfy The table name must contain no spaces and must not look like a cell reference, or Excel repairs the file. Column headers in the sheet must be non-empty and unique, or the table part is discarded. The reference must include a header row and at least one data row and must not extend beyond written cells. The table name must be unique across the workbook, including named ranges. Rule Violating example Symptom No spaces in the name 'Sales Data' file repaired Name unlike a cell ref 'Q1' file repaired Headers non-empty blank cell in E1 table discarded Headers unique two columns named Amount table discarded Ref covers written cells A1:H500 over 412 rows table discarded

Fix: Validate, Then Create the Table and Named Ranges

Derive the reference from the data actually written, sanitise names, and add the table with a style. Changed lines carry comments.

# pip install "pandas>=2.2" openpyxl
import re
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.worksheet.table import Table, TableStyleInfo

CELL_LIKE = re.compile(r"^[A-Za-z]{1,3}\d+$")

def safe_object_name(raw: str, used: set[str]) -> str:
    name = re.sub(r"[^\w.]+", "_", str(raw).strip()).strip("_") or "Table"     # changed: legal characters
    if name[0].isdigit():
        name = f"_{name}"                                                       # changed: cannot start with a digit
    if CELL_LIKE.match(name):
        name = f"{name}_tbl"                                                    # changed: not a cell reference
    candidate, n = name[:255], 2
    while candidate.lower() in {u.lower() for u in used}:
        candidate = f"{name[:250]}_{n}"
        n += 1
    used.add(candidate)
    return candidate

def write_with_table(frame: pd.DataFrame, dest: Path, sheet: str = "Data",
                     table_name: str = "Sales Data") -> str:
    frame = frame.loc[:, [c for c in frame.columns if str(c).strip()]]          # changed: no blank headers
    frame.columns = pd.io.common.dedup_names(list(map(str, frame.columns)), is_potential_multiindex=False) \
        if hasattr(pd.io.common, "dedup_names") else list(map(str, frame.columns))
    dest.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(dest, engine="openpyxl") as writer:
        frame.to_excel(writer, sheet_name=sheet, index=False)
    wb = load_workbook(dest)
    try:
        ws = wb[sheet]
        used = {t for s in wb.worksheets for t in s.tables} | set(wb.defined_names)
        name = safe_object_name(table_name, used)
        last_col = get_column_letter(len(frame.columns))
        ref = f"A1:{last_col}{len(frame) + 1}"                                   # changed: from real data size
        if len(frame) == 0:
            raise ValueError("an Excel table needs at least one data row")
        table = Table(displayName=name, ref=ref)
        table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True,
                                              showFirstColumn=False, showLastColumn=False)
        ws.add_table(table)                                                      # changed: after the data exists
        for column in ("region", "amount"):
            if column in frame.columns:
                idx = list(frame.columns).index(column) + 1
                letter = get_column_letter(idx)
                range_name = safe_object_name(f"{column}_values", used)
                wb.defined_names[range_name] = DefinedName(
                    range_name, attr_text=f"'{sheet}'!${letter}$2:${letter}${len(frame) + 1}")
        wb.save(dest)
        return name
    finally:
        wb.close()

if __name__ == "__main__":
    sales = pd.DataFrame({"region": ["EMEA", "APAC"], "amount": [1240.5, 980.0], "status": ["Open", "Closed"]})
    print(write_with_table(sales, Path("out/report.xlsx")))

Sales_Data

Building the reference from len(frame) rather than a constant is what keeps the table valid as the data grows or shrinks. Sheet names containing spaces must be quoted in a defined name's reference — 'Sales Data'!$B$2:$B$100 — which the code does unconditionally, because an unquoted name is another silent repair.

Once the table exists, readers get filter buttons and banded rows for free, and the range grows automatically when a user types in the row beneath it — which is what makes the loader in read specific cell ranges from Excel robust.

Creating a valid table The data is written first so the sheet has real cells. The reference is derived from the number of rows and columns actually written, always including the header row and at least one data row. The table name is sanitised for characters, leading digits, cell-reference lookalikes and collisions with existing tables and named ranges. The table is added with a style, then named ranges are defined with quoted sheet names. Finally the workbook is opened to confirm Excel accepts it. Write data sheet has cells Derive ref A1:H{rows+1} Sanitise name no spaces, unique Add table with a style Named ranges quoted sheet name

Variant Fix 1: Structured References in Formulas

A table's real benefit appears in formulas: =SUM(Sales_Data[amount]) keeps working when rows are added, where =SUM(C2:C412) does not.

# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook

def add_structured_formulas(path: Path, sheet: str, table_name: str, summary_sheet: str = "Summary") -> None:
    wb = load_workbook(path)
    try:
        summary = wb[summary_sheet] if summary_sheet in wb.sheetnames else wb.create_sheet(summary_sheet)
        summary["A1"], summary["B1"] = "Metric", "Value"
        rows = {
            "Total amount": f"=SUM({table_name}[amount])",
            "Orders": f"=COUNTA({table_name}[region])",
            "Average order": f"=IFERROR(AVERAGE({table_name}[amount]),0)",
            "Open orders": f'=COUNTIFS({table_name}[status],"Open")',
        }
        for i, (label, formula) in enumerate(rows.items(), start=2):
            summary[f"A{i}"] = label
            summary[f"B{i}"] = formula                       # structured reference: grows with the table
        wb.calculation.fullCalcOnLoad = True                 # Excel computes them when the file opens
        wb.save(path)
    finally:
        wb.close()

Column names inside brackets must match the header cells exactly, including case and spaces; a mismatch produces #NAME? in Excel rather than a repair prompt. Because openpyxl writes no cached values, these cells read as None from Python until a spreadsheet application recalculates — the behaviour explained in fix openpyxl data_only formula returns None.

Variant Fix 2: Dynamic Named Ranges That Grow

Where a table is not appropriate — a lookup list feeding data validation, for instance — a defined name with a formula grows with the data:

# pip install openpyxl
from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName

def add_dynamic_name(wb: Workbook, name: str, sheet: str, column_letter: str = "A") -> None:
    quoted = f"'{sheet}'" if " " in sheet else sheet
    formula = (f"OFFSET({quoted}!${column_letter}$2,0,0,"
               f"COUNTA({quoted}!${column_letter}:${column_letter})-1,1)")
    wb.defined_names[name] = DefinedName(name, attr_text=formula)

OFFSET with COUNTA returns a range as tall as the filled cells, which keeps dropdown lists current as entries are added — the pattern used in add dropdown lists to Excel with openpyxl. It requires the column to be contiguous; a blank cell truncates the range. OFFSET is also volatile, recalculating on every change, which is irrelevant for a list of forty regions and worth avoiding for names used in thousands of formulas — there, a table's structured reference is both faster and clearer.

Fixed ranges versus table references With a fixed range, the total formula sums C2 to C412 and the average uses the same bounds, so appending 80 rows leaves them out and the totals are silently wrong. With structured references, the total sums the amount column of the table and the average uses the same column, so appending rows extends the table and both figures update automatically. Fixed ranges =SUM(C2:C412) =AVERAGE(C2:C412) 80 rows appended -> still sums 411 rows totals silently wrong Structured references =SUM(Sales_Data[amount]) =AVERAGE(Sales_Data[amount]) 80 rows appended -> table ref grows to 492 totals stay correct

Updating a Table When the Data Changes

Rewriting a sheet under an existing table leaves the table's reference pointing at the old bounds. Update both together:

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

def refresh_table_data(path: Path, sheet: str, table_name: str, frame: pd.DataFrame) -> str:
    wb = load_workbook(path)
    try:
        ws = wb[sheet]
        if table_name not in ws.tables:
            raise KeyError(f"table {table_name!r} not found on sheet {sheet!r}")
        ws.delete_rows(2, ws.max_row)                                  # keep the header row
        for row in frame.itertuples(index=False):
            ws.append(list(row))
        last_col = get_column_letter(len(frame.columns))
        new_ref = f"A1:{last_col}{len(frame) + 1}"
        ws.tables[table_name].ref = new_ref                            # keep the table in step with the data
        for name, dn in list(wb.defined_names.items()):
            if f"'{sheet}'!" in str(dn.value) or f"{sheet}!" in str(dn.value):
                letter = str(dn.value).split("$")[1]
                dn.value = f"'{sheet}'!${letter}$2:${letter}${len(frame) + 1}"
        wb.save(path)
        return new_ref
    finally:
        wb.close()

Deleting rows and appending keeps the header row's formatting, the table style and any conditional formatting attached to the sheet. Updating the defined names in the same function prevents the mismatch where a dropdown list still points at last month's shorter range — a bug that shows up as missing options rather than as an error.

Verification

Confirm the saved file opens cleanly and that the table and names describe the data that is actually there.

# pip install openpyxl
import shutil
import subprocess
import tempfile
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.utils import range_boundaries

def verify_table(path: Path, sheet: str, table_name: str, expected_rows: int) -> None:
    wb = load_workbook(path)
    try:
        ws = wb[sheet]
        assert table_name in ws.tables, f"table {table_name!r} missing (was the file repaired?)"
        ref = ws.tables[table_name].ref
        min_col, min_row, max_col, max_row = range_boundaries(ref)
        assert max_row - min_row == expected_rows, f"{ref} covers {max_row - min_row} data rows"
        headers = [ws.cell(min_row, c).value for c in range(min_col, max_col + 1)]
        assert all(h not in (None, "") for h in headers), f"blank header in {headers}"
        assert len({str(h).lower() for h in headers}) == len(headers), f"duplicate headers {headers}"
        for name, dn in wb.defined_names.items():
            assert "#REF" not in str(dn.value), f"named range {name} points at #REF"
    finally:
        wb.close()
    if shutil.which("soffice"):
        with tempfile.TemporaryDirectory() as tmp:
            proc = subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", tmp, str(path)],
                                  capture_output=True, text=True, timeout=180)
            assert proc.returncode == 0, f"LibreOffice could not open the file: {proc.stderr[:200]}"
    print(f"{path.name}: table {table_name} covers {expected_rows} rows; names valid")

The LibreOffice conversion is a practical stand-in for "Excel opens it without repairing": a structurally invalid table part usually makes the conversion fail or log errors, which catches the problem in CI rather than on a user's desk.

FAQ

Can one sheet hold several tables? Yes, as long as their ranges do not overlap and their names are unique across the workbook.

Does pandas write tables? No. to_excel writes plain cells; add the table afterwards with openpyxl, or use xlsxwriter's add_table when creating a new file.

Why is my table name different from what I passed? The sanitiser replaced illegal characters or resolved a collision. Log the returned name and use it for structured references.

Do tables survive being read and rewritten by pandas? No — writing a new file drops them. Load with openpyxl and modify in place to keep tables, styles and validations.

Part of Writing Excel Formulas and Charts with openpyxl.

/html>