Guide

Getting Started With Python Excel AutomationDeep dive

openpyxl vs pandas for Excel Automation

pandas reads Excel through openpyxl, so they are not rivals. See where the line falls — rows versus cells — and how to hand off between them in one script.

openpyxl and pandas are the two libraries almost every Python Excel project installs, and the question of which to use is confused by the fact that pandas uses openpyxl underneath. They are not alternatives at the same level: pandas is a table library that happens to read and write spreadsheets, and openpyxl is a spreadsheet library that knows nothing about tables. This guide, part of Choosing a Python Excel Library, sets out the line between them and shows the handoff that gets both jobs done in one script.

Prerequisites

Bash

pip install pandas openpyxl

Every snippet below writes its own sample workbook first, so you can paste and run them in order.

The dividing line: rows versus cells

pandas thinks in columns and rows of values. openpyxl thinks in cells, each carrying a value, a number format, a font, a fill, a border and a comment. If what you need can be expressed as "filter these rows, group by that column, total this one" — pandas. If it can only be expressed as "make row 1 bold, freeze it, and set column B to two decimal places" — openpyxl.

Python

import pandas as pd

sales = pd.DataFrame({
    "Region": ["North", "South", "North", "West"],
    "Rep": ["Ana", "Ben", "Cara", "Dev"],
    "Revenue": [12400.0, 9800.5, 15320.25, 7010.0],
})
sales.to_excel("sales.xlsx", sheet_name="Raw", index=False)

# pandas: the shape of the data
summary = sales.groupby("Region", as_index=False)["Revenue"].sum()
print(summary)

That aggregation in openpyxl would mean iterating rows, accumulating into a dictionary and sorting the result by hand — perhaps twenty lines to pandas' one. The reverse is just as lopsided.

Python

from openpyxl import load_workbook
from openpyxl.styles import Font

book = load_workbook("sales.xlsx")
sheet = book["Raw"]

# openpyxl: the appearance of the file
for cell in sheet[1]:
    cell.font = Font(bold=True)
sheet.freeze_panes = "A2"
for cell in sheet["C"][1:]:
    cell.number_format = "#,##0.00"
sheet.column_dimensions["A"].width = 14
book.save("sales-styled.xlsx")

pandas has no vocabulary for any of that. to_excel writes values and nothing else.

Reading: the same parser, two different results

Both calls below read the same file with the same code underneath. The difference is what you get back: a typed table, or a grid of cells you can inspect individually.

Python

import pandas as pd
from openpyxl import load_workbook

frame = pd.read_excel("sales.xlsx", sheet_name="Raw")
print(frame.dtypes)                      # typed columns, ready to aggregate

book = load_workbook("sales.xlsx")
sheet = book["Raw"]
print(sheet["C2"].value, sheet["C2"].number_format, sheet.max_row)

pandas gives you dtypes, alignment and vectorised operations. openpyxl gives you the number format, the merged-cell ranges, the comment on C2 and the fact that the sheet has 5 rows — none of which survive the trip into a DataFrame. When a read has to answer a question about the file rather than about the data, openpyxl is the only one of the two that can.

Writing: values versus a finished document

The clean pattern is to let each library do its half in sequence. pandas produces the values; openpyxl — reached through pandas.ExcelWriter so nothing has to be reopened — finishes the sheet.

Python

import pandas as pd
from openpyxl.styles import Alignment, Font
from openpyxl.utils import get_column_letter

summary = sales.groupby("Region", as_index=False)["Revenue"].sum()

with pd.ExcelWriter("regional.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="By region", index=False)
    sheet = writer.sheets["By region"]
    for cell in sheet[1]:
        cell.font = Font(bold=True, color="FFFFFF")
        cell.alignment = Alignment(horizontal="center")
    for row in sheet.iter_rows(min_row=2, min_col=2, max_col=2):
        for cell in row:
            cell.number_format = "#,##0.00"
    for index, column in enumerate(summary.columns, start=1):
        width = max(len(str(column)), *(len(str(v)) for v in summary[column])) + 4
        sheet.column_dimensions[get_column_letter(index)].width = width

writer.sheets hands you the live openpyxl worksheet, so the styling happens before the file is saved rather than in a second pass. That single fact removes most of the reason to choose between the two libraries at all.

The one job only openpyxl can do

pandas cannot open an existing workbook and change part of it. to_excel writes a new sheet; even mode="a" on ExcelWriter appends a sheet rather than editing one, and it will not preserve formatting the way a real edit does. When the requirement is "the finance team's template, with this quarter's numbers in it", the answer is openpyxl every time.

Python

from openpyxl import load_workbook

book = load_workbook("template.xlsx")     # logo, headers and formulas already in place
sheet = book["Summary"]
sheet["C4"] = 128400.0
sheet["C5"] = 96150.5
book.save("summary-2026-Q3.xlsx")         # save under a new name, always

Fill an Excel Template with Python and openpyxl develops that pattern; the important part here is that no amount of pandas gets you to it.

Five real requirements, decided

Requirements rarely arrive as "should I use pandas or openpyxl", so it helps to translate a few of the common ones.

"Total revenue by region and email the result." pandas for the grouping, openpyxl only if the attached workbook needs to look designed. If a plain table will do, to_excel alone is enough.

"Update cell C4 in the board pack each month." openpyxl alone. Loading, assigning and saving is three lines, and pandas has no way to express it without rewriting the sheet.

"Turn a 400,000-row export into a summary." pandas for the aggregation, with the read pushed through a faster engine, and xlsxwriter for the output if it needs formatting. openpyxl's normal mode would be the slow path here.

"Find every cell with a red fill and report it." openpyxl alone — fills are a property of cells, and they are invisible to pandas. Highlight Invalid Cells in Excel with Python uses that same cell-level access in reverse.

"Join two workbooks on account number and flag mismatches." pandas, without hesitation. The equivalent in openpyxl means writing a join by hand, and Merge Two Excel Files on a Common Column in Python shows how little code it takes with frames.

The pattern in all five: the library follows the noun in the requirement. Rows, totals and joins are pandas nouns. Cells, fills, widths and templates are openpyxl nouns.

What each one costs to install

openpyxl is a pure-Python package with no heavy dependencies, so a container that only patches cells in a template can be small and fast to build. pandas pulls in NumPy and, in recent versions, optionally PyArrow — hundreds of megabytes once the wheels are unpacked. On a scheduled job that runs in a cold container every hour, that difference shows up in the startup time, not just the image size.

Bash

pip install openpyxl          # ~250 KB wheel, no compiled dependencies
pip install pandas openpyxl   # pulls NumPy; considerably larger image

That is not an argument against pandas — a job that does real analysis needs it. It is an argument for not installing it in the job that writes three cells into a template, which is a surprisingly common shape once reporting scripts multiply.

Common pitfalls

Symptom	Cause	Fix
Styling applied with openpyxl disappears	The file was rewritten afterwards with `to_excel`	Do the pandas write first, then style — or style through `writer.sheets` in one pass
`to_excel(mode="a")` raises on an existing sheet name	pandas will not overwrite a sheet by default	Pass `if_sheet_exists="replace"`, or edit with openpyxl instead
Number formats look right in Python, wrong in Excel	The value was written as a string	Write real numbers and set `number_format`; formatting a text cell changes nothing
Memory spikes on a large file	The full grid is materialised by both libraries	Use `read_only=True` (openpyxl) or read in chunks; see the scale notes below
Formulas read back as text	openpyxl returns the formula unless `data_only=True`	Open with `data_only=True`, and remember only Excel populates cached values

Performance and scale

For a file under about 50,000 rows the difference is not worth measuring — both finish in under a second. Past that, three effects show up. pandas holds one NumPy array per column, which is far more compact than openpyxl's one Python object per cell, so for reading a large sheet into memory pandas is the lighter of the two despite using openpyxl to parse it. For scanning a large sheet without keeping it, openpyxl's read_only=True mode wins outright because it never builds anything. And for writing large volumes, neither is the right answer — that is xlsxwriter's territory, described in Write a Million Rows to Excel with XlsxWriter in Constant Memory.

Python

from openpyxl import load_workbook

# Scan a big sheet without building a DataFrame or a cell grid.
book = load_workbook("large.xlsx", read_only=True, data_only=True)
sheet = book["Data"]
rows = 0
for row in sheet.iter_rows(min_row=2, values_only=True):
    rows += 1
book.close()
print(f"{rows:,} rows scanned")

Conclusion

Use pandas for the data and openpyxl for the document. pandas turns a sheet into a typed table and back again in two lines, and knows nothing about how the result looks; openpyxl controls every visible property of the file and is the only one of the two that can edit a workbook that already exists. The productive pattern is not to choose but to sequence them — aggregate with pandas, then reach through writer.sheets and finish the sheet with openpyxl before it is saved.

Frequently asked questions

Is openpyxl faster than pandas for reading Excel? No — pandas uses openpyxl to do the reading, so it cannot be faster than what it delegates to. The only case where openpyxl alone wins is a streaming read with read_only=True, where you scan rows without ever building a DataFrame.

Can I use both in the same script? Yes, and most production scripts do. Read and reshape with pandas, then reopen the saved file with openpyxl to add widths, styles or a formula column. pandas.ExcelWriter even exposes the underlying openpyxl workbook as writer.book so you can do it without reopening.

Why does my pandas output have an extra unnamed first column? to_excel writes the DataFrame index by default. Pass index=False whenever the row numbers are not meaningful data.

Does openpyxl need pandas installed? No. openpyxl is standalone and has no dependency on pandas or NumPy, which makes it the lighter choice for a container that only patches cells in an existing workbook.

Up one level: Choosing a Python Excel Library — the whole landscape, including xlsxwriter, Polars and xlwings.
openpyxl vs xlsxwriter vs pandas.ExcelWriter — the same question on the writing side.
Using openpyxl for Excel File Manipulation — the cell-level API in depth.
Writing DataFrames to Excel with Pandas — the pandas half of the handoff.
Auto-Fit Column Widths When Writing with Pandas — the most common reason to reach through to openpyxl.