Use Structured Table References in Excel Formulas from Python

Excel tables give columns names, and formulas can use those names instead of addresses: =SUM(Sales[Total]) rather than =SUM(D2:D250). The difference matters most in files that Python generates and people then extend. An address range is fixed at the size Python wrote; a structured reference follows the table as it grows. Writing these formulas from Python is straightforward with one exception — the familiar [@Column] shorthand is a display form, not what the file stores. This guide, part of Working with Excel Formulas in Python, covers both libraries and verifies the results.

Prerequisites

pip install openpyxl xlsxwriter

LibreOffice is used at the end to recalculate and check the openpyxl output; it is optional.

Step 1 — Create the table with openpyxl

A structured reference needs a real Excel table (a ListObject), not just a range that looks like one. With openpyxl, write the data including the header, then add a Table whose ref covers the header and every data row:

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

wb = Workbook()
ws = wb.active
ws.title = "Data"
ws.append(["Region", "Qty", "Price", "Total"])
for row in [["North", 2, 10], ["South", 3, 20], ["East", 1, 5]]:
    ws.append(row)

sales = Table(displayName="Sales", ref="A1:D4")
sales.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True)
ws.add_table(sales)

The table name must be unique in the workbook, start with a letter or underscore, and contain no spaces. More on table options in Create an Excel Table with Python.

Step 2 — Write the calculated column in the stored form

In Excel you would type =[@Qty]*[@Price] into the Total column. That @ form is what Excel shows; inside the file the same formula is stored as Sales[[#This Row],[Qty]]*Sales[[#This Row],[Price]]. openpyxl writes formula text exactly as given, so give it the stored form:

for row in range(2, 5):
    ws[f"D{row}"] = "=Sales[[#This Row],[Qty]]*Sales[[#This Row],[Price]]"

Write =[@Qty]*[@Price] instead and the file opens with #NAME? in every Total cell — Excel does not parse the display shorthand when loading a file. Rather than remembering the long form, convert with a small helper:

import re

def expand_this_row(formula: str, table: str) -> str:
    def repl(m):
        column = m.group(1).strip("[]")
        return f"{table}[[#This Row],[{column}]]"
    return re.sub(r"\[@(\[[^\]]+\]|[^\]\s,)*+/-]+)\]?", repl, formula)

print(expand_this_row("=[@Qty]*[@Price]", "Sales"))
# =Sales[[#This Row],[Qty]]*Sales[[#This Row],[Price]]
print(expand_this_row("=[@[Unit Price]]*2", "Sales"))
# =Sales[[#This Row],[Unit Price]]*2

The second example shows the bracketed form Excel uses for column names with spaces, [@[Unit Price]], which the helper also expands.

Step 3 — Summary formulas over table columns

References to a whole column need no special handling. They work in any cell, inside or outside the table, and in any function that takes a range:

ws["F1"] = "Sum of Total"
ws["F2"] = "=SUM(Sales[Total])"
ws["G1"] = "North total"
ws["G2"] = '=SUMIFS(Sales[Total],Sales[Region],"North")'
ws["H1"] = "Rows"
ws["H2"] = "=ROWS(Sales[#Data])"
ws["I1"] = "Header of col 2"
ws["I2"] = "=INDEX(Sales[#Headers],2)"
wb.save("structured.xlsx")
print(load_workbook("structured.xlsx")["Data"]["G2"].value)

The special items select parts of the table: [#Data] the data rows, [#Headers] the header row, [#Totals] the total row if there is one, and [#All] everything. They combine with columns — Sales[[#Headers],[Qty]] is the Qty header cell.

The this-row form is the only one tied to a position: it means "the cell in this column on the formula's own row", so it only makes sense in a cell whose row belongs to the table. Written in a cell outside the table's rows it returns #VALUE!. Everything else in the table above can be used anywhere in the workbook, on any sheet, which is what makes structured references useful for summary sheets: =SUM(Sales[Total]) on a dashboard tab keeps working when the data sheet is moved, renamed or extended. Referencing a table on another sheet needs no sheet prefix at all — the table name is unique in the workbook, so Sales[Total] is enough. Totals rows are the subject of Add a Total Row to an Excel Table with Python.

Step 4 — Check the results by recalculating

openpyxl does not calculate anything, so check the formulas by recalculating a copy with LibreOffice and reading the cached values:

import subprocess
from pathlib import Path

Path("checked").mkdir(exist_ok=True)
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "checked", "structured.xlsx"],
               check=True, capture_output=True, timeout=120)
v = load_workbook("checked/structured.xlsx", data_only=True)["Data"]
print([v[f"D{r}"].value for r in range(2, 5)])            # [20, 60, 5]
print(v["F2"].value, v["G2"].value, v["H2"].value, v["I2"].value)   # 85 20 3 Qty

The calculated column gives 20, 60 and 5, the column sum 85, the conditional sum 20 and the row count 3. Had the Total column been written with [@Qty], the same check would have shown #NAME? — a cheap test worth adding to any job that writes table formulas with openpyxl. In a test suite, assert that no recalculated cell in the table holds a string starting with # — that single check catches misspelled table or column names as well as the shorthand problem, because every one of those failures surfaces as an Excel error value after recalculation.

Step 5 — Let xlsxwriter do the conversion

xlsxwriter builds tables with calculated columns directly, and it accepts the @ shorthand in a column's formula, converting it to the stored form itself:

import xlsxwriter

book = xlsxwriter.Workbook("structured_xw.xlsx")
sheet = book.add_worksheet("Data")
sheet.add_table("A1:D4", {
    "name": "Sales",
    "data": [["North", 2, 10], ["South", 3, 20], ["East", 1, 5]],
    "columns": [
        {"header": "Region"},
        {"header": "Qty"},
        {"header": "Price"},
        {"header": "Total", "formula": "=[@Qty]*[@Price]"},
    ],
})
sheet.write_formula("F2", "=SUM(Sales[Total])")
book.close()

xlsxwriter also records the formula as the table's calculated column formula, so when someone adds a row to the table in Excel, the Total cell of the new row fills itself. openpyxl's tables do not set that property, which is one more reason to build tables with xlsxwriter when a new file is being created. One caution for testing: LibreOffice did not evaluate xlsxwriter's unqualified [#This Row] references in our check and showed #N/A, although the form matches what Excel writes; verify xlsxwriter table formulas in Excel.

Common pitfalls

• Writing [@Column] with openpyxl. It opens as #NAME?. Expand to Table[[#This Row],[Column]] first.
• Table ref without the header. Table(ref=...) must include the header row; a ref that starts on the first data row makes Excel repair the file.
• Special characters in column names. Inside a reference, [, ], # and ' must be escaped with an apostrophe: a column named #Units is referenced as Sales['#Units]. Simpler still, keep table column names to letters, digits, spaces and underscores.
• Appending rows does not grow the table. ws.append() writes below the table, but the table's ref still ends at the old last row, so Sales[Total] ignores the new rows. After appending, set ws.tables["Sales"].ref to the new range, for example "A1:D7", and the structured formulas cover the added rows again.
• Renaming columns later. Excel updates structured references when a column is renamed in Excel, but a Python script that rewrites the header text does not update formulas. Rename columns in the data before writing formulas that use them.

Performance and scale notes

Structured references cost Excel nothing extra compared with addresses; they are resolved to ranges internally. In a generated workbook their benefit is robustness: when a later job appends rows by resizing the table's ref, every structured formula covers the new rows without being rewritten, while address-based formulas would need regenerating. For writing large tables, xlsxwriter is also markedly faster than openpyxl, and its constant-memory mode is covered in Write a Million Rows to Excel with xlsxwriter.

Conclusion

Structured references make generated formulas follow the table instead of a fixed range. Whole-column references like Sales[Total] and special items like [#Data] work as written in either library. This-row references must be stored in their long form, Sales[[#This Row],[Qty]]: write that yourself with openpyxl, or let xlsxwriter convert [@Qty] in table column formulas. Check openpyxl output by recalculating a copy, and keep column names free of characters that need escaping.

Frequently asked questions

Why does @Qty show #NAME? when I write it with openpyxl? The @ shorthand is how Excel displays a this-row reference, but the file format stores the long form Table[[#This Row],Column]. openpyxl writes your text verbatim, so write the long form yourself.

Does xlsxwriter accept the @ shorthand? Yes, in table column formulas. xlsxwriter converts @Qty into the stored #This Row form automatically when you pass it as a column formula in add_table.

Do structured references update when the table grows? Yes. SalesTotal always means the whole data area of that column, so when rows are added to the table — by a person or a later script run that resizes the table — every formula using it covers the new rows.

How do I refer to a column whose name contains a bracket or a hash? Escape the special characters ,, # and the apostrophe with a leading apostrophe inside the reference, for example Sales'#Units for a column named #Units.

Related

• Up one level: Working with Excel Formulas in Python — writing and reading formulas.
• Creating Excel Tables and Autofilters with Python — tables in depth.
• Write Dynamic Array Formulas like FILTER and UNIQUE from Python — spilling formulas over table columns.
• Read an Excel Table by Name into pandas — reading tables back.