Add Calculated Columns to an Excel Table with Python

Workbooks that arrive from other people or systems often already contain an Excel table — an order log, a price list, an export someone formatted as a table. Adding a column of figures derived from the others is a frequent job: a margin from price and cost, an age from a date, a flag from a status. Writing values into the cells next to the table looks fine until someone adds a row in Excel and the new row has no value, or filters the table and the extra column is not part of it. The proper result is a calculated column: part of the table, with a formula Excel fills into every row — including rows typed in later. openpyxl can do that, but it does not do it for you: the table's range, its autofilter, its column list and the formulas in the cells all have to be updated together. This page does each step on an existing table, adds a total, and checks the formulas by recalculating. It belongs to Creating Excel Tables and Autofilters with Python.

Prerequisites

pip install openpyxl pandas

The starting point is products.xlsx, a workbook with a table named Products covering A1:E6 — SKU, product, price, unit cost and units sold for five products — and a note someone typed in G2. LibreOffice (soffice) recalculates the result in the last step.

Step 1 — Find the table and check there is room

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter, range_boundaries

wb = load_workbook("products.xlsx")
ws = wb["Products"]
table = ws.tables["Products"]
min_col, min_row, max_col, max_row = range_boundaries(table.ref)
new_col = max_col + 1
letter = get_column_letter(new_col)
occupied = [ws.cell(r, new_col).coordinate for r in range(min_row, max_row + 1) if ws.cell(r, new_col).value is not None]
print("table", table.displayName, table.ref, "| new column", letter, "| occupied cells:", occupied or "none")
print("existing columns:", [c.name for c in table.tableColumns])

ws.tables maps table names to their definitions; range_boundaries turns the reference into column and row numbers. The new column goes immediately to the right, in F. Checking that those cells are empty matters: a table cannot overlap other content, and silently writing over someone's notes is worse than stopping. Here column F is free — the note is in G — so the table can grow. A table's columns are also listed by name in tableColumns, and that list must match the header cells exactly, which is why the next step adds to both.

Step 2 — Extend the table definition

from openpyxl.worksheet.table import TableColumn, TableFormula

NAME = "Margin %"
FORMULA = "IFERROR((Products[[#This Row],[Price]]-Products[[#This Row],[Unit cost]])/Products[[#This Row],[Price]],0)"

ws.cell(min_row, new_col, NAME)
column = TableColumn(id=max(c.id for c in table.tableColumns) + 1, name=NAME)
column.calculatedColumnFormula = TableFormula(attr_text=FORMULA)
table.tableColumns.append(column)
table.ref = f"{get_column_letter(min_col)}{min_row}:{letter}{max_row}"
table.autoFilter.ref = table.ref
print(table.ref, [c.name for c in table.tableColumns])

Four things change together: the header cell, a new TableColumn with the next unused id and the same name as the header, its calculatedColumnFormula, and the table's ref and autoFilter.ref, which both widen to column F. Leaving any of them out produces a file Excel offers to repair on opening — usually by deleting the table. The calculated-column formula is what tells Excel this column is calculated: when someone adds a row to the table in Excel, Excel copies this formula into it. It is written without the leading =, as Excel stores it.

Step 3 — Write the formula into every row

The column definition is a template for future rows; the existing rows still need the formula in their cells:

for r in range(min_row + 1, max_row + 1):
    cell = ws.cell(r, new_col, "=" + FORMULA)
    cell.number_format = "0.0%"
ws.column_dimensions[letter].width = 11
wb.save("products.xlsx")
print(ws[f"{letter}2"].value[:60] + "…")

The formula uses fully qualified structured references — Products[[#This Row],[Price]] — rather than the [@Price] shorthand. Excel accepts both and may display the short form, but the full form works in every application that reads the file, as the recalculation below shows; Write an Excel Table with Formula Columns Using XlsxWriter compares the two in detail. IFERROR guards against a zero price, so a free sample does not fill the column with #DIV/0!. Column names that contain spaces or special characters are fine inside the brackets; names with [, ], # or ' need escaping with an apostrophe, so it is simplest to avoid them in headers.

Step 4 — A second column, a total, and a recalculation check

Wrap the steps in a function so further columns are one call each, add a revenue-weighted margin and check everything by recalculating the workbook:

def add_calculated_column(ws, table_name, name, formula, number_format):
    table = ws.tables[table_name]
    c1, r1, c2, r2 = range_boundaries(table.ref)
    col = c2 + 1
    if any(ws.cell(r, col).value is not None for r in range(r1, r2 + 1)):
        raise ValueError(f"column {get_column_letter(col)} is not empty")
    ws.cell(r1, col, name)
    tc = TableColumn(id=max(c.id for c in table.tableColumns) + 1, name=name)
    tc.calculatedColumnFormula = TableFormula(attr_text=formula)
    table.tableColumns.append(tc)
    table.ref = table.autoFilter.ref = f"{get_column_letter(c1)}{r1}:{get_column_letter(col)}{r2}"
    for r in range(r1 + 1, r2 + 1):
        ws.cell(r, col, "=" + formula).number_format = number_format

wb = load_workbook("products.xlsx")
ws = wb["Products"]
try:
    add_calculated_column(ws, "Products", "Gross profit",
                          "(Products[[#This Row],[Price]]-Products[[#This Row],[Unit cost]])*Products[[#This Row],[Units sold]]", "#,##0.00")
except ValueError as e:
    print("refused:", e)
ws["A9"], ws["B9"] = "Total gross profit", "=SUM(Products[Gross profit])"
wb.save("products.xlsx")

The function refuses to add the second column: column G is not empty, because of the note in G2. That is the safety check doing its job — the note has to be moved before the table can grow further. Move it and try again:

import subprocess
import pandas as pd

ws["I2"], ws["G2"] = ws["G2"].value, None
add_calculated_column(ws, "Products", "Gross profit",
                      "(Products[[#This Row],[Price]]-Products[[#This Row],[Unit cost]])*Products[[#This Row],[Units sold]]", "#,##0.00")
wb.save("products.xlsx")

load_workbook("products.xlsx").save("roundtrip.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "roundtrip.xlsx"], check=True, capture_output=True)
calc = load_workbook("calc/roundtrip.xlsx", data_only=True)["Products"]
rows = [[c.value for c in r[:7]] for r in calc.iter_rows(min_row=1, max_row=6)]
df = pd.DataFrame(rows[1:], columns=rows[0])
expected = ((df["Price"] - df["Unit cost"]) * df["Units sold"]).round(2)
print(df[["SKU", "Margin %", "Gross profit"]].round(3).to_string(index=False))
print("gross profit matches:", (df["Gross profit"].round(2) == expected).all(), "| total:", calc["B9"].value, "| table now", ws.tables["Products"].ref)

After the note moves to I2, the table grows to A1:G6. The margins range from 34% on brackets to 60% on washers, and the gross profit column — price less cost, times units — totals 7,415, with bolts contributing the most at 2,880 despite their middling margin, because they sell in volume. Moving content out of the way is a decision for the code's author, not something to automate silently. Recalculating with LibreOffice gives margins and gross profits that match the same calculation in pandas, with the total below the table summing the new column by name. Rows added to the table later in Excel will get both formulas automatically, because each column carries its calculated-column formula.

The results quoted here come from the recalculated copy. The workbook openpyxl saved holds formulas without results, so reading it straight back with pandas, or with openpyxl's data_only=True, gives empty cells in the new columns until the file has been opened and saved in Excel or recalculated as above. If a later step of the same job needs the margins as numbers, compute them in pandas from the same columns rather than reading the formulas back; Read an Excel Table by Name into pandas shows how to load the table itself.

Common pitfalls

• Writing values beside the table. They are not part of it; new rows get nothing.
• Forgetting autoFilter.ref. Widen it with ref, or Excel repairs the file.
• Mismatched names. The TableColumn name must equal the header text.
• Overwriting neighbouring cells. Check the new column is empty first.
• Shorthand references. Use Table[[#This Row],[Column]] for files read outside Excel.

Conclusion

To add a calculated column to an existing table with openpyxl, check the column to its right is empty, write the header, append a TableColumn with the same name, the next id and a calculatedColumnFormula, widen both table.ref and table.autoFilter.ref, and write the formula — with fully qualified structured references — into every data row. Wrap it in a function, refuse to overwrite other content, and recalculate to check the results.

Frequently asked questions

How do I add a column to an existing Excel table with openpyxl? Write the header next to the table, append a TableColumn with that name to table.tableColumns, and extend table.ref and table.autoFilter.ref to include the new column.

How do I make Excel fill a formula into new table rows? Set the column's calculatedColumnFormula to the formula without the leading equals sign; Excel copies it into rows added to the table.

Why does Excel repair my workbook after I changed a table with openpyxl? The table's ref, autoFilter ref, column list and header cells must agree; a column in the header but missing from tableColumns, or a ref that was not widened, triggers a repair.

Should I use @Column or Table[[#This Row],Column] in openpyxl formulas? The fully qualified form works in Excel and in other applications such as LibreOffice, so it is the safer choice for generated files.

Related

• Up one level: Creating Excel Tables and Autofilters with Python — tables from Python.
• Create an Excel Table with Python — creating the table.
• Append Rows to an Excel Table and Resize It — growing it downwards.
• Add a Total Row to an Excel Table — totals inside the table.