Write an Excel Table with Formula Columns Using XlsxWriter

An Excel table — the banded, filterable range created with Insert › Table — is more than formatting. Its columns have names that formulas can use (Orders[Net]), a calculated column fills its formula into every row and into rows added later, and a totals row can sum, count or average whatever is currently filtered. XlsxWriter writes tables with all of that from Python, which makes generated reports easier for people to extend: they type a new order under the table and the net amount calculates itself. The details that trip people up are the table's range, which must include the header and totals rows, and the form of the structured references, because one form works in Excel only while another works in every spreadsheet application. This page writes a DataFrame as a table with formula columns and totals, compares the two reference styles, and checks the results by recalculating the file. It belongs to Building Excel Reports with xlsxwriter.

Prerequisites

pip install xlsxwriter pandas openpyxl

LibreOffice (soffice) is used in the last step to recalculate the workbook, and openpyxl to read the results.

Step 1 — A DataFrame as a table, with a formula column

add_table() takes the range, the data as a list of rows, and a list of column definitions. A column with a formula becomes a calculated column:

import pandas as pd
import xlsxwriter

orders = pd.DataFrame({"Product": ["Bolts", "Nuts", "Washers", "Rivets"], "Qty": [120, 80, 300, 45],
                       "Price": [0.45, 0.12, 0.05, 1.80], "Discount": [0.10, 0.0, 0.05, 0.15]})

wb = xlsxwriter.Workbook("orders_table.xlsx")
ws = wb.add_worksheet("Orders")
money = wb.add_format({"num_format": "#,##0.00"})
pct = wb.add_format({"num_format": "0%"})
columns = [
    {"header": "Product", "total_string": "Total"},
    {"header": "Qty", "total_function": "sum"},
    {"header": "Price", "format": money},
    {"header": "Discount", "format": pct},
    {"header": "Net", "format": money, "total_function": "sum",
     "formula": "=[@Qty]*[@Price]*(1-[@Discount])"},
]
last_row = len(orders) + 1                       # header + data rows + totals row, zero-based
ws.add_table(0, 0, last_row, len(columns) - 1, {"name": "Orders", "data": orders.values.tolist(),
             "columns": columns, "total_row": True, "style": "Table Style Medium 2"})
ws.set_column(0, 0, 12)
ws.set_column(1, 4, 11)
wb.close()
print(f"table Orders: A1:E{last_row + 1} ({len(orders)} data rows, header and totals)")

The range passed to add_table() must cover the header row, every data row and the totals row: with four orders that is rows 0 to 5, zero-based, or A1:E6. Getting it one row short is the common mistake — the last data row silently falls outside the table, or worse, ends up as the totals row. The DataFrame contributes only its values (orders.values.tolist()); the headers come from the column definitions, which is also where per-column formats live. The Net column has no data, only a formula, written with Excel's [@Column] "this row" shorthand. Table names follow the rules for defined names — letters, digits and underscores, starting with a letter, unique in the workbook — so Orders works but Q3 orders does not; choosing a meaningful name pays off every time someone writes a formula against the table.

Step 2 — What actually ends up in the file

import zipfile

with zipfile.ZipFile("orders_table.xlsx") as z:
    sheet = z.read("xl/worksheets/sheet1.xml").decode()
    table = z.read("xl/tables/table1.xml").decode()
cell = sheet[sheet.find('<c r="E2"'):]
print("E2 formula:", cell[cell.find("<f>") + 3:cell.find("</f>")])
total = sheet[sheet.find('<c r="E6"'):]
print("E6 formula:", total[total.find("<f>") + 3:total.find("</f>")])
print("calculated column stored:", "<calculatedColumnFormula>" in table)

XlsxWriter translates [@Qty] into the form Excel 2010 and later save, [[#This Row],Qty] — a reference to the current row without the table name, which Excel resolves from the table the cell is in. The built-in sum total becomes SUBTOTAL(109,[Net]); function 109 sums only visible rows, so the total follows the table's filters. The formula is also recorded as the column's calculatedColumnFormula, which is what makes Excel fill it into new rows typed under the table. XlsxWriter cannot calculate formulas, so it stores 0 as each formula's last result; Excel recalculates on opening, but anything that reads cached values — pandas, openpyxl with data_only=True — sees zeros until the file has been opened and saved in a spreadsheet application. The options for that are covered in Store Formula Results with xlsxwriter.

Step 3 — References that work everywhere

The shortened references are valid Excel, but not every application resolves a reference without its table name. Recalculate the file in LibreOffice and look:

import subprocess
from openpyxl import load_workbook

def recalculated(path):
    load_workbook(path).save("roundtrip.xlsx")            # drop XlsxWriter's cached zeros so LibreOffice recalculates
    subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "roundtrip.xlsx"], check=True, capture_output=True)
    return [row for row in load_workbook("calc/roundtrip.xlsx", data_only=True)["Orders"].iter_rows(values_only=True)]

for row in recalculated("orders_table.xlsx"):
    print(row)

The recalculated() helper is a testing trick, not something to ship: re-saving with openpyxl removes the cached zeros, which makes LibreOffice compute every formula on load, and the converted copy is then read back with its calculated values. The Net column shows #N/A and the totals #NAME?: LibreOffice does not resolve the shortened, table-less references. Writing the references in full — Orders[[#This Row],[Qty]] for the row formula and an explicit =SUBTOTAL(109,Orders[Net]) as a custom total — gives formulas that Excel and LibreOffice both calculate:

columns[1]["total_function"] = "=SUBTOTAL(109,Orders[Qty])"
columns[4]["total_function"] = "=SUBTOTAL(109,Orders[Net])"
columns[4]["formula"] = "=Orders[[#This Row],[Qty]]*Orders[[#This Row],[Price]]*(1-Orders[[#This Row],[Discount]])"
wb = xlsxwriter.Workbook("orders_table.xlsx")
ws = wb.add_worksheet("Orders")
for c in columns:                                        # formats belong to this workbook
    if "format" in c:
        c["format"] = wb.add_format({"num_format": "#,##0.00" if c["header"] != "Discount" else "0%"})
ws.add_table(0, 0, last_row, len(columns) - 1, {"name": "Orders", "data": orders.values.tolist(),
             "columns": columns, "total_row": True, "style": "Table Style Medium 2"})
wb.close()

rows = recalculated("orders_table.xlsx")
for row in rows:
    print(row)
expected = (orders["Qty"] * orders["Price"] * (1 - orders["Discount"])).round(2)
print("Net matches pandas:", [round(r[4], 2) for r in rows[1:-1]] == expected.tolist(), "| total", round(rows[-1][4], 2), "vs", round(expected.sum(), 2))

Every Net value now calculates and matches the same calculation in pandas, and the totals row sums both columns. A string starting with = as total_function is written as a custom total formula. Excel treats the full references exactly like the short ones — it may even display them in the short form — so nothing is lost for Excel users, and the workbook also works for anyone opening it in LibreOffice or reading it with tools that evaluate formulas. Table references in openpyxl-built workbooks follow the same rules, as covered in Use Structured Table References in Formulas from Python.

Letting people extend the table

The point of writing a table rather than a plain range is what happens after the file leaves Python. In Excel, typing a new product in the row directly below the last data row — above the totals row, which moves down — extends the table automatically: the new row takes the table's banding and column formats, the Net column fills in from the stored calculated-column formula, and the totals include the new row. Formulas elsewhere that refer to Orders[Net] include it too, with no ranges to edit. That makes tables the right structure for any generated workbook people are expected to add to — order logs, budget lines, issue trackers. Data validation on input columns, added with ws.data_validation() over the table's data rows, keeps those additions clean; new rows in a table inherit validation from the rows above.

Common pitfalls

• A range one row short. Include the header and totals rows in the add_table() range.
• Headers from the DataFrame. Table headers come from the columns list, not the data.
• Expecting cached results. XlsxWriter stores 0; Excel recalculates, other readers do not.
• Shorthand references outside Excel. Use Table[[#This Row],[Column]] for portability.
• Reusing formats across workbooks. Formats belong to the workbook that created them.

Conclusion

To write a DataFrame as an Excel table with XlsxWriter, pass its values to add_table() with a range covering header, data and totals rows, and describe each column — header, format, total function, and a formula for calculated columns. Write structured references with the table name, Orders[[#This Row],[Qty]], and custom totals such as =SUBTOTAL(109,Orders[Net]), so the formulas calculate in every spreadsheet application, and check them by recalculating the file and comparing with pandas.

Frequently asked questions

How do I write a pandas DataFrame as an Excel table with XlsxWriter? Call worksheet.add_table with a range that covers the header and data rows, pass df.values.tolist() as data and a columns list with a header for each column.

How do I add a calculated column to an XlsxWriter table? Give the column definition a formula using structured references, for example =@Qty*@Price or the fully qualified Orders[[#This Row],Qty]*Orders[[#This Row],Price].

How do I add a totals row to an XlsxWriter table? Set total_row to True, extend the range by one row, and give columns a total_function such as sum or a custom formula starting with =.

Why do my XlsxWriter table formulas show errors in LibreOffice? XlsxWriter saves @Column as a reference without the table name, which LibreOffice does not resolve; write the reference with the table name instead.

Related

• Up one level: Building Excel Reports with xlsxwriter — reports with XlsxWriter.
• Create an Excel Table with Python — tables with openpyxl.
• Add a Total Row to an Excel Table — totals rows in detail.
• Add Calculated Columns to an Excel Table with Python — adding them to existing tables.