Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Add a Native Excel Pivot Table with Python

Ship a real, refreshable Excel pivot table rather than a static grid — write the source as a table, drive Excel through xlwings on Windows, and the cross-platform alternatives.

pandas.pivot_table produces a grid of numbers. A native Excel pivot table is something different: a live object the reader can pivot, filter, drill into and refresh. Producing one from Python is harder than it should be, because the pivot definition and its cache are among the few Excel structures openpyxl cannot build from scratch. This guide covers the three routes that actually work — a template with the pivot already in it, driving Excel through xlwings, and the portable static fallback — and how to choose between them. It extends Creating Pivot Tables from Excel Data.

Three routes to putting a pivot in front of a reader Three approaches compared on portability and interactivity. A template workbook that already contains the pivot works anywhere, because the script only writes source rows and flags the cache to refresh on open — the reader gets a live pivot. Driving Excel with xlwings builds the pivot programmatically and is fully flexible, but requires Windows with Excel installed. A pandas pivot table written as a formatted grid runs anywhere including a Linux container, but the result is static values the reader cannot re-pivot. portability and interactivity pull in opposite directions template + refresh the pivot already exists the script writes rows only runs anywhere stays interactive needs the template built once xlwings drives Excel builds the pivot in code full control of layout Windows + Excel only stays interactive no good on a server static pandas grid pivot_table, then format plus the raw data sheet runs anywhere values only, not live the honest default

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter
pip install xlwings          # only for the Windows route

Some source data to pivot:

Python
import pandas as pd

sales = pd.DataFrame({
    "date": pd.to_datetime(
        ["2026-06-03", "2026-06-19", "2026-07-02", "2026-07-22",
         "2026-08-05", "2026-08-15"] * 2
    ),
    "region": ["North", "South", "West", "North", "South", "West"] * 2,
    "product": ["Widget"] * 6 + ["Gadget"] * 6,
    "revenue": [5150.00, 4268.50, 3511.25, 2980.10, 3140.75, 1820.00,
                4210.00, 3980.25, 2711.50, 3320.80, 2905.60, 1615.40],
})

Step 1 — Write the source as a named table

Whichever route you take, the source should be a named Excel table rather than a plain range. A table's reference expands automatically as rows are added, so a pivot pointed at it never needs its source updating:

Python
import pandas as pd

def write_source_table(df, path, sheet_name="Data", table_name="SalesData"):
    """Write the source rows as a named Excel table, ready for a pivot."""
    with pd.ExcelWriter(path, engine="xlsxwriter",
                        datetime_format="yyyy-mm-dd") as writer:
        df.to_excel(writer, sheet_name=sheet_name, index=False, startrow=0)
        sheet = writer.sheets[sheet_name]

        sheet.add_table(
            0, 0, len(df), len(df.columns) - 1,
            {
                "name": table_name,
                "columns": [{"header": str(c)} for c in df.columns],
                "style": "Table Style Medium 2",
            },
        )
        sheet.set_column("A:A", 13)
        sheet.set_column("B:C", 14)
        sheet.set_column("D:D", 14,
                         writer.book.add_format({"num_format": "#,##0.00"}))
    return path

write_source_table(sales, "sales.xlsx")

Two things this buys you beyond the pivot. Readers get filter dropdowns for free, and any formula elsewhere can reference SalesData[revenue] rather than a range that goes stale. The table mechanics are covered in creating an Excel table with Python.

Step 2 — The template route

What the script touches, and what the template keeps A template workbook holds a Data sheet with a named table and a Pivot sheet with a pivot pointing at it. Each run the script does exactly three things: it clears and rewrites the data rows, it extends the table reference so the new last row is included, and it sets the pivot cache's refreshOnLoad flag. The pivot definition, its layout and its formatting are never touched, and Excel rebuilds the cache the moment a reader opens the file. the template Data · named table SalesData Pivot · built once, by hand layout and formatting never touched 1 · clear and rewrite the data rows delete_rows then append — the header stays 2 · grow the table reference so the pivot's source covers every new row 3 · cache.refreshOnLoad = True Excel rebuilds the cache when the reader opens it

This is the approach that works on a server and gives readers a live pivot. Build the pivot once, by hand, in a template workbook: a Data sheet with the named table, and a Pivot sheet with a pivot table pointed at it. Then the script only ever writes rows and flags the cache.

Python
from openpyxl import load_workbook
import pandas as pd

def refresh_template(template, dest, df, sheet_name="Data"):
    """Fill a template's data sheet and mark its pivot caches to refresh."""
    wb = load_workbook(template)
    ws = wb[sheet_name]

    # Clear previous rows, keeping the header.
    if ws.max_row > 1:
        ws.delete_rows(2, ws.max_row - 1)

    for record in df.itertuples(index=False):
        ws.append(list(record))

    # Grow the table reference so the pivot sees every row.
    for table in ws.tables.values():
        first, _, last_col = table.ref.partition(":")
        column_letters = "".join(c for c in last_col if c.isalpha())
        table.ref = f"{first}:{column_letters}{len(df) + 1}"

    # Ask Excel to rebuild the pivot cache when the file opens.
    for pivot in wb._pivots:
        pivot.cache.refreshOnLoad = True

    wb.save(dest)
    return dest

refreshOnLoad is the key. openpyxl cannot rebuild the cache itself — it has no calculation engine — but it can set the flag that makes Excel do it the moment a reader opens the workbook. From the reader's point of view the pivot is simply current.

Two constraints to respect. Keep the template's structure intact: renaming the data sheet or the table breaks the pivot's source reference, and openpyxl will not warn you. And write into the existing sheet rather than replacing it — the fill-only discipline described in populating an Excel template without losing formatting applies exactly.

Step 3 — The xlwings route

On Windows with Excel installed, you can build the pivot programmatically through COM:

Python
import xlwings as xw

def build_pivot(path, dest, data_sheet="Data", pivot_sheet="Pivot"):
    """Create a real pivot table by driving Excel. Windows only."""
    app = xw.App(visible=False)
    try:
        book = app.books.open(path)
        source = book.sheets[data_sheet]
        used = source.used_range

        if pivot_sheet in [s.name for s in book.sheets]:
            book.sheets[pivot_sheet].delete()
        target = book.sheets.add(pivot_sheet, after=source)

        cache = book.api.PivotCaches().Create(
            SourceType=1,                       # xlDatabase
            SourceData=used.api,
        )
        table = cache.CreatePivotTable(
            TableDestination=target.range("A3").api,
            TableName="RevenuePivot",
        )

        table.PivotFields("region").Orientation = 1      # xlRowField
        table.PivotFields("product").Orientation = 2     # xlColumnField
        revenue = table.PivotFields("revenue")
        revenue.Orientation = 4                          # xlDataField
        revenue.Function = -4157                         # xlSum
        revenue.NumberFormat = "#,##0.00"

        book.save(dest)
        return dest
    finally:
        app.quit()

The finally block is not optional. An unhandled exception without it leaves an invisible Excel process running and holding the file, and a scheduled job that fails a few times accumulates them until the machine runs out of memory. The wider xlwings model is in reading and writing a live Excel workbook with xlwings.

Those magic numbers are Excel's own enumeration constants — 1 for a row field, 2 for a column field, 4 for a data field, -4157 for sum. They are stable across versions, but naming them makes the code readable:

Python
ROW_FIELD, COLUMN_FIELD, DATA_FIELD = 1, 2, 4
SUM, COUNT, AVERAGE = -4157, -4112, -4106

Step 4 — The portable static route

When the job runs on Linux, or you simply do not want a dependency on Excel, produce a formatted static pivot alongside the raw data. The reader loses interactivity and gains a report that works everywhere:

Ship the summary and the source together A workbook with two sheets. The Summary sheet holds a formatted static pivot with regions down the side, products across the top, totals, and currency formatting — enough for most readers. The Data sheet holds the complete source rows as a named Excel table, so a reader who wants a live pivot can insert one themselves in two clicks. This combination is the portable answer that costs nothing on a server. sheet 1 · Summary region Gadget Widget North 7,530.80 8,130.10 Total · 41,614.15 enough for most readers sheet 2 · Data SalesData — a named Excel table every source row, filterable grows with the data a reader can insert their own pivot in two clicks
Python
import pandas as pd

def static_pivot_workbook(df, path):
    """A formatted summary plus the full source table — portable everywhere."""
    pivot = pd.pivot_table(
        df, index="region", columns="product", values="revenue",
        aggfunc="sum", margins=True, margins_name="Total",
    ).round(2)

    with pd.ExcelWriter(path, engine="xlsxwriter",
                        datetime_format="yyyy-mm-dd") as writer:
        pivot.to_excel(writer, sheet_name="Summary")
        df.to_excel(writer, sheet_name="Data", index=False)

        book = writer.book
        money = book.add_format({"num_format": "#,##0.00"})
        header = book.add_format({"bold": True, "bg_color": "#EEF2FF",
                                  "border": 1})
        total = book.add_format({"bold": True, "num_format": "#,##0.00",
                                 "top": 1})

        summary = writer.sheets["Summary"]
        summary.set_column("A:A", 16)
        summary.set_column(1, len(pivot.columns), 15, money)
        summary.set_row(0, None, header)
        summary.set_row(len(pivot), None, total)
        summary.freeze_panes(1, 1)

        data = writer.sheets["Data"]
        data.add_table(0, 0, len(df), len(df.columns) - 1,
                       {"name": "SalesData",
                        "columns": [{"header": str(c)} for c in df.columns],
                        "style": "Table Style Medium 2"})
        data.set_column("A:C", 14)
        data.set_column("D:D", 14, money)

    return path

static_pivot_workbook(sales, "sales_summary.xlsx")

Shipping both sheets is the point. The summary answers the question most readers have, and the table lets the one reader who wants to slice it differently insert their own pivot without asking you for a new report. The pandas pivot mechanics are covered in creating a pivot table from Excel with pandas.

Common pitfalls and fixes

SymptomCauseFix
No way to create a pivot in openpyxlIt cannot build the cacheUse a template, xlwings, or a static pivot.
Pivot shows stale numbersCache not refreshedSet pivot.cache.refreshOnLoad = True.
Pivot misses the newest rowsSource is a fixed rangePoint it at a named table and grow the ref.
Excel processes accumulatexlwings App not quit on errorQuit in a finally block.
Pivot source reference brokenData sheet or table renamedKeep the template's structure fixed.
pandas pivot has a MultiIndex headerMultiple values or columnsFlatten before writing, or accept two header rows.
Works locally, fails in the containerxlwings needs Windows and ExcelUse the static or template route.

Performance and scale notes

The three routes have very different cost profiles, and the difference is not subtle.

The static route is pure pandas — one vectorised aggregation and one write, so a million source rows summarise in seconds. It is also the only route with no external process.

The template route costs one openpyxl load and save of the whole workbook. That is fine for tens of thousands of rows and slow beyond that, because openpyxl holds everything in memory. Where the source is genuinely large, write the data sheet with the streaming approach in writing large DataFrames with write-only mode — though note that streaming mode cannot carry an existing pivot, so the pivot has to live in a separate workbook that references the data one.

The xlwings route is the slowest by a wide margin, because every property assignment is a COM round trip. Two mitigations matter:

Python
import xlwings as xw

app = xw.App(visible=False)
app.screen_updating = False       # do not repaint after every change
app.display_alerts = False        # no modal dialogs to block a batch job
try:
    ...
finally:
    app.screen_updating = True
    app.quit()

And write the source data with pandas or xlsxwriter before opening Excel, rather than assigning cell values through COM — pushing a hundred thousand rows across the boundary one range at a time is orders of magnitude slower than writing the file and opening it.

The practical conclusion for most reporting pipelines: build the template once by hand, and let the scheduled job take the template route. It gives readers a live pivot, runs on a server, and costs a single workbook round trip.

Conclusion

openpyxl cannot create a pivot table from nothing, so the question is which of three routes fits your constraints. A template that already contains the pivot is usually the best answer: the script writes rows, grows the table reference, and sets refreshOnLoad so Excel rebuilds the cache when a reader opens the file — interactive for the reader, portable for the server. xlwings builds a pivot from scratch but ties the job to Windows with Excel. And where neither applies, ship a formatted static pivot alongside the full source as a named table, so anyone who wants to slice it differently can insert their own.

Frequently asked questions

Can openpyxl create a pivot table from scratch? No. openpyxl can read and preserve a pivot table that already exists in a workbook, and it can mark the cache to refresh on open, but it cannot construct a new pivot definition and its cache from nothing.

What is the difference between a native pivot and a pandas pivot? A native pivot stays interactive — the reader can drag fields, change the aggregation and refresh against new data. A pandas pivot is a static grid of values, which is fine for a printed report and useless to somebody who wants to explore.

Does the xlwings approach work on a server? No. It drives a real Excel instance through COM, so it needs Windows with Excel installed. On Linux or in a container you must use the template or static approaches instead.

How do I make a pivot refresh when the file opens? Set the pivot cache's refreshOnLoad flag. openpyxl can do this on an existing pivot, so a template carrying the pivot picks up new source rows the moment a reader opens the workbook.

Should the source data be a table or a plain range? A named Excel table. A table's reference grows automatically as rows are added, so the pivot's source never needs updating; a fixed range has to be rewritten every time the row count changes.