Append Rows to an Excel Table and Resize It with openpyxl

An Excel table — the kind created with Insert → Table, with a name, banded rows and filter buttons — is the best thing to point formulas, pivot tables and charts at, because they refer to it by name and follow it as it grows. When a person types a row directly below a table, Excel extends it automatically. When Python writes the same row, nothing extends it: the new row sits outside the table, unformatted, missing from every pivot and total. openpyxl can make the table grow properly, but it has to be told each part. This page, part of Creating Excel Tables and Autofilters with Python, appends rows to a table with a calculated column, a totals row and a dropdown.

Prerequisites

pip install openpyxl

orders.xlsx has a table named Orders with three orders, a calculated Revenue column, a totals row that sums Qty and Revenue, and a dropdown of regions on the Region column.

Step 1 — Find the table and its data rows

Tables live in ws.tables, keyed by name. The table's ref is its whole range, header and totals included; range_boundaries turns it into numbers, and totalsRowCount says how many rows at the bottom are totals rather than data:

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

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]
table = ws.tables["Orders"]
min_col, min_row, max_col, max_row = range_boundaries(table.ref)
totals = table.totalsRowCount or 0
last_data = max_row - totals
print(table.ref, "| filter", table.autoFilter.ref, "| data rows", min_row + 1, "to", last_data, "| totals rows:", totals)
print([c.name for c in table.tableColumns])
print("Revenue formula:", ws.cell(row=2, column=5).value)

The table covers A1:E5: the header in row 1, data in rows 2 to 4, and the totals row in row 5. The filter range stops at row 4, because the totals row is not filtered. The Revenue column holds a formula in the file-format syntax, Orders[[#This Row],[Qty]], which Excel displays as [@Qty].

Step 2 — Write an append helper

The helper does what Excel does when someone types below a table: saves the totals row, writes the new records where it was, fills calculated columns with the formula from the last data row, copies number formats and fonts down, puts the totals row back underneath, and extends the table and filter ranges:

from copy import copy

def append_to_table(ws, name, records):
    table = ws.tables[name]
    min_col, min_row, max_col, max_row = range_boundaries(table.ref)
    totals = table.totalsRowCount or 0
    last_data = max_row - totals
    template = [ws.cell(row=last_data, column=c) for c in range(min_col, max_col + 1)]
    saved_totals = [[(ws.cell(row=r, column=c).value, copy(ws.cell(row=r, column=c)._style))
                     for c in range(min_col, max_col + 1)] for r in range(last_data + 1, max_row + 1)]

    row = last_data
    for record in records:
        row += 1
        values = iter(record)
        for src in template:
            cell = ws.cell(row=row, column=src.column)
            is_calculated = isinstance(src.value, str) and "[#This Row]" in src.value
            cell.value = src.value if is_calculated else next(values)
            cell._style = copy(src._style)

    for offset, saved in enumerate(saved_totals, start=1):
        for c, (value, style) in zip(range(min_col, max_col + 1), saved):
            cell = ws.cell(row=row + offset, column=c)
            cell.value, cell._style = value, style

    last_col = get_column_letter(max_col)
    table.ref = f"{get_column_letter(min_col)}{min_row}:{last_col}{row + totals}"
    if table.autoFilter is not None:
        table.autoFilter.ref = f"{get_column_letter(min_col)}{min_row}:{last_col}{row}"
    return row

new_orders = [("SO-1004", "West", 2, 30.0), ("SO-1005", "North", 10, 12.5)]
last = append_to_table(ws, "Orders", new_orders)
print(table.ref, "| filter", table.autoFilter.ref)
for r in ws.iter_rows(min_row=2, max_row=last + 1, values_only=True):
    print(r)

The new records supply values only for the ordinary columns; the calculated Revenue column is recognised by its [#This Row] formula and filled automatically. Copying the private _style attribute copies the cell's font, fill, border, alignment and number format in one step. The table now covers A1:E7, the filter A1:E6, and the totals row has moved to row 7. Its SUBTOTAL(109, Orders[Revenue]) formulas need no change: they refer to the table's column by name, and the column now includes the new rows.

Step 3 — Extend dropdowns and save

Data validation is stored separately from the table, as a list of cell ranges, so a dropdown that covered B2:B4 does not cover the new rows. Extend every rule that touches the table's columns down to the new last data row:

from openpyxl.worksheet.cell_range import CellRange

def extend_validations(ws, first_data_row, last_data_row, min_col, max_col):
    for dv in ws.data_validations.dataValidation:
        for rng in list(dv.sqref.ranges):
            if rng.min_row <= first_data_row and min_col <= rng.min_col <= max_col:
                dv.sqref.add(CellRange(min_col=rng.min_col, max_col=rng.max_col, min_row=rng.max_row + 1, max_row=last_data_row).coord)
    return [str(dv.sqref) for dv in ws.data_validations.dataValidation]

print(extend_validations(ws, 2, last, 1, 5))
wb.save("orders_appended.xlsx")
check = load_workbook("orders_appended.xlsx")["Orders"]
print(check.tables["Orders"].ref, check["E7"].value, check["D6"].number_format)

The Region dropdown now covers rows 2 to 6. Conditional formatting behaves the same way: rules are attached to fixed ranges, so a rule on E2:E4 must be re-added for the longer range. When a sheet will grow regularly, create validations and formats over a generous range, such as B2:B1000, from the start.

Step 4 — Check the totals in a spreadsheet

Formulas written by openpyxl have no stored results until a spreadsheet application recalculates them. Open the saved file — or convert it with LibreOffice in headless mode — and confirm the totals include the new rows:

expected_qty = 5 + 3 + 7 + 2 + 10
expected_revenue = 5 * 12.5 + 3 * 8.0 + 7 * 4.25 + 2 * 30.0 + 10 * 12.5
print("expected totals: Qty", expected_qty, "| Revenue", expected_revenue)

Opened in a spreadsheet, the totals row shows 27 and 301.25, and each new Revenue cell shows its own product — 60.00 and 125.00. A formula that refers to the table by name elsewhere in the workbook, such as =SUM(Orders[Revenue]), includes the new rows too, and so does a pivot table based on Orders once it is refreshed.

Why not the short form @Qty?

The @ form is what Excel shows in the formula bar, but it is not what it saves. In the file, a this-row reference is always written Orders[[#This Row],[Qty]]. Written from Python in the short form, the formula is not understood when the file is opened: LibreOffice shows Err:507, and Excel may offer to repair the workbook. Copying the formula from the last existing row, as the helper does, sidesteps the question — whatever syntax the file already uses is reused.

Common pitfalls

• Writing below the table without changing ref. The rows sit outside the table and out of every pivot.
• Forgetting autoFilter.ref. The filter must cover the new rows and stop above the totals row.
• Overwriting the totals row. Save it, write the data, then write it back underneath.
• The [@Column] shorthand. Use Table[[#This Row],[Column]] in formulas written from Python.
• Fixed-range validations and formats. Extend them, or create them over a larger range to begin with.
• Anything below the table. Appending overwrites cells under the table; keep the space below a growing table empty.

Performance notes

The helper touches only the new rows and the totals row, so appending is fast even to a large table. For thousands of rows, append them in one call rather than one at a time, and keep the workbook open for the whole batch; loading and saving are the slow steps.

Conclusion

Find the table in ws.tables, work out its data rows from ref and totalsRowCount, and write the new records directly below the last data row, copying calculated-column formulas and cell styles from the row above. Put the totals row back underneath, set table.ref and table.autoFilter.ref to the new ranges, and extend data validation and conditional formatting that should cover the new rows. Use the [#This Row] form for structured references written from Python.

Frequently asked questions

How do I add rows to an existing Excel table with openpyxl? Find the table in ws.tables, write the new rows directly below its last data row, then update table.ref — and table.autoFilter.ref — to cover the new rows. Writing below the table without changing ref leaves the rows outside it.

Why does my calculated column formula show an error after openpyxl writes it? The short form @Qty is how Excel displays a this-row reference, not how it stores it. In the file it must be written as Orders[[#This Row],Qty]; the short form written by Python produces errors or a repair prompt.

How do I append rows to a table that has a totals row? The totals row must stay last. Read its cells, write the new data starting where the totals row was, then write the totals row again below the new data and extend table.ref. The autoFilter range ends at the last data row, above the totals.

Will a pivot table or chart based on the table include the new rows? Yes, if it refers to the table by name, because the name now covers the larger range. Pivot tables still need refreshing in Excel to show the new data.

Related

• Up one level: Creating Excel Tables and Autofilters with Python — tables from Python.
• Create an Excel Table with Python — making the table in the first place.
• Add a Total Row to an Excel Table — the totals row in detail.
• Read an Excel Table by Name into pandas — reading the grown table back.