Rename and Restyle Existing Excel Tables

Tables created by hand in Excel are named Table1, Table2, Table13 in the order they were made, and formulas that use them read =SUM(Table7[Amount]) — correct, but meaningless to anyone maintaining the workbook. Renaming them in Excel's Table Design tab updates every formula automatically. Renaming them from Python does not: openpyxl stores formulas as plain text, so changing the table's name leaves every Table7[...] pointing at a table that no longer exists, and Excel shows #REF! the next time it calculates. This page renames tables safely — the table object, its key in the sheet and every structured reference in formulas, defined names, data validation and conditional formatting — then applies a consistent style to every table in the workbook, and proves the formulas still calculate the same results. It belongs to Creating Excel Tables and Autofilters with Python.

Prerequisites

pip install openpyxl

The example workbook has an Orders table called Table1, a Regions table called Table2, a Summary sheet whose formulas use both, a defined name, a dropdown whose list comes from a table column, and a conditional formatting rule that refers to a table. LibreOffice is used to recalculate the formulas as a check; it understands structured references.

Step 1 — Find every place a table name is used

import re
from openpyxl import load_workbook

def table_refs(wb, name):
    """Yield (where, text) for every formula-like string that refers to the table."""
    rx = re.compile(rf"(?<![\w.]){re.escape(name)}\[", re.IGNORECASE)
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for c in row:
                if isinstance(c.value, str) and c.value.startswith("=") and rx.search(c.value):
                    yield f"{ws.title}!{c.coordinate}", c.value
        for dv in ws.data_validations.dataValidation:
            if dv.formula1 and rx.search(dv.formula1):
                yield f"{ws.title} validation {dv.sqref}", dv.formula1
        for cf in ws.conditional_formatting:
            for rule in cf.rules:
                for f in rule.formula or []:
                    if rx.search(f):
                        yield f"{ws.title} format {cf.sqref}", f
    for dn_name, dn in wb.defined_names.items():
        if rx.search(dn.attr_text or ""):
            yield f"name {dn_name}", dn.attr_text

wb = load_workbook("orders.xlsx")
for where, text in table_refs(wb, "Table1"):
    print(f"{where:28} {text}")

A structured reference is the table name followed directly by [: Table1[Amount], Table1[[#Data],[Amount]], Table1[#All]. The pattern matches the name only when it is followed by a bracket and not preceded by a letter, digit or full stop, so renaming Table1 never touches Table10 or MyTable1. Table names are not case-sensitive in Excel, so neither is the search. Formulas live in more places than cells: data validation lists, conditional formatting rules and defined names can all refer to a table, and each needs updating. Listing them before changing anything shows the scope of a rename, which is worth a look on a workbook you did not build.

Step 2 — Rename the table and rewrite the references

NAME_RX = re.compile(r"[A-Za-z_\\][\w.]{0,254}")

def rename_table(wb, old, new):
    if not NAME_RX.fullmatch(new) or re.fullmatch(r"[A-Za-z]{1,3}\d+", new):
        raise ValueError(f"{new!r} is not a valid table name")
    taken = {t.lower() for ws in wb.worksheets for t in ws.tables} | {n.lower() for n in wb.defined_names}
    if new.lower() in taken - {old.lower()}:
        raise ValueError(f"{new!r} is already used by a table or defined name")
    ws = next(ws for ws in wb.worksheets if old in ws.tables)
    table = ws.tables[old]
    table.name = table.displayName = new
    del ws.tables[old]
    ws.tables[new] = table

    rx = re.compile(rf"(?<![\w.]){re.escape(old)}(?=\[)", re.IGNORECASE)
    count = 0
    def sub(text):
        nonlocal count
        text, n = rx.subn(new, text)
        count += n
        return text
    for sheet in wb.worksheets:
        for row in sheet.iter_rows():
            for c in row:
                if isinstance(c.value, str) and c.value.startswith("="):
                    c.value = sub(c.value)
        for dv in sheet.data_validations.dataValidation:
            if dv.formula1:
                dv.formula1 = sub(dv.formula1)
        for cf in sheet.conditional_formatting:
            for rule in cf.rules:
                rule.formula = [sub(f) for f in rule.formula or []]
    for dn in wb.defined_names.values():
        dn.attr_text = sub(dn.attr_text or "")
    return count

print("Table1 -> Orders:", rename_table(wb, "Table1", "Orders"), "references updated")
print("Table2 -> Regions:", rename_table(wb, "Table2", "Regions"), "references updated")
try:
    rename_table(wb, "Orders", "OrderAmounts")
except ValueError as e:
    print("refused:", e)

The rename has three parts. The table object's name and displayName are both set — Excel uses the display name in formulas, and openpyxl writes both. The sheet's table list is keyed by name, so the entry is removed under the old key and added under the new one; otherwise openpyxl saves a table whose key and name disagree. Then the same pattern as before rewrites every reference, using a lookahead for the bracket so only the name itself is replaced. Before any of that, the new name is checked: it must be a valid name, must not look like a cell address, and must not already belong to another table or a defined name, because tables and defined names share one namespace in Excel. The last call shows the check working: OrderAmounts is already a defined name, so the rename is refused rather than producing a broken file.

One kind of reference is deliberately not touched: a table name inside a text string, such as the INDIRECT("Table2[Region]") in the dropdown. Excel does not update those either when a table is renamed in its own interface, because to Excel they are text, not references. The pattern above does rewrite it, since the validation formula is searched as plain text — which here is what we want, but it is worth knowing that the rename goes one step further than Excel would.

Step 3 — One style for every table

from openpyxl.worksheet.table import TableStyleInfo

for ws in wb.worksheets:
    for table in ws.tables.values():
        table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True,
                                              showFirstColumn=False, showLastColumn=False, showColumnStripes=False)
wb.save("orders_renamed.xlsx")
print({ws.title: {n: ws.tables[n].tableStyleInfo.name for n in ws.tables} for ws in wb.worksheets if ws.tables})

The loop uses ws.tables.values() to get the table objects; note that openpyxl's ws.tables.items() is unusual and returns each table's range rather than the object, so it is the wrong tool here. Workbooks built by hand usually end up with a different table style on every sheet — whatever was the default when each table was made. Setting the same TableStyleInfo on every table gives the workbook one look. The style names are Excel's built-in ones (TableStyleLight1–21, Medium1–28, Dark1–11); choosing and customising them is covered in Choose and Customise Excel Table Styles with Python.

Step 4 — Prove the formulas still work

for f in orders orders_renamed; do
  soffice --headless --convert-to 'csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true,false,false,3' "$f.xlsx" > /dev/null 2>&1
  echo "$f: $(head -4 "$f-Summary.csv" | tr '\n' ' ')"
done
python -c "
from openpyxl import load_workbook
wb = load_workbook('orders_renamed.xlsx'); s = wb['Summary']
print([s[c].value for c in ('B1', 'B2', 'B3', 'B4')]); print(wb.defined_names['OrderAmounts'].attr_text)"

The check recalculates the Summary sheet of the original and the renamed workbook in LibreOffice and prints both. The totals are identical — 3,480 in total, 1,630 for North, three regions and a largest order of 1,200 — which shows that every formula still finds its table under the new name. The formulas themselves now read =SUM(Orders[Amount]) and =ROWS(Regions[Region]), and the defined name points at Orders[Amount].

What this does not update

The rename covers everything stored as a formula inside the workbook. It cannot reach references held elsewhere: Power Query queries and pivot table caches in the same file that read a table by name, other workbooks that link to this one, VBA code, and pandas or Power BI scripts that load the table by name. openpyxl keeps pivot tables and queries only partially in any case, as described in What openpyxl Loses When You Save an Existing Workbook, so a workbook with pivots or queries is better renamed in Excel itself. For plain data workbooks, renaming in code and checking by recalculation is safe and quick.

Common pitfalls

• Changing only displayName. Also re-key ws.tables, or the sheet's entry keeps the old name.
• Plain find-and-replace. Match the name followed by [, or Table1 will also hit Table10.
• Forgetting validation, formats and names. Formulas live in all of them.
• Names that clash with defined names. Tables and names share one namespace.
• No check afterwards. Recalculate and compare results before and after.

Conclusion

To rename a table with openpyxl, validate the new name against both tables and defined names, set the table's name and displayName, re-key it in ws.tables, and rewrite every structured reference — in cells, data validation, conditional formatting and defined names — with a pattern that matches the old name only when it is followed by [. Apply one TableStyleInfo to every table for a consistent look, and recalculate the workbook before and after to prove nothing broke.

Frequently asked questions

How do I rename an Excel table with openpyxl? Set table.name and table.displayName, move it to the new key in ws.tables, and update all formulas that use the old name.

Does openpyxl update formulas when a table is renamed? No; formulas are stored as text, so structured references must be rewritten with a search and replace.

Can a table have the same name as a defined name? No; tables and defined names share one namespace, so the names must differ.

How do I apply the same style to every table in a workbook? Loop over ws.tables.values() on every sheet and set tableStyleInfo to the same TableStyleInfo.

Related

• Up one level: Creating Excel Tables and Autofilters with Python — tables and filters.
• Convert Several Ranges into Excel Tables at Once — creating tables in bulk.
• Add Calculated Columns to an Excel Table with Python — structured references in columns.
• Read an Excel Table by Name into pandas — scripts that depend on table names.