Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Add a Total Row to an Excel Table with Python

Give an Excel table a real totals row from Python — the one readers can switch between Sum, Average and Count in Excel — with openpyxl's totalsRowCount and table column settings, SUBTOTAL formulas that respect filters, custom totals, the matching XlsxWriter options and how to keep the totals row out of pandas.
Tables, filters & outlinesFormulas in workbooksopenpyxl

Excel tables can show a totals row: a last row under the data, styled with the table, where each column has a dropdown to pick Sum, Average, Count, Min, Max and so on. It is better than a hand-written SUM under the data because it is part of the table — it moves when rows are added, respects filters and keeps its styling. Adding one from Python takes three pieces that must agree: the table's range, the column definitions and the formulas in the cells. This guide, part of Creating Excel Tables and Autofilters with Python, puts them together with openpyxl and XlsxWriter. Creating the table itself is covered in Create an Excel Table with Python.

A sales table with its totals row A table named Sales has Region, Orders and Amount columns and four data rows. Its totals row reads Total, then 40 orders from SUBTOTAL 109 of the Orders column and 4,360.75 from SUBTOTAL 109 of the Amount column. fx =SUBTOTAL(109,Sales[Amount]) A B C 1 Region Orders Amount 2 North 12 1,200.50 3 South 8 950.00 4 East 15 1,780.25 5 West 5 430.00 6 Total 40 4,360.75 the totals row is part of the table, not a formula under it

Prerequisites

Bash
pip install openpyxl xlsxwriter pandas

Step 1 — Leave room for the totals row in the range

A table's ref covers the header, the data and the totals row. With four data rows under a header in row 1, the data ends in row 5 and the totals row is row 6:

Python
from openpyxl import Workbook
from openpyxl.worksheet.filters import AutoFilter
from openpyxl.worksheet.table import Table, TableColumn, TableStyleInfo

wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Region", "Orders", "Amount"])
for row in [("North", 12, 1200.5), ("South", 8, 950), ("East", 15, 1780.25), ("West", 5, 430)]:
    ws.append(row)
last_data = ws.max_row                   # 5
totals_row = last_data + 1               # 6

The filter buttons, though, belong to the header and data only. Excel sets the table's autofilter range to exclude the totals row, and doing the same avoids a table whose filter includes its own total.

Step 2 — Define the columns with their totals

Each TableColumn needs the exact header text as its name — a mismatch makes Excel repair the file — plus either a label or a function for the totals row. openpyxl can generate columns automatically, but at the point you configure them it only knows placeholder names like "Column1", so build them from the header cells:

Python
headers = [cell.value for cell in ws[1]]
columns = [TableColumn(id=i, name=name) for i, name in enumerate(headers, start=1)]
columns[0].totalsRowLabel = "Total"
columns[1].totalsRowFunction = "sum"
columns[2].totalsRowFunction = "sum"

table = Table(displayName="Sales", ref=f"A1:C{totals_row}", totalsRowCount=1,
              tableColumns=columns, autoFilter=AutoFilter(ref=f"A1:C{last_data}"))
table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True)

totalsRowFunction accepts sum, average, count, countNums, max, min, stdDev, var and custom. It is what Excel's dropdown shows as selected; the reader can switch it to another function in Excel afterwards.

Step 3 — Write the formulas into the totals row

openpyxl records the function but does not write the formula — the cells would stay empty until someone reselects the function in Excel. Write what Excel itself would write: SUBTOTAL with function number 109 for a sum, using structured references to the table's columns:

Python
ws[f"A{totals_row}"] = "Total"
ws[f"B{totals_row}"] = "=SUBTOTAL(109,Sales[Orders])"
ws[f"C{totals_row}"] = "=SUBTOTAL(109,Sales[Amount])"
ws.add_table(table)
wb.save("sales_table.xlsx")
print(table.ref, table.totalsRowCount)          # A1:C6 1

Recalculating the saved file in LibreOffice gives 40 orders and 4,360.75. The 100-series function numbers ignore rows hidden by a filter, so when a reader filters to North and South, the totals show 20 and 2,150.50 — the total of what they are looking at. Structured references such as Sales[Amount] keep working when rows are added inside the table; they are covered in Use Structured Table References in Formulas from Python.

totalsRowFunction and the formula to write For sum write SUBTOTAL 109, for average SUBTOTAL 101, for count of non-empty cells SUBTOTAL 103, for count of numbers SUBTOTAL 102, for max SUBTOTAL 104 and for min SUBTOTAL 105, each with the table column as a structured reference. A custom function stores its own formula in totalsRowFormula. Formula in the cell sum =SUBTOTAL(109,Sales[Amount]) average =SUBTOTAL(101,Sales[Amount]) count =SUBTOTAL(103,Sales[Region]) countNums =SUBTOTAL(102,Sales[Orders]) max / min =SUBTOTAL(104 / 105,…) custom totalsRowFormula + same formula

Step 4 — A custom total

Some totals are not a single function of one column — the average order value, for example, is total amount divided by total orders. Use custom, store the formula on the column, and write the same formula in the cell:

Python
from openpyxl.worksheet.table import TableFormula

wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Region", "Orders", "Amount", "Per order"])
for i, (region, n, amount) in enumerate([("North", 12, 1200.5), ("South", 8, 950),
                                          ("East", 15, 1780.25), ("West", 5, 430)], start=2):
    ws.append([region, n, amount, f"=C{i}/B{i}"])
cols = [TableColumn(id=i, name=h) for i, h in enumerate(["Region", "Orders", "Amount", "Per order"], 1)]
cols[0].totalsRowLabel = "Total"
cols[1].totalsRowFunction = cols[2].totalsRowFunction = "sum"
custom = "SUBTOTAL(109,Sales[Amount])/SUBTOTAL(109,Sales[Orders])"
cols[3].totalsRowFunction = "custom"
cols[3].totalsRowFormula = TableFormula(attr_text=custom)
ws.add_table(Table(displayName="Sales", ref="A1:D6", totalsRowCount=1, tableColumns=cols,
                   autoFilter=AutoFilter(ref="A1:D5")))
ws["A6"], ws["B6"], ws["C6"] = "Total", "=SUBTOTAL(109,Sales[Orders])", "=SUBTOTAL(109,Sales[Amount])"
ws["D6"] = "=" + custom
wb.save("sales_custom.xlsx")

The total per order is 4,360.75 ÷ 40 = 109.02 — a weighted average, which is what the row really means. Averaging the per-order column instead would give each region equal weight regardless of size, the mistake explained in Use Different Aggregations per Column in a pandas Pivot.

Step 5 — The same with XlsxWriter

XlsxWriter's add_table builds the totals row in one step, including the formulas, from a total_row flag and per-column settings:

Python
import xlsxwriter

book = xlsxwriter.Workbook("sales_xw.xlsx")
sheet = book.add_worksheet("Sales")
data = [["North", 12, 1200.5], ["South", 8, 950], ["East", 15, 1780.25], ["West", 5, 430]]
sheet.add_table("A1:C6", {
    "name": "Sales", "data": data, "total_row": True,
    "columns": [{"header": "Region", "total_string": "Total"},
                {"header": "Orders", "total_function": "sum", "total_value": 40},
                {"header": "Amount", "total_function": "sum", "total_value": 4360.75}],
})
book.close()
print("written")

The range again includes the totals row. total_value stores the result so readers that do not recalculate see the right figure, for the reasons covered in Store Formula Results with xlsxwriter So Every Viewer Shows Values.

openpyxl or XlsxWriter for a totals row openpyxl can add a totals row to a table in an existing workbook, but you must define the columns and write the SUBTOTAL formulas yourself. XlsxWriter writes the formulas automatically and can store their values, but only when creating a new file. openpyxl works on existing workbooks write SUBTOTAL formulas yourself no cached values stored define TableColumns by hand XlsxWriter formulas written for you total_value caches results new files only simplest for generated reports

Step 6 — Keep the totals row out of pandas

pd.read_excel has no idea the last row is a total. Read only the data rows by counting the table's rows:

Python
import pandas as pd
from openpyxl import load_workbook

tbl = load_workbook("sales_table.xlsx")["Sales"].tables["Sales"]
first, last = tbl.ref.split(":")
data_rows = int(last[1:]) - int(first[1:]) - (tbl.totalsRowCount or 0)
df = pd.read_excel("sales_table.xlsx", sheet_name="Sales", nrows=data_rows)
print(len(df), df["Amount"].sum())               # 4 4360.75

Without the adjustment the frame has a fifth row whose Amount doubles the sum. A reusable reader that handles headers, totals and table names is in Read an Excel Table by Name into pandas.

Step 7 — Add rows to a table that has totals

Appending a row with ws.append puts it below the totals row, outside the table. To add data to an existing table, write the new rows where the totals row is, move the totals row down, and extend both ranges:

Python
wb = load_workbook("sales_table.xlsx")
ws = wb["Sales"]
tbl = ws.tables["Sales"]
old_total = int(tbl.ref.split(":")[1][1:])                         # 6
new_rows = [("Central", 9, 870.0), ("Islands", 3, 215.5)]
for offset, row in enumerate(new_rows):
    for col, value in enumerate(row, start=1):
        ws.cell(old_total + offset, col, value)
new_total = old_total + len(new_rows)
ws[f"A{new_total}"] = "Total"
ws[f"B{new_total}"] = "=SUBTOTAL(109,Sales[Orders])"
ws[f"C{new_total}"] = "=SUBTOTAL(109,Sales[Amount])"
tbl.ref = f"A1:C{new_total}"
tbl.autoFilter.ref = f"A1:C{new_total - 1}"
wb.save("sales_table.xlsx")
print(tbl.ref, tbl.autoFilter.ref)             # A1:C8 A1:C7

The structured references in the totals formulas need no change: Sales[Amount] now covers the longer column automatically. What openpyxl will not do is update the ranges for you — insert_rows shifts cells but leaves the table's ref where it was — so always set both ranges after changing the data.

Common pitfalls

  • ref without the extra row. If the range ends at the last data row, the "totals row" overwrites your data's last line in Excel's view.
  • Column names that differ from the header. "Amount " with a space in the cell and "Amount" in the column definition make Excel repair the table.
  • Two tables with the same name. displayName must be unique in the workbook; when writing one table per sheet, derive the name from the sheet.
  • SUM instead of SUBTOTAL. A plain SUM ignores filters and double-counts if another total sits inside the range.

Conclusion

A table totals row needs three things that agree: a ref one row longer than the data with totalsRowCount=1 and an autofilter that stops above it, TableColumn definitions named exactly like the headers with totalsRowFunction or totalsRowLabel, and SUBTOTAL(109, Table[Column]) formulas written into the cells — or custom with a matching totalsRowFormula. XlsxWriter does the formulas for you in new files. When reading the table back, drop the totals row before summing.

Frequently asked questions

How do I add a total row to an Excel table with openpyxl? Create the Table with a ref that includes one extra row and totalsRowCount=1, define each TableColumn with totalsRowFunction (such as "sum") or totalsRowLabel, and write the matching SUBTOTAL formulas into the totals row cells yourself.

Why use SUBTOTAL(109, …) instead of SUM? SUBTOTAL with function number 109 ignores rows hidden by a filter, so the total follows what the reader has filtered. It is also what Excel itself writes into a table's totals row.

Do I have to write the formulas if I set totalsRowFunction? With openpyxl, yes. totalsRowFunction tells Excel which function the totals row uses, but openpyxl does not write the cell formula, so the cell would stay empty until someone re-selects the function in Excel.

How do I stop pandas from reading the total row as data? Read the table's range and drop the last totalsRowCount rows, or use nrows when reading with pandas. A helper that reads a table by name and drops its totals row keeps this in one place.

More on tables, filters & outlines

More on tables, filters & outlines →